|
PymssqlExamples
Example scripts using pymssql module.
en, ko pymssql examples (strict DB-API compliance):import pymssql
conn = pymssql.connect(host='SQL01', user='user', password='password', database='mydatabase')
cur = conn.cursor()
cur.execute('CREATE TABLE persons(id INT, name VARCHAR(100))')
cur.executemany("INSERT INTO persons VALUES(%d, %s)", \
[ (1, 'John Doe'), (2, 'Jane Doe') ])
conn.commit() # you must call commit() to persist your data if you don't set autocommit to True
cur.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
row = cur.fetchone()
while row:
print "ID=%d, Name=%s" % (row[0], row[1])
row = cur.fetchone()
# if you call execute() with one argument, you can use % sign as usual
# (it loses its special meaning).
cur.execute("SELECT * FROM persons WHERE salesrep LIKE 'J%'")
conn.close()
# You can also use iterators instead of while loop. Iterators are DB-API extensions, and are available since pymssql 1.0.Rows as dictionariesSince pymssql 1.0.2 rows can be fetched as dictionaries instead of tuples. This allows for accessing columns by name instead of index. Note the as_dict argument. import pymssql
conn = pymssql.connect(host='SQL01', user='user', password='password', database='mydatabase', as_dict=True)
cur = conn.cursor()
cur.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
for row in cur:
print "ID=%d, Name=%s" % (row['id'], row['name'])
conn.close()Calling stored proceduresAs of pymssql 2.0.0 stored procedures can be called using the rpc interface of db-lib. import pymssql
conn = pymssql.connect(host='SQL01', user='user', password='password', database='mydatabase', as_dict=True)
cur = conn.cursor()
cur.callproc('findPerson', ('John Doe',))
for row in cur:
print "ID=%d, Name=%s" % (row['id'], row['name'])
conn.close()
|
► Sign in to add a comment
Thank you for examples and building the interface.
Thank you very much
When I tried the example I am getting the following erro in Feora14
OperationalError? Traceback (most recent call last)
/home/jaganadhg/Desktop/YahooKDDCup/trunk/<ipython console> in <module>()
/usr/lib/python2.7/site-packages/pymssql.pyc in connect(dsn, user, password, host, database, timeout, login_timeout, trusted, charset, as_dict, max_conn)
--> 607 raise OperationalError?, e0?OperationalError?: DB-Lib error message 20009, severity 9: Unable to connect: Adaptive Server is unavailable or does not exist Net-Lib error during Operation now in progress Error 115 - Operation now in progress How to solve it
The above example for rows as dictionaries has a little bug in it? I think the loop should be: for row in cur.fetch_all():
Otherwise, I cannot get the rows to be dictionaries. This is behavior is from the ubuntu 10.04 universe repo:
# apt-cache show python-pymssql Package: python-pymssql Priority: optional Section: universe/python Installed-Size: 184 Maintainer: Ubuntu Developers <ubuntu-devel-discuss@lists.ubuntu.com> Original-Maintainer: Josselin Mouette <joss@debian.org> Architecture: amd64 Source: pymssql Version: 1.0.2+dfsg-1 Depends: libc6 (>= 2.4), libsybdb5 (>= 0.63), python (<< 2.7), python (>= 2.6), python-support (>= 0.90.0) Filename: pool/universe/p/pymssql/python-pymssql_1.0.2+dfsg-1_amd64.deb Size: 28316 MD5sum: 1e9c36f5abc39058b93965de65017df2 SHA1: b53fae710c2ab612691b1f6f26682f6ed21fba11 SHA256: 4db61b74fbf1b4422fcc03a5856f9887775924dcab5bb017eaeb98823a369904 Description: Python database access for MS SQL server and Sybase
Bugs: https://bugs.launchpad.net/ubuntu/+filebug Origin: Ubuntuoops. I meant: for row in cur.fetchall():
I tried the following code with no success and no error message:
The connect seems to work fine and the execute returns no error but it also doesn't print anything. If I connect to the database with an SQL client and run the same query statement I get 74 rows back. If I look at the cur variable in my debugger (Eclipse with PyDev?) cur.rowcount is -1.
Additionally, if I use tsql from the command line and run the query it works fine and my freedts.log file shows the tsql activity but nothing from my python. I've tried converting it to mssql as well with no errors but no data either.
Mac OS X 10.6.7 Python 2.7 pymssql 1.0.2
Anyone have any ideas?
Has anyone an example of create_engine with sqlalchemy using pymssql? I don't find one
I have found it!! just changes mssql: ---> mssql+pymsql: in the create_engine
is there a way of telling that conn.commit() executed successfully e.g. return a value for success? i want after an insert of values into another database table msssql? then if insert to mssql was successful then i proceed to update another table column in a mysql table.