|
Objects
Overview of pyodbc objects
The objects provided by pyodbc are defined by the Python Database API Specification v2.0, so you should familiarize yourself with it. The ModuleYou start, of course, by importing the pyodbc module: import pyodbc If you can't get past this, pyodbc is not installed correctly -- see the installation pages. The module provides:
ConnectionsThe Connection object represents a single connection to the database and is obtained from the module's connect function: cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=mine;UID=me;PWD=pwd')There are two primary features of the connection object:
commitUnless you have enabled autocommit (in the pyodbc.connect function or with Connection.autocommit), all uncommited work will be discarded when the Connection object is closed. You must call cnxn.commit() or your work will be lost! cnxn = pyodbc.connect(...) # do work here... cnxn.commit() cnxn.close() exception handlingThe standard Python (www.python.org) uses reference counting for garbage collection, so as long as your connection and cursors are not used outside the current function, they will be closed automatically when the function exits. Since connections automatically roll back changes (since the last commmit call) when they are closed, you do not need a finally block to clean up errors: cnxn = pyodbc.connect(...) cursor = cnxn.cursor() # do work here # an exception is raised here cnxn.commit() In this example, the exception causes the function to exit without reaching the commit call. Therefore all the work performed with the cursor will be rolled back. CursorsCursor objects are used to execute SQL. ODBC & pyodbc allow multiple cursors per connection, but not all databases support this. (You can use SQLGetInfo to determine how many concurrent cursors can be supported: cnxn.getInfo(pyodbc.SQL_MAX_CONCURRENT_ACTIVITIES).) The most important features of cursors are:
cnxn = pyodbc.connect(...)
cursor = cnxn.cursor()
cursor.execute("""
select user_id, last_logon
from users
where last_logon > ?
and user_type <> 'admin'
""", twoweeks)
rows = cursor.fetchall()
for row in rows:
print 'user %s logged on at %s' % (row.user_id, row.last_logon)RowsRow objects are tuple-like, as specified by the DB API, but also support accessing columns by name. The names of the columns is described by Cursor.description. Row objects do not reference their creating cursor, so it is valid to use rows after closing the cursor and connection. Each row has a cursor_description attribute which is the same as the Cursor.description from the row's cursor, so it is accessible without the cursor. |
cursor.getInfo(pyodbc.SQL_MAX_CONCURRENT_ACTIVITIES) should read: connection.getinfo(pyodbc.SQL_MAX_CONCURRENT_ACTIVITIES)
For SQL Server it is usually 1 = no concurrent cursors and MySQL (libmyodbc) returns 0 = no limit (or undefined)
FreeTDS 0.82 has a bug that requires you to read all results from a cursor or close() it before starting another query on the same connection:
con = pyodbc.connect('DRIVER={FreeTDS};SERVER=<yourserver>;UID=<uid>;PWD=<pwd>') cur = con.cursor() cur.execute('SELECT 1') cur.execute('SELECT 1') ProgrammingError: ('24000', '[24000] [FreeTDS][SQL Server]Invalid cursor state (0) (SQLExecDirectW)')For Sql Server, if you use this command
pyodbc.connect('DRIVER={SQL Server};SERVER=servername:1433;DATABASE=data;')
and get this error:
Error: ('08001', '08001? Microsoft?SQL Server Driver?DBNETLIB?Invalid connection. (14) (SQLDriverConnectW); 01000? Microsoft?SQL Server Driver?DBNETLIB?ConnectionOpen? (ParseConnectParams?()). (14)')
Use this instead:
pyodbc.connect('DRIVER={SQL Server};SERVER=servername\data,1433;')
The formatting of what follows the SERVER variable makes a BIG difference
Is it possible to output the names of the columns (cursor.description)? When I just put:
for row in rows:
All I get is the data within the table, but I do not get the column header names.
Thanks!
It is typically better to use TRUSTED_CONNECTION=true as part of your connection string if your Windows User has access to the DB.
Then you can remove the UID and PASSWORD from your python code.
To get a list of column header names in SQL Server...
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'your_table_name_or_view'
http://anekajaringmurah.blogspot.com/ http://pusatjaringmurah.blogspot.com/ http://anekajaringpengaman.blogspot.com/ http://agenjaringpengaman.blogspot.com/ http://jaringpengamanfutsal.blogspot.com/ http://jaring-pengamanmurah.blogspot.com/ http://jaringcenter.blogspot.com/ http://agenjaringjakata.blogspot.com/ http://jualjaringpengamanmurah.blogspot.com/ http://jaringsafetyjakarta.blogspot.com/ http://jaringpengaman-murah.blogspot.com/ http://jaringmurah.blogspot.com/ http://jaring-murah.blogspot.com/ http://jaringpengamanmurah.blogspot.com/ http://jaringbangunan.blogspot.com/ http://agenjaringsafety.blogspot.com/ http://sentral-jaring.blogspot.com/ http://sentraljaring.blogspot.com/ http://tokojaringpengaman.blogspot.com/ http://pusatjaringjakarta.blogspot.com/ http://tokojaringpengamanmurah.blogspot.com/ http://jualjaringsafetymurah.blogspot.com/ https://pancasamudera.wordpress.com/ https://pasangjaringfutsal.wordpress.com/ https://jualtambangmurah.wordpress.com/ https://tokojaring.wordpress.com/ https://jualjaringfutsal.wordpress.com/ https://jaringfutsal.wordpress.com/