|
FAQs
Frequently Asked Questions
Featured
Getting Help and Reporting IssuesIf you are having trouble, first make sure you are using a recent version. In particular, use the 3.x version from the master branch. I'd really like to stop support the 2.1.x versions, but I will until there are very few people using them. Be sure to search the issues (including closed ones) and the discussion group. If you have questions, start by posting something to the discussion group, which allows others to help find the problem. If you are sure you have a bug, try to create a small test to reproduce the problem and open a new issue. Error MessagesConnecting fails with an error about SQL_ATTR_AUTOCOMMITpyodbc.Error:('HYC00','[HYC00][Microsoft][ODBC Excel Driver]Optional feature not implemented (106) (SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT))')Add autocommit=True to the connection call. ODBC drivers always start with autocommit turned on, but the Python DB API specification requires it to be turned off. After a connection is made, pyodbc first attempts to turn off autocommit before returning the connection. If the driver does not support transactions, such as the Microsoft Excel driver, you will see an error like the following: If you set the autocommit keyword to True, pyodbc will not attempt any changes to the connection since it will already have autocommit turned on: cnxn = pyodbc.connect('DSN=test', autocommit=True)Connecting fails on OS/X 10.4 with an error about SQL_ATTR_CONNECTION_POOLINGApparently unixODBC on OS/X 10.4 does not support connection pooling, so you will see an error like: Fatal Python error: Unable to set SQL_ATTR_CONNECTION_POOLING attribute. Abort trap You will need to set pyodbc.pooling to False before your first connection is made: pyodbc.pooling = False Connection pooling works on 10.5 and above. Catalog procedure 'xxx' can not be run in a transactionThis error was reported by Sybase ASE 15 with FreeTDS when running Cursor.primaryKeys(), but I would expect similar errors when using the other catalog functions. For some reason, ASE does not allow the stored procedure "sp_keys" to be run inside of a transaction. By default all pyodbc connections start with a transaction, so autocommit will need to be turned on. It can be turned on using the autocommit keyword to the connect function, or it can be turned on temporarily like this: cnxn.autocommit = true rows = cursor.primaryKeys().fetchall() cnxn.autocommit = false Data QuestionsHow do I retrieve autogenerated/identity values?Unfortunately ODBC does not have a way to retrieve the value of autogenerated values. (There is a function that looks like it was designed for this, but a closer read of the documentation explains it shouldn't be used.) Many databases do provide a way to access this using a 2nd select however. SQL ServerUse "SELECT @@IDENTITY". Note that @@IDENTITY returns the last generated value, so if a trigger causes a 2nd insert, you'll get the trigger's generated value. SQL Server also provides SCOPE_IDENTITY() which is supposed to fix this. Unfortunately the SQL Server ODBC driver calls an internal stored procedure for each execute call which messes up the scoping, making this unusable. DB/2Use the following: value = cursor.execute("select IDENTITY_VAL_LOCAL() from SYSIBM.SYSDUMMY1").fetchone()[0] Note that value is always going to be a Decimal object. The IBM documentation for IDENTITY_VAL_LOCAL() says The result is a DECIMAL(31,0), regardless of the actual data type of the corresponding identity column. You can use a cast to change the datatype if necessary: select cast(IDENTITY_VAL_LOCAL() as integer) from SYSIBM.SYSDUMMY1 Why are inserts, updates, and deletes silently failing?Did you forget to call Connection.commit()? People used to ODBC but new to the Python DB API often expect connections to start in autocommit mode. Since the Python DB API requires manual commit, pyodbc connections do not autocommit by default. You must call cnxn.commit() after your inserts and deletes. This is usually the best design for complex database work since you can batch multiple statements and commit them in one transaction. Partial work is automatically rolled back when an exception is raised because the connection object is closed when deleted. However, if you really want autocommit, set the connection function's autocommit keyword parameter to true: cnxn = pyodbc.connect(cstring, autocommit=True) or use the Connection object's autocommit attribute: # Check the current setting print 'turned on? ', cnxn.autocommit # Turn autocommit on cnxn.autocommit = True Excel sometimes returns NULL even though there is dataSince Excel spreadsheets don't really have data types, the Excel driver uses the first 8 rows to determine what data type to assign to each. The majority type in the first 8 rows determines the type. For example, if a table in Excel looks like this:
The column id will be assigned data type 'float' since most of the values are numbers. When a value is read that doesn't match the data type, such as 'hi' in the 5th row, Excel cannot convert it and will return NULL. Unicode or Garbage from MySQLIf your database columns have Unicode, you may need to add CHARSET=UTF8 to your connection string. Also note that metadata, such as results from Cursor.columns, is in Unicode, so even if your tables only use ASCII, you might need this setting. Serializing to JSONTo serialize each row as a JSON object, convert each one to a dictionary mapping column name to value like so: obj = { t[0]: value for (t, value) in zip(row.cursor_description, row) }The cursor_description variable is simply the Cursor.description list. The first item (0) of each element is the column's name. Obviously you'll need to ensure the column names are good Javascript names and the data types are understood by the json module. Miscellaneous QuestionsHow can I set the application name in SQL Server?In SQL Server, each connection has an application name which can be very helpful for troubleshooting. This is set using the APP keyword in your connection string, like so: cnxn = pyodbc.connect('dsn=test;pwd=pwd;APP=MyApp') In this example, the connection would show up as MyApp. Building QuestionsDoesn't build on LinuxOn Linux, pyodbc is usually built with unixODBC, so it will need the unixODBC header files installed. On a RedHat/Fedora/CentOS box, this RPM is usually named unixODBC-devel yum install unixODBC-devel Notes And ResearchMac OS/XODBC was not included until Jaguar. The libraries included are actually OpenLink Software's iODBC library. (Reference: Open Database Connectivity in Jaguar) | ||||||||||||
I need to write a database client in Python that runs on a networked Linux machine, and which has to access a Microsoft SQL Server database that runs on a Windows server. Can pyodbc help me with accessing a Microsoft SQL Server remotely from Linux?
Yes, try along with freetds driver.
As for requiring "ODBC" on Ubuntu; this translates into a dependency on the package unixodbc that must be installed before running pyodbc's setup.py
For Ubuntu I had to install unixodbc-dev before pyodbc - apt-get install unixodbc unixodbc-dev
I'm installing on Win 2008 R2 and get a 'requres python 2.6 which is not found in the registry' error that quits the installation. Python 2.6.4 is installed and works -- but even running as admin it won't see it. Can I force installation?
when I run python setup.py install i get this: running install running build running build_ext building 'pyodbc' extension gcc -pthread -fno-strict-aliasing -DNDEBUG -g -fwrapv -O2 -Wall -Wstrict-prototypes -fPIC -DPYODBC_VERSION=2.1.8 -I/usr/include/python2.6 -c /home/administrador/web2py/pyodbc-2.1.8/src/sqlwchar.cpp -o build/temp.linux-i686-2.6/home/administrador/web2py/pyodbc-2.1.8/src/sqlwchar.o -Wno-write-strings gcc: error trying to exec 'cc1plus': execvp: No existe el fichero ó directorio error: command 'gcc' failed with exit status 1 i have alreday run sudo apt-get install unixodbc unixodbc-dev since i'm an ubuntu user. Can some one help me with this?
I also met this problem , after installed 'python-dev','python2.6-dev','unixodbc-dev' , this problem was solved
Using a raspberry pi with python3. Is there or will be available pyodbc 3.0.2 for python3 on rpi?
henry
getting below error when using cursor.execute() to run a select SQL:
pyodbc.Error: ('HY000', "HY000? DataDirect?Sybase Wire Protocol driver?Server?Attempt to BEGIN TRANSACTION in database 'testdb' failed because database is READ ONLY.\n (3906) (SQLExecDirectW)")
pyodbc:3.0.7 DB: Sybase 12.5 conn: Driver={SYBASE ASE ODBC Driver};NA=abc,123;Uid=USER;Pwd=PASS;
anyone seen this before when compiling pyodbc on rhel 5
/src/pyodbccompat.h:119: error: expected â,â or â;â before â{â token pyodbc-3.0.7/src/buffer.cpp:62: fatal error: error closing -: Broken pipe compilation terminated. The bug is not reproducible, so it is likely a hardware or OS problem. error: command 'gcc44' failed with exit status 1
has anyone had any success with installing pyodbc on Solaris 10 machines?
I've tried to build it from source, e.g python setup.py build install but unfortunately i got this error:
ImportError?: No module named distutils.corebuilding and/or installing packages on Solaris 10 can sometimes be a pain....
When ran cursor.execute(sql) and the database has uncommited changes, the execute will stay eternally waiting an answer. Is there a way to put a timeout and make the execute fail if it doesn't get the answer before the timeout?
When I install pyodbc in Linux, I meet the compile error:
In file included from /usr/local/unixODBC-2.3.2/include/sql.h:19,
/usr/local/unixODBC-2.3.2/include/sqltypes.h:406: error: ISO C++ forbids declaration of ‘SQLBIGINT’ with no type /usr/local/unixODBC-2.3.2/include/sqltypes.h:409: error: ISO C++ forbids declaration of ‘SQLUBIGINT’ with no typeI have add the compile attr "-DBUILD_REAL_64_BIT_MODE", but it does not work, what can i do????
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/
I get this odbc error on osx by trying to insert a JPEG into a FileMaker? 14 database. When i run the same script against a MySQL for inserting a blob, there's no error. Inserting a text field does not produce an error. Also i have no clue and cannot find a meaningful explanation for odbc error HY011...
Can anyone help please? Thanks in advance!
The output when i type the code interactively :
Python 2.7.9 (default, Dec 13 2014, 15:13:49) 4.2.1 Compatible Apple LLVM 6.0 (clang-600.0.56)? on darwin Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc >>> with open('Femke.jpg', 'rb') as f: ... data = f.read() ... >>> conn = pyodbc.connect('DRIVER={/Library/ODBC/FileMaker? ODBC.bundle/Contents/MacOS/fmodbc.so};SERVER=localhost;DATABASE=testContainer;UID=Admin') >>> cur = conn.cursor() >>> cur.execute("insert into testContainer (cont) values (PutAs?(?, 'JPEG'))", pyodbc.Binary(data)) Traceback (most recent call last):
pyodbc.Error: ('HY011', 'HY011? unixODBC?Manager?Attribute cannot be set now (0) (SQLPutData)') >>>Python 3 using PyODBC 3.0.10 does NOT allow access to Row data by column name: Even though there is/are records in the cursor, when accessing row columns by name I get
>>>>> AttributeError?: 'Row' object has no attribute 'adminuserid'
The cursor_description shows 'adminuserid' as one of the column names and the case is correct/consistent. This fails for ALL selected column names: It simply cannot access Row records by column name. By position does work however.