My favorites | Sign in
Project Home Downloads Wiki
READ-ONLY: This project has been archived. For more information see this post.
Search
for
ConnectionStrings  
Connecting to Databases
Featured
Updated Nov 22, 2011 by mkleehammer

Overview

The most important thing to know is that pyodbc does not even look at the connection string -- it is passed directly to SQLDriverConnect unmodified. Therefore any generic ODBC connection string documentation should be valid. And anything ODBC supports, pyodbc supports, including DSN-less connections and FILEDSNs.

We'll collect useful information here, but connection strings are driver specific.

DSN-less Connections

A DSN is basically a collection of settings for a connection. Sometimes it is handy to eliminate centralized DSNs and provide the connection settings from your own configuration. To do this, you simply need to pass all of the settings normally in a DSN to ODBC. (Note that this works well on Windows. Recent versions of unixODBC are supposed to support this. I have not confirmed iODBC yet.)

You choose the driver using the DRIVER keyword. You then pass all of the other settings, separated by semicolons. The other setting keywords are driver-specific, but most drivers try to use the same set of keywords, so figuring out the right connection string is usually not too hard.

Here is an example for SQL Server 2000-2008:

DRIVER={SQL Server};SERVER=cloak;DATABASE=test;UID=user;PWD=password

And Microsoft Access:

DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\\dir\\file.mdb

(Don't forget to escape your backslashes like I did above or use r'...'.)

Security

This obviously differs by database, but most support a user-id and password. Windows DSNs generally do not store the password, so you will have to do so in your application configuration. (Since I have to do this anyway, this is why I generally opt for dsn-less connections.) To provide the password for a DSN connection, append PWD=xxx:

DSN=myserver;PWD=xxx

On Windows, some databases support Windows authentication / single sign-on. This uses the currently running account to logon. See your driver's documentation for this. For Microsoft drivers, append Trusted_Connection=yes:

DSN=myserver;Trusted_Connection=yes

Important: This is generally not a good idea for servers on Windows since they are usually running as a non-standard user. I recommend using a real user-id and password for server configurations.

Databases

SQL Server

On Windows

There are actually two or three SQL Server drivers written and distrubuted by Microsoft: one referred to as "SQL Server" and the other as "SQL Native Client" and "SQL Server Native Client 10.0}".

  • DRIVER={SQL Server};SERVER=cloak;DATABASE=test;UID=user;PWD=password
  • DRIVER={SQL Native Client};SERVER=dagger;DATABASE=test;UID=user;PWD=password
  • DRIVER={SQL Server Native Client 10.0};SERVER=dagger;DATABASE=test;UID=user;PWD=password

The "SQL Server" one works for all versions of SQL Server, but only enables features and data types supported by SQL Server 2000, regardless of your actual server version.

For SQL Server 2005 installations, use "SQL Native Client" to enable 2005 features and types. Note that this version is not provided in all SQL Server 2008 installations!

Finally, you need "SQL Server Native Client 10.0" for SQL Server 2008 features and types.

Adding the APP keyword allows you to set a name for your application which is very handy for the database administrator:

APP=MyApp;DRIVER={SQL Server Native Client 10.0};SERVER=dagger;DATABASE=test;UID=user;PWD=password

Microsoft's ODBC driver for Linux

Microsoft has their own ODBC driver for Linux now.

As of late 2011, it appears to be targeted at RedHat-like distros, but it may be easy enough to move the files. (They are 64-bit binaries.) A 32-bit version is expected.

http://msdn.microsoft.com/en-us/library/hh598132(v=SQL.10).aspx

FreeTDS

If you are having trouble, please test against Microsoft's driver (above) if possible. It would be helpful to determine if problems are in pyodbc or the driver.

Reading VARCHAR fields limited to 255 characters: If you do not set the TDS protocol version it will default to something old and limit your reads to 255 characters. Use TDS_Version=8.0.

You can use this in your connection string or in your /etc/odbc.ini or ~/.odbc.ini files:

 [freetdstest] 

Driver = /usr/lib64/libtdsodbc.so.0 Description = test Server = 192.168.1.100 Port = 1433 TDS_Version = 8.0

MySQL

The official reference is here: http://dev.mysql.com/doc/refman/5.1/en/connector-odbc-configuration-connection-parameters.html

Unicode

If you are using UTF8 in your database and are getting results like "\x0038", you probably need to add "CHARSET=UTF8" to your connection string.

Errors on OS/X

Some MySQL ODBC drivers have the wrong socket path on OS/X, causing an error like Can't connect to local MySQL server through socket /tmp/mysql.sock. To connect, determine the correct path and pass it to the driver using the 'socket' keyword.

Run mysqladmin version and look for the Unix socket entry:

Server version		5.0.67
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/var/lib/mysql/mysql.sock

Pass the socket path in the connection string:

cnxn = pyodbc.connect('DRIVER={MySQL};DATABASE=test;SOCKET=/var/lib/mysql/mysql.sock')

Microsoft Excel

The Excel driver does not support transactions, so you will need to use the autocommit keyword:

cnxn = pyodbc.connect('DSN=test', autocommit=True)

Operating Systems

Windows

Obviously Microsoft wrote the ODBC implementation for Windows. It is now part of the base operating system and many drivers (well Microsoft drivers) are included automatically. If you are using Windows, you'll find the ODBC support is very good, stable, and fast.

Unix, Linux

There are two different ODBC implementations for Linux: iODBC and unixODBC. If you are having trouble connecting, figure out which you have and include it in your web searches.

The unixODBC drivers do not allow multiple queries at the same time by default. You must add Threading = 1 to your odbcinst.ini file to enable it. (Make sure your driver allows it, however!)

http://lists.ibiblio.org/pipermail/freetds/2009q4/025439.html

[FreeTDS]
Driver = /path/to/our/libtdsodbc.so
Threading = 1

Mac OS/X

iODBC is apparently standard on Mac OS/X now, so you'll need to become familiar with how it works.

http://www.macsos.com.au/MacODBC/index.html

Comment by doarm...@gmail.com, Feb 12, 2009

That link for OS/X at maccos.com.au seems to refer to old information?

Comment by danielbm...@gmail.com, Feb 22, 2009

Useful module. Thanks! Playing with the Excel ODBC, I find using a DSN less connection very clean. Sharing an example:

pyodbc.connect("Driver={Microsoft Excel Driver (.xls)}; Dbq=Sample.xls", autocommit=True)

Comment by danie...@gmail.com, Aug 11, 2009

The above example should read:

pyodbc.connect("Driver={Microsoft Excel Driver (*.xls)}; Dbq=Sample.xls", autocommit=True) 
Comment by mpietrzak, Aug 13, 2009

ODBC connect string for Teradata Express 12.0:

DRIVER={Teradata};DBCNAME=localhost;UID=dbc;PWD=dbc;QUIETMODE=YES;

Example interactive Python session: session:

>>> conn = pyodbc.connect('DRIVER={Teradata};DBCNAME=localhost;UID=dbc;PWD=dbc;QUIETMODE=YES;')
>>> conn
<pyodbc.Connection object at 0x01FDF840>
>>> curs = conn.cursor()
>>> curs.execute('select current_timestamp')
<pyodbc.Cursor object at 0x01FD0FA8>
>>> curs.fetchone()
(datetime.datetime(2009, 8, 13, 11, 34, 1, 250), )
Comment by alx3a...@gmail.com, Oct 1, 2009

For sqlserver named instances you can use this syntax:

...SERVER=<hostname>\<instancename>...
Comment by software...@gmail.com, May 13, 2010

I am trying to dynamically create the connection object. I am having problems with the syntax. This is the syntax that works hardocoded cnxn = pyodbc.connect('driver={SQL Server};server=SDWPCE1D\PLM;database=EnterpriseControls?;Trusted_Connection?=yes')

The following does not work, keeps telling me I cannot concatenate a builtin function and a str.

Any help would be appreciated.

server='SDWPCE1D\PLM' database='EnterpriseControls?' insert="('driver={SQL Server};server="+server+";database="+database+";Trusted_Connection?=yes')" cnxn=pyodbc.connect+insert

or modified a bit does not work either cnxn=pyodbc.connect(insert)

I have solved the problem leave the single quotes out

insert="(driver={SQL Server};server="+server+";database="+database+";Trusted_Connection?=yes)" cnxn=pyodbc.connect(insert)

Comment by coen...@gmail.com, Oct 15, 2010
# using mysql odbc driver http://www.mysql.com/downloads/connector/odbc/
import pyodbc
#connect to localhost
cnxn = pyodbc.connect('Driver={MySQL ODBC 5.1 Driver};Server=127.0.0.1;Port=3306;Database=information_schema;User=root; Password=root;Option=3;')
cursor = cnxn.cursor()
Comment by mail2ba...@gmail.com, Dec 13, 2010

how to connect to DB2 database. An example please

Comment by browse2s...@gmail.com, Jan 10, 2011

I am using below connection string to connect sqlserver2008 using IP

import pyodbc as p p.connect( 'DRIVER={SQL Server Native Client 10.0};Server=ip;port=port;Network Library=DBMSSOCN;Database=TEST;uid=id;pwd=pass;Trusted_Connection?=Yes')

I am able to connect using sqlserver management studio..but using this string giving me error:

p.connect( 'DRIVER={SQL Server};Server=192.168.50.220,1433 ;Network Library=DBMSSOCN; Database=EGL_TEST;User ID=SearceDeveloper?;Password=sRc#dvLpr ;Trusted_Connection?=Yes')
Error: ('28000', '28000? Microsoft?SQL Server Driver?Server?Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (18452) (SQLDriverConnectW); 01S00? Microsoft?SQL Server Driver?Invalid connection string attribute (0); 28000? Microsoft?SQL Server Driver?Server?Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (18452); 01S00? Microsoft?SQL Server Driver?Invalid connection string attribute (0)') how to resolve this..its problem of authentication .please help...

Comment by m.milver...@gmail.com, Mar 16, 2011

Python DSN with Filemaker Pro on OS X

Prerequisites

Get the ODBC JDBC Guide It will instruct you to install the OBDC drivers for filemaker that will be in your xDBC folder on your installation cd or downloaded package. Follow the instructions until you can test that your connection is okay.

I then followed the test procedures for iODBC and ran the iodbctest. Inside this tool, the specified the following (as an example): DSN=Local Virtuoso Demo;UID=dba;PWD=cens0red. All I changed was the DSN to be the name of my database, the UID to be my filemaker username, and of course the password.

Having setup pyodbc I passed in my connection string to pyodbc as in:

DSN=Local Virtuoso Demo;UID=dba;PWD=cens0red. 
connection = pyodbc.connect(DSN)
cursor = connection.cursor()

cursor.execute('select * from "Vehicle Faults"')
rows = cursor.fetchall()

for x in rows:
    print x

Conclusion

Hopefully it helps somebody.

Comment by tomas...@gmail.com, Mar 21, 2011

If you get an error: Error: ('0', '0? unixODBC?c (0) (SQLDriverConnectW)') try adding ansi=True in your pyodbc.connect for example: conn = pyodbc.connect(DSN="myDSN", UID="username", PWD="password", ansi=True)

Comment by m.milver...@gmail.com, Mar 28, 2011

Corrections for Python DSN with Filemaker Pro on OS X

The DSN in the python snippet should be enclosed in quotes

DSN="Local Virtuoso Demo;UID=dba;PWD=cens0red"

Filemaker Creates obdc.ini

The exact location escapes me but if you have successfully followed the setup instructions contained in the OBDC JDBC Guide then this file is created for you. The only thing left to do is to run the iodbctest tool, enter the DSN for your Filemaker Pro database and run a test sql query like "select from my_table". Once this works, then your python code should work too (providing you have installed it correctly of course)

Comment by luxago...@gmail.com, Mar 30, 2011

Clarification on using Python with FileMaker? Pro on OS X

I would not have been able to get this running without the help above, and information out there is sparse -- so let me just comment on this and fill in some of the blanks.

1. Install xDBC drivers from CD or .dmg

-- They are not automatically installed. You need to install them yourself.
2. Launch the ODBC Manager in the /Applications/Utilities folder
-- There is also ODBC Administrator from Apple, but I was using ActualTech?'s ODBC Manager. -- This generates a /Library/ODBC/odbc.ini file for you .. versus you doing it yourself. -- Make sure you note the 'name' of it... you will use it below.
3. Test it out
-- Use iodbctest from http://www.iodbc.org/dataspace/iodbc/wiki/iODBC/IODBCPythonHOWTO
-- This contains a suite of programs; install it via RPM or
download it in OS X (it's precompiled; see the downloads section)
-- When you load iodbctest, don't type the name at the prompt... instead
type "DSN=<name>;UID=<login>;PWD=<password>"
-- This should give you the SQL> command prompt where you can test out some commands

Comment by luxago...@gmail.com, Mar 31, 2011

Clarification on using Python with FileMaker? Pro on OS X (Part 2)

4. On the iodbc page mentioned above, one thing that was a little confusing was to ignore the bit about mxODBC and their odbc.ini file setup -- it's a commercial product; pyODBC will work fine for right now and OS X maintains an odbc.ini file in another location.

5. Set Two Shell Variables (example from bash)

$ export ODBCINI="/Library/ODBC"

$ export ODBCSYSINI="/Library/ODBC"

These are not set by default, and you'll get failure messages if pyodbc can't find the "alternate location" that OS X uses for the files.

6. You're Done.

Import pyodbc and you're ready to roll. Fill in the details from above and it should work.

>>> conn = pyodbc.connect(DSN="<name>", UID="<login>", PWD="<password>")

Comment by aarondig...@gmail.com, Jun 20, 2011

If you are using Teradata (this testing on 12) and using DDL I found that I needed to use the autocommit keyword otherwise I received an error Only an ET or null statement is legal after a DDL Statement.

  cnxn = pyodbc.connect('DRIVER=Teradata;DBCNAME=host;UID=user;PWD=password;', autocommit=True)
Comment by luisguil...@gmail.com, Jul 8, 2011

A Sample on Customizing Connection Strings

Most ODBC drivers have a closed similar sintax on creating conn strrings. Thus, It's usefull to write a function to provide proper ConnMakers? to each driver. I wrote a little code sample to help on writting conn strings:

Sample: Code Module

# -*-mode: python; fill-column: 75; tab-width: 8; coding: cp1252 -*-
#
# $Id: odbcCnnStrngMaker.py 00002 2011-07-08 00:17:28Z Luis.G.Quevedo $
from locale import getlocale       #to set locales for MsAccess driver fam.
class DbFamily():
   """Some constants to identify Database driver choices"""
    standarodbc = 0
    Cache       = 0
    Interbase   = 0
    LotusNotes  = 0
    MySql       = 0
    SqlServer   = 0
    Oracle      = 0
    MsAccess    = 1
    MsExcell    = 2
    SQLite      = 3
    Informix    = 4
    postgreSql  = 5
    DBaseFoxpro = 6
    DBase       = 6
    FoxPro      = 6
    # ... add others as you need

def createCnnStrMakerFunction(driver, serverIp, user = '', dbFam=0, uselocale=False):
    """Returns a function pad to create proper conn strings for each couple driver, dbFam."""
    if dbFam==DbFamily.standarodbc:
        if user != '':
            return lambda dbnm, pwd: "DRIVER={" + driver + "};SERVER=" + serverIp + ";DATABASE=" \
                          + dbnm + ";UID=" + user + ";PWD='" + pwd + "'"
        else:
            return lambda dbnm, userNm='', pwd='': "DRIVER={" + driver + "};SERVER=" + serverIp \
                          + ";DATABASE=" + dbnm + ";UID=" + userNm + ";PWD='" + pwd + "'"
    elif dbFam==DbFamily.MsAccess:
        strlocale = ""
        if uselocale:
            tmp = getlocale()
            strlocale = ";Locale Identifier=" + str(tmp[1])
        if user != '':
            sufix=""
            if user == "Admin": sufix="ExtendedAnsiSQL=1;"
            #"ExtendedAnsiSQL=1;" create,drop,... etc privileges
            return lambda dbpath, pwd: "Driver={" + driver + "};Dbq=" + dbpath \
                          + ";Uid="+ user + strlocale + ";Pwd=" + pwd + sufix
        else:
            return lambda dbpath, userNm='', pwd='': "Driver={" + driver + "};Dbq=" + dbpath \
                          + ";Uid="+ userNm + strlocale + ";Pwd=" + pwd
    elif dbFam==DbFamily.MsExcell:
        _driver = driver
        if (driver=='') | (driver=='default'): _driver = 'Microsoft Excel Driver (*.xls)'
        return lambda filepath, dfltWdirpath='', driverid='790': "Driver={" + _driver \
                      + "};DriverId=" + str(driverid) + ";Dbq=" \
                      + filepath + ";DefaultDir=" + dfltWdirpath + ";"
    elif dbFam==DbFamily.SQLite:
        return lambda dbpath, timeout='1000', longnames='0', syncpragma='NORMAL', notxn='0', stepapi='0': \
                      "Driver=" + driver + "Database=" + dbpath + ";LongNames=" + str(longnames) \
                      + ";Timeout="+ str(timeout) + ";NoTXN=" + str(notxn) + ";SyncPragma=" \
                      + syncpragma + ";StepAPI=" + str(stepapi) + ";"
    elif dbFam==DbFamily.Informix:
        if user != '':
            return lambda dbnm, hostnm, serviceNm, pwd='', protocol='olsoctcp': \
                          "Dsn='';Driver={" + driver + "};Host=" + hostnm + "};Server=" + serverIp \
                          + ";Service=" + serviceNm + ";Protocol=" + protocol + ";Database=" \
                          + dbnm + ";Uid="+ user + ";Pwd=" + pwd
        else:
            return lambda dbnm, hostnm, serviceNm, userNm='', pwd='', protocol='olsoctcp': \
                          "Dsn='';Driver={" + driver + "};Host=" + hostnm + "};Server=" + serverIp \
                          + ";Service=" + serviceNm + ";Protocol=" + protocol + ";Database=" \
                          + dbnm + ";Uid="+ userNm + ";Pwd=" + pwd
    elif dbFam==DbFamily.postgreSql:
        if user != '':
            return lambda dbnm, pwd='', port='5432': "Driver={" + driver + "};Server=" + serverIp \
                          + ";Port=" + str(port) + ";Database=" + dbnm + ";Uid=" + user + ";Pwd=" + pwd + ";"
        else:
            return lambda dbnm, userNm='', pwd='', port='5432': "Driver={" + driver + "};Server=" + serverIp \
                          + ";Port=" + str(port) + ";Database=" + dbnm + ";Uid=" + userNm + ";Pwd=" + pwd + ";"
    elif dbFam==DbFamily.DBaseFoxpro:
        return lambda dbpath, driverid='277': "Driver={" + driver + "};DriverID=" \
                      + str(driverid) + ";Dbq=" + dbpath + ";"

#A tester group of functions
def _gf(pseudodriver, username, familyId):
        return createCnnStrMakerFunction(pseudodriver,'127.0.0.1',username, familyId, True)

def _tester(username='root'):
    dvr = [("Cache ODBC driver", 0),
           ("Interbase ODBC Driver Name", 0),
           ("LotusNotes ODBC Driver Name", 0),
           ("MySql ODBC Driver Name", 0),
           ("SqlServer ODBC Driver Name", 0),
           ("Oracle ODBC Driver Name", 0),
           ("MsAccess ODBC Driver Name", 1),
           ("MsExcell ODBC Driver Name", 2),
           ("SQLite ODBC Driver Name", 3),
           ("Informix ODBC Driver Name", 4),
           ("postgreSql ODBC Driver Name", 5),
           ("DBase ODBC Driver Name", 6),
           ("FoxPro ODBC Driver Name", 6)]
    l=len(dvr)
    tf = []
    for i in range(l):
        tf.append( _gf(dvr[i][0], username, dvr[i][1]))
    return tf

def __test__():
    f = _tester()
    print '>>> f[0]("database0", "pasword0")'
    print f[0]("database0", "pasword0")
    print '>>> f[1]("database1", "pasword1")'
    print f[1]("database1", "pasword1")
    print '>>> f[2]("database2", "pasword2")'
    print f[2]("database2", "pasword2")
    print '>>> f[3]("database3", "pasword3")'
    print f[3]("database3", "pasword3")
    print '>>> f[4]("database4", "pasword4")'
    print f[4]("database4", "pasword4")
    print '>>> f[5]("database5", "pasword5")'
    print f[5]("database5", "pasword5")
    print '>>> f[6]("Database.mdb", "password6")'
    print f[6]("Database.mdb", "password6")
    print '''>>> f[7]("MyExcellFile.xls", "'c:\\...\\My documents'")'''
    print f[7]("MyExcellFile.xls", "'c:\\...\\My documents'")
    print '>>> f[8]("Database8")'
    print f[8]("Database8")
    print '>>> f[9]("Database9", "HostName", "InstaledServiceName", "password9")'
    print f[9]("Database9", "HostName", "InstaledServiceName", "password9")
    print '>>> f[10]("database10", "password10")'
    print f[10]("database10", "password10")
    print '>>> f[11]("Database11.dbf")'
    print f[11]("Database11.dbf")
    print '>>> f[12]("Database12.dbf")'
    print f[12]("Database12.dbf")

Now you have a module to customize conn strings.

Testing the Module

Copy and paste the code. Save as odbcCnnStrngMaker.py and run the next code lines:

>>> from odbcCnnStrngMaker import *
>>> __test__()
   ...
   printed a list of conn strings

To Use The Module

I supouse the module was imported. As I did lines before.

>>> MySqlConMaker = createCnnStrMakerFunction("MySQL ODBC 5.1 Driver", 
                                              "localhost", 
                                              "root", 
                                              DbFamily.MySql)

>>> db1ConnString = MySqlConMaker("Db1Name", "db1pwrd")
>>> db2ConnString = MySqlConMaker("Db2Name", "db2pwrd")

Etc... Hope it helps somebody.

Comment by luisguil...@gmail.com, Jul 10, 2011

I had a bad mistake on my last post: Most conn strings set pwd=ThePassword? and I wrote pwd='ThePassword?'. Delete the simple quotes before using the module where necesary. LgQ.

Comment by sujit.n...@gmail.com, Apr 28, 2012

Any tips on how to make this work on servers which require LDAP authentication? Here is my setup so far.

1. pyodbc-2.1.11

2. python-2.6.6 (64 bit)

3. 64 bit drivers for Teradata taken from http://downloads.teradata.com/download/connectivity/odbc-driver/linux

4. RHEL Workstation 6.1 is my client for querying a Teradata server

Right now, it throws the following error.

adhoc: (SQL Diagnostics) STATE=28000, CODE=4294959279, MSG=Teradata?Teradata Driver?Database? The UserId?, Password or Account is invalid.

Here, adhoc is the sample code provided by Teradata and is kept in /opt/teradata/client/13.10/odbc_64/samples/C

Thanks a bunch, Sujit

Comment by pandit.s...@gmail.com, May 10, 2012

Does anyone know how to connect to Sybase 15 using pyodbc? Appreciate any help you can provide.

Comment by jonathan...@gmail.com, Jan 29, 2013

SQL Server Connection String mystery solved (Don't forget proper quoting for connection string)

Symptom

Able to connect to my test server, but connections to the production server produce an error (see below). Both databases run the same version of SQL server. Python run on Windows7x64(Python 2.7.2(32 bit), pyodbc-3.0.6, SQL Server 2008r2). No connection issues from SQL Server Management Studio or Java/JDBC to either test or prod databases. The error is an obvious pointer at the connection string.

Connection to Test Works
cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 10.0};' + 
       'SERVER=testserver.companyname.com;' +
       'DATABASE=App_Specific_DB_Name;schema=foo;UID=AppSpecificUserId;PWD=SuperSecret')
Connection to Prod Fails
cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 10.0};' + 
       'SERVER=prodserver.companyname.com\prodOnlyInstanceName;' +
       'DATABASE=App_Specific_DB_Name;schema=foo;UID=AppSpecificUserId;PWD=SuperSecret')
Connection to Prod Works
cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 10.0};' + 
       r'SERVER=prodserver.companyname.com\prodOnlyInstanceName;' +
       'DATABASE=App_Specific_DB_Name;schema=foo;UID=AppSpecificUserId;PWD=SuperSecret')

Example error

pyodbc.Error: ('08001', '[08001] [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [53].
  (53) (SQLDriverConnect); [HYT00] [Microsoft][SQL Server Native Client 10.0]Login timeout expired (0);
  [01S00] [Microsoft][SQL Server Native Client 10.0]Invalid connection string attribute (0);
  [08001] [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server.
  Server is not found or not accessible. 
  Check if instance name is correct and if SQL Server is configured to allow remote connections.
  For more information see SQL Server Books Online. (53)')
Comment by electroc...@gmail.com, Jul 1, 2013

How can I set a connection that use a proxy server?

Comment by dberg...@gmail.com, Jul 24, 2013

I am trying to use this module to connect to an Open edge Progress driver. The driver is not found. The driver is managed by the ODBC Manager in the sysWOW64 dir vice the system32 dir. I am thinking the module is looking in the area related to system 32 and not sysWOW64. has anyone used the module to connect to Progress?

Comment by toastie...@gmail.com, Sep 12, 2013

If you're using pyodbc on a UNIX machine with FreeTDS and unixODBC, and you've installed FreeTDS and/or unixODBC to a non-standard location, then you may get an error when trying to connect. For example, using:

conn = pyodbc.connect(r'DRIVER={FreeTDS};SERVER=yourserver.yourcompany.com;UID=yourlogin;PWD=yourpassword;')

you may get the following error, even though you can connect successfully with both tsql (the FreeTDS utility) and isql (the unixODBC utility).

[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)

This error might be because the UNIX environment variables ODBCSYSINI and ODBCINI have not been set in your UNIX shell to point to your odbc.ini, odbcinst.ini, and freetds.conf files. This happened to me because I was using a virtual environment created with the Python 3.3 built-in "venv" module. Whether this happens with the 3rd-party "virtualenv" module as well, I do not know.

The fix is to set those environment variables. For example, if your odbc.ini, odbcinst.ini, and freetds.conf files are in directory /home/myusername/myvirtualenv/etc, then set them as follows:

export ODBCSYSINI=/home/myusername/myvirtualenv/etc
export ODBCINI=/home/myusername/myvirtualenv/etc/odbc.ini

Without these settings, the implicit values for ODBCSYSINI and ODBCINI are probably "/usr/local/etc" and "~/.odbc.ini" on your machine. ODBCSYSINI is the main one to set, but ODBCINI seems to be used by the function pyodbc.dataSources().

The command odbcinst -j may be useful here to check what values unixODBC is using.

Other ODBC-related environment variables are ODBCINSTINI and ODBCHOME, but neither seems to be relevant for pyodbc.

Comment by CokeMonk...@gmail.com, Jun 10, 2014

FYI if your password contains a ';' you'll have to escape it like ,pwd='{password}' ! I've spent hours trying to figure this out. Using key-value doesn't solve the problem on its own because it just converts the input to a standard connection string. You MUST escape ';' using braces {}

Full example, with password "password;with":

cursor = pyodbc.connect(driver='{SQL Server}', server='server', uid='myself', pwd='{password;with}').cursor()

Comment by gregor.s...@gmail.com, Feb 18, 2015

Anyone having trouble connecting to .mdb where path to mdb file is using "international" characters? I can't find a way around this problem. I'm thinking about copying entire database to some other location. Unfortunately is more than 1G big :(

tabela=cursor.fetchall()

UnicodeEncodeError?: 'ascii' codec can't encode character u'\u017e' in position 111: ordinal not in range(128)

Comment by putuindr...@gmail.com, Mar 21, 2015

can you please tell me what this is? I would like to deepen

http://wdfshare.blogspot.com

Powered by Google Project Hosting