My favorites | Sign in
Project Home Downloads Wiki
READ-ONLY: This project has been archived. For more information see this post.
Search
for
GettingStarted  
Quick Examples To Get You Started
Featured
Updated Jul 28, 2011 by mkleehammer

Connect to a Database

Make a direct connection to a database and create a cursor.

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=me;PWD=pass')
cursor = cnxn.cursor()

Make a connection using a DSN. Since DSNs usually don't store passwords, you'll probably need to provide the PWD keyword.

cnxn = pyodbc.connect('DSN=test;PWD=password')
cursor = cnxn.cursor()

There are lots of options when connecting, so see the connect function and ConnectionStrings for more details.

Selecting Some Data

Select Basics

All SQL statements are executed using the cursor.execute function. If the statement returns rows, such as a select statement, you can retreive them using the Cursor fetch functions (fetchone, fetchall, fetchmany). If there are no rows, fetchone will return None; fetchall and fetchmany will both return empty lists.

cursor.execute("select user_id, user_name from users")
row = cursor.fetchone()
if row:
    print row

Row objects are similar to tuples, but they also allow access to columns by name:

cursor.execute("select user_id, user_name from users")
row = cursor.fetchone()
print 'name:', row[1]          # access by column index
print 'name:', row.user_name   # or access by name

The fetchone function returns None when all rows have been retrieved.

while 1:
    row = cursor.fetchone()
    if not row:
        break
    print 'id:', row.user_id

The fetchall function returns all remaining rows in a list. If there are no rows, an empty list is returned. (If there are a lot of rows, this will use a lot of memory. Unread rows are stored by the database driver in a compact format and are often sent in batches from the database server. Reading in only the rows you need at one time will save a lot of memory.)

cursor.execute("select user_id, user_name from users")
rows = cursor.fetchall()
for row in rows:
    print row.user_id, row.user_name

If you are going to process the rows one at a time, you can use the cursor itself as an interator:

cursor.execute("select user_id, user_name from users"):
for row in cursor:
    print row.user_id, row.user_name

Since cursor.execute always returns the cursor, you can simplify this even more:

for row in cursor.execute("select user_id, user_name from users"):
    print row.user_id, row.user_name

A lot of SQL statements don't fit on one line very easily, so you can always use triple quoted strings:

cursor.execute("""
               select user_id, user_name
                 from users
                where last_logon < '2001-01-01'
                  and bill_overdue = 'y'
               """)

Parameters

ODBC supports parameters using a question mark as a place holder in the SQL. You provide the values for the question marks by passing them after the SQL:

cursor.execute("""
               select user_id, user_name
                 from users
                where last_logon < ?
                  and bill_overdue = ?
               """, '2001-01-01', 'y')

This is safer than putting the values into the string because the parameters are passed to the database separately, protecting against SQL injection attacks. It is also be more efficient if you execute the same SQL repeatedly with different parameters. The SQL will be prepared only once. (pyodbc only keeps the last statement prepared, so if you switch between statements, each will be prepared multiple times.)

The Python DB API specifies that parameters should be passed in a sequence, so this is also supported by pyodbc:

cursor.execute("""
               select user_id, user_name
                 from users
                where last_logon < ?
                  and bill_overdue = ?
               """, ['2001-01-01', 'y'])
cursor.execute("select count(*) as user_count from users where age > ?", 21)
row = cursor.fetchone()
print '%d users' % row.user_count

Inserting Data

To insert data, pass the insert SQL to Cursor.execute, along with any parameters necessary:

cursor.execute("insert into products(id, name) values ('pyodbc', 'awesome library')")
cnxn.commit()
cursor.execute("insert into products(id, name) values (?, ?)", 'pyodbc', 'awesome library')
cnxn.commit()

Note the calls to cnxn.commit(). You must call commit or your changes will be lost! When the connection is closed, any pending changes will be rolled back. This makes error recovery very easy, but you must remember to call commit.

Updating and Deleting

Updating and deleting work the same way, pass the SQL to execute. However, you often want to know how many records were affected when updating and deleting, in which case you can use the cursor.rowcount value:

cursor.execute("delete from products where id <> ?", 'pyodbc')
print cursor.rowcount, 'products deleted'
cnxn.commit()

Since execute always returns the cursor, you will sometimes see code like this. (Notice the rowcount on the end.)

deleted = cursor.execute("delete from products where id <> 'pyodbc'").rowcount
cnxn.commit()

Note the calls to cnxn.commit(). You must call commit or your changes will be lost! When the connection is closed, any pending changes will be rolled back. This makes error recovery very easy, but you must remember to call commit.

Tips and Tricks

Since single quotes are valid in SQL, use double quotes to surround your SQL:

deleted = cursor.execute("delete from products where id <> 'pyodbc'").rowcount

If you are using triple quotes, you can use either:

deleted = cursor.execute("""
                         delete
                           from products
                          where id <> 'pyodbc'
                         """).rowcount

Some databases (e.g. SQL Server) do not generate column names for calculations, in which case you need to access the columns by index. You can also use the 'as' keyword to name columns (the "as user_count" in the SQL below).

row = cursor.execute("select count(*) as user_count from users").fetchone()
print '%s users' % row.user_count

If there is only 1 value you need, you can put the fetch of the row and the extraction of the first column all on one line:

count = cursor.execute("select count(*) from users").fetchone()[0]
print '%s users' % count

This will not work if the first column can be NULL! In that case, fetchone() will return None and you'll get a cryptic error about NoneType not supporting indexing. If there is a default value, often you can is ISNULL or coalesce to convert NULLs to default values directly in the SQL:

maxid = cursor.execute("select coalesce(max(id), 0) from users").fetchone()[0]

In this example, coalesce(max(id), 0) causes the selected value to be 0 if max(id) returns NULL.

Comment by ratul...@gmail.com, Sep 26, 2009

Nice start

Comment by karen.he...@gmail.com, Oct 29, 2009

Very helpful! Thank you.

Comment by prah...@gmail.com, Nov 12, 2009

Very very very nice ! I need to access a lot of mdb, so i really need your library. Great job !

Comment by zensoc...@gmail.com, Dec 30, 2009

It would be good to have an insert example in here. I recall that I need to do something to commit the contents, but I'm having trouble finding what it is looking through here...

Comment by e.h.doxt...@accenture.com, Jan 26, 2010

Just as an FYI... If you're using MS Access 2007, there are some subtle differences in the connection string:

conn = pyodbc.connect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=<path to MDB or ACCDB>;")

Also, you need to use the square brackets notation if your column has spaces or nonstandard characters. I prefer an alias:

cursor.execute("SELECT Cust.[ZIP CODE] AS ZIPCODE FROM Cust")
for row in cursor:
	print row.ZIPCODE
Comment by sgar...@gmail.com, Feb 16, 2010

If I updated a table how would I commit the changes to the DB. I was expecting to commit the changes in the following manner but was unsuccessful cursor= conn.cursor() cursor.excute("update eqpt set notes='BOB' where serialno='SAM'") conn.commit conn.close

please feel free to contact me at sgarc90@gmail.com

Comment by moh...@gmail.com, Apr 8, 2010

why am not able to provide the server ip number instead of the odbc connection name.! DRIVER={SQL Server};SERVER=10.2.33.150;DATABASE=name;UID=user;PWD=password

i get this error pyodbc.Error: ('IM003', 'IM003? Specified driver could not be loaded due to system error 126: The specified module could not be found. (SQL Server, %WINDIR%\\system32\\SQLSRV32.dll). (160) (SQLDriverConnectW)')

Comment by aeben...@gmail.com, Apr 22, 2010

Im a newbie at this. I am able to connect to MS SQL server and acquire a cursor. When printing out the contents to screen I get ASCII characters. This seems like a common issue. How do i convert MS SQL to a readable binary format?

>> cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=myserver;DATA ALED;UID=me;PWD=pwd') >> cursor = cnxn.cursor() >> cursor.execute("SELECT spd FROM spds") pyodbc.Cursor object at 0x00C26870> >> row = cursor.fetchone() >> row <read-only buffer for 0x00CB03B8, size 256, offset 0 at 0x00BF1320>, ) >> print row >> print row1? >> print row0?

ë ►§ ë

Please feel free to contact me at aebenavi@gmail.com Thanks

Comment by bhadri.t...@gmail.com, Jun 11, 2010

i am not able to read data feom access file from python

Comment by moyedans...@gmail.com, Aug 31, 2010

I'm new in python development. I just import pyodbc. Can any one tell me the overall process of connecting MS SQL server? I'm stuck in connection string.

Comment by ashish...@gmail.com, Sep 23, 2010

Hi, I need to insert variables inside the SELECT statement in above statement. How to do it? Eg:cursor.execute("SELECT Top 100 FROM tblLabResultsHeader where Firstname="+a" and Lastname='"+b"' and DOB='"+c"' and OrderCreated?>='"+d"'")

where a, b, c and d are variables.

Comment by joshliz...@gmail.com, Sep 28, 2010

@moyedansari

yea just import pyodbc it will look like this:

import pyodbc

connection = pyodbc.connect('DRIVER={SQL Server};SERVER=yourServer;DATABASE=yourDatabase;UID=yourUsername;PWD=yourPass')

#then to call the database just use a cursor 
cur = connection.cursor() 
cur.execute("SELECT tablex.id, tablex.name FROM tablex")

for row in cur:
    print "ID: %s"% row.id
    print "NAME: %s" row.name

##OR the one-line way

cur.execute("SELECT tablex.id, tablex.name FROM tablex")

resultList = [(row.id, row.name) for row in cur]
Comment by joshliz...@gmail.com, Sep 28, 2010

@ashish391

I don't quite understand the syntax of your SELECT statement but you should put in ? marks where you have variables:

cursor.execute("SELECT top 10 FROM someTable WHERE id = ? and name = ?", (idVariable, nameVariable))
Comment by ashish...@gmail.com, Oct 4, 2010

Thank you.

Comment by ashish...@gmail.com, Oct 4, 2010

I got it, it should be:

cursor.execute("SELECT Top 100 FROM tblLabResultsHeader where Firstname='"+a+"' and Lastname='"+b+"' and DOB='"+c+"' and OrderCreated??>='"+d+"'")

where a, b, c and d are variables.

Comment by project member mkleehammer, Oct 12, 2010

ashish391, you really don't want to build a SQL statement unless absolutely necessary, since that is how most SQL injection attacks occur. Use "?" placeholders and pass the variables at the end:

cursor.execute("select top 100 * from tbl where firstname=? and lastname=?", a, b)
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()

#select all tables from all databases
cursor.execute("select t1.TABLE_SCHEMA field1,t1.TABLE_NAME field2  from `TABLES` t1;")
rows = cursor.fetchall()
for row in rows:
    print "%s.%s" % (row.field1,row.field2)
Comment by boris.va...@forces.gc.ca, Oct 25, 2010

I am new to python and pyodbc and I am looking for help with fetching data from an MS Access 2003 database. I am using Ubuntu 8.04, Python 2.5.2, pyodbc 2.1.8, unixODBC 2.2.11-16.

The connection is open and cursor generated without problems

connection_string = "DSN=py_access" connection = pyodbc.connect(connection_string) cursor = connection.cursor()

But the data comes back with extra non-ascii characteres inserted or appended. For example,

cursor.execute("SELECT id, eventid, name FROM tblEvents") row = cursor.fetchone() print row.id, row.eventid, row.name print row

yields

49, 20m, ABC(non-ascii-character) (49, '20\x00m', 'ABC\x00\b1')

where as the expected values are

10, 20, ABC

I tried adding CHARSET=UTF8 to the connection string, (connection_string="DSN=py_access;CHARSET=UTF8") but it did not help.

Does anyone know what causes insertion of non-ASCII characters into the output? Is there a way to get around it?

Thank you in advance, Boris.

Comment by ew2...@gmail.com, Nov 23, 2010

on a linux machine using freetds, the connection string becomes:

import pyodbc
conn = pyodbc.connect("DRIVER={FreeTDS};SERVER=dsn;UID=username;PWD=password;DATABASE=database_name")

where the "dsn" is defined in the /etc/freetds/freetds.conf file:

...
[MYMSSQLSERVER]
        host = 192.168.1.1
        port = 1433
        tds version = 7.0
...
Comment by lenfong...@gmail.com, May 7, 2011

i have installed pyodbc-2.1.8.win32-py2.7.exe in python 2.7

then my code: import re import os import stat import pyodbc source = 'DRIVER={SQL Server};SERVER=localhost;DATABASE=test;UID=sa;PWD=123456' db = pyodbc.connect(source) cursor = db.cursor() cursor.execute('select url,term from html_url where term="103"') for i in cursor.fetchall():

url_str=str(i0?) print url_str

when i run it error information: Traceback (most recent cast last)

file "pyodbc.py",line 6,in<module> import pyodbc file "c:\myprogram\test.py" line 9,in<module> db=pyodbc.connect<source> attributeError :'module' object has no attribute 'connect' i am new to python what's the problem with my program? please tell me

Comment by lyili...@gmail.com, May 26, 2011

anyway,good! thanku and i think the place holder is really GOOD! i do not need to create so many SQL statements looks like the same one!

perfect

Comment by lyili...@gmail.com, May 27, 2011

i have a prob

list=['2011-5-28','2012-3-1'] cursor.execute("""select from table where date = ?""",[list0?])

here,list0? is string ,but the place holder in sql statement need datetype ,which is a type in SQLSERVER2005

i donot know how to transform the string format of python to datetype SQL need

thank u so much!

Comment by moons...@gmail.com, Jun 18, 2011

>Just as an FYI... If you're using MS Access 2007, there are some subtle differences in the connection string: >conn = pyodbc.connect("Driver={Microsoft Access Driver (.mdb, .accdb)};DBQ=<path to MDB or ACCDB>;")

I cannot connect to a .accdb file this way (Python 2.7.1 x64, Win7 64).

conn = pyodbc.connect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C\\DB.accdb;")

results in: Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnectW)') May the 64-bitness be a problem?

Comment by lgmoquev...@gmail.com, Jun 21, 2011

A way to retrive values by names passed as params

def getNamedRowValues(rcrdset, rowNum):
    row = rcrdset[rowNum]
    dscrb = rcrdset[0].cursor_description   #retrives a list of tuples containing fields descriptions
    colnm = []                              #to tetrive column (field) names
    for nm in dscrb:
        colnm.append( nm[0] )
    l = len(colnm)
    out = {}                                #to prepare a dictionary output
    for i in range(l):
        out[colnm[i]]=row[i]                #linking name-value as dictionary entry
    return out
def getColumnValue(rcrdset, rowNum, columnName):
    namedRow = getNamedRowValues(rcrdset, rowNum)
    return namedRow[columnName]
# use
import pyodbc
dsn="DSN=MyDsnName;PWD=MyPwd"
cnnctn = pyodbc.connect(dsn)
cnnctn.autocommit = True                    #To ensure updating (other choises - updating is not sure)
cursor = cnnctn.cursor()
sql = "My SQL Query, in this case to retrive data"
cursor.execute(sql)
recordset = cursor.fetchall()
column = 'TheColumnINeedName'
line = X #           X is integer, The row I need 
MyValue = getColumnValue(recordset, line, column)  
#ilustrates how to retrive data whit column names passed  as params

Hope this lines help somebody

Comment by lgmoquev...@gmail.com, Jun 21, 2011

A response to the question:

# ... >Just as an FYI... results in: Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager]
# Data source name not found and no default driver specified (0) (SQLDriverConnectW)') 
# May the 64-bitness be a problem?

Just go to Control Panel -> System and Security -> Administrative Tools -> ODBC Data Sources and define a DSN for your database

Comment by satish.a...@gmail.com, Jun 30, 2011

HI All,

I am working on python connection with SQL Server. I could establish the connection and derive data. I have a table with column 1 containing the person_name (key column) and another column containing marks of that person. I could derive the data independently in each column like row.Person_name and row.marks from following code.

cursor.execute('SELECT Person_name,marks FROM Food') row = cursor.fetchall()

My requirement is how to derive data such that row.marksPerson_name? will give the marks data of that person.? i.e, marks should be a function of Person. Is it possible? Please help me out.

Thanks, Satish

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

Hi Satish reading the code line you wrote, I thought It was not functional. Perhaps u need some lines as

sSql = 'SELECT Person_name,marks FROM Food'
cursor.execute(sSql)
rows = cursor.fetchall()
for row in rows:
    DoSomeThing(row.Person_name)
    DoOtherThing(row.marks)

Note: once you read your data into the for statement it "disapear" from cursor it isn´t persistent. If you need it again you must requery. So if u'll need it on future, export it to a persistent object like a global array or collection

hope it helps

Comment by sinopt...@gmail.com, Jul 23, 2011

I try to use code that working on windows fine, on linux. My connection string is:

DRIVER={PSQL};SERVER=localhost;DATABASE=vusb_test;UID=devila;PWD=xxx;

I'm using unixODBC & psqlODBC. Driver is configured (isql work fine). But when, i try using code working on Windows (change only driver name) i getting error:

Traceback (most recent call last):
  File "db_pg.py", line 291, in <module>
    PSQL.connect_db()
  File "db_pg.py", line 47, in connect_db
    self.connection = pyodbc.connect(self.ODBC_connection_string,autocommit=True)
pyodbc.Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnectW)')

How fix it?

Comment by ianmdjam...@gmail.com, Jul 26, 2011

hi, I am new to databases and ODBCs and I am trying to connect mysql to pyodbc. To this end I am using the mysqltest script with the mysql test db. In the command line I am passing the DRIVER and DATABASE as suggested but I am unable to connect. Prior to this I tried using the Accesstests with the access DBs supplied and I was successful. So I think it has something to do with the ODBC driver I am specifying and the path to the mysql test db. In the first case I have specified the same ODBC driver version that was used for the access db. In the second case I have tried specifying various paths to the mysql test db but nothing seems to work. Here is the command line entry with the error:

C:\pyodbc-2.1.7\tests>python mysqltests.py "DRIVER={MySQL ODBC 3.52 Driver};DATA BASE=C:\Program Files\MySQL\MySQL Server 4.1\data\mysql\test;" Traceback (most recent call last):

File "mysqltests.py", line 669, in <module>
main()
File "mysqltests.py", line 652, in main
cnxn = pyodbc.connect(connection_string)
pyodbc.Error: ('IM002', 'IM002? Microsoft?Driver Manager? Data source na me not found and no default driver specified (0) (SQLDriverConnectW)')

I am running this from the test dir within the pyodbc 2.1.7 for python 2.6 on Windows xp pro service pack 3. I have not included a separate ODBC driver for the mysql.

I am clearly making a wrong assumption somewhere, for which I apologize, but any help would be great.

Thanks, ian

Comment by harris...@gmail.com, Sep 26, 2011

Note, if you are having trouble figuring out how to select from a table with a space in it, for example:

SELECT * FROM [table with space in it]

Then check the connection properties, like so:

>>> cnxn = pyodbc.connect(etc etc)
>>> ident_quote = cnxn.getinfo(pyodbc.SQL_IDENTIFIER_QUOTE_CHAR)
>>> print ident_quote

So you could now do something like this:

'SELECT * FROM {iq}table with spaces{iq}'.format(iq = ident_quote)
Comment by mitra.go...@gmail.com, Oct 10, 2011

Hi, I am using Sybase ASE ODBC Driver. The code inserting records in the database but connection.commit is not working although .rowcount returns 1 as it is supposed to: import pyodbc

def ExecuteSql?(conn, sql):

connection = pyodbc.connect(conn) cursor = connection.cur. sor() inserted=cursor.execute(sql).rowcount connection.commit() connection.close() print "inserted",inserted

def TestSql?():

conn = "DSN=...;UID=...;PWD=..." sql = "insert into am_status(next_avail_id,block_online,fiscal_year,prev_fiscal_year,prev_fiscal_year_freeze)values(3150,'N',2011,2010,'Y')" ExecuteSql?(conn,sql)

if name == 'main':

TestSql?()

Could you please help?

Comment by tutue...@gmail.com, Oct 18, 2011

Hi, This is Cool!! I am able to connect to my DB, my question is how do I modify a row/record which I have selected? Thanks

Comment by mapbo...@yahoo.com, Nov 7, 2011

I am working with Python 3.2 and have downloaded pyodbc-py3-3.0.1-beta04.win32-py3.2. My code is relatively simple and yet I can't get it to work. Here is the code: import pyodbc cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=999.99.99.99;DATABASE=UDT4;UID=SA;PWD=SA') cursor = cnxn.cursor() cursor.execute("SELECT I_EventNumber? FROM IIncident") for row in cursor.fetchall():

print(row)

The error message I get is:

Traceback (most recent call last):

File "H:\EclipseProjects?\Database2\src\Trial19.py", line 3, in <module>
cnxn = pyodbc.connect('DSN=CAD2;PWD=SA')
pyodbc.Error: ('28000', "28000? Microsoft?SQL Server Driver?Server?Login failed for user ''. The user is not associated with a trusted SQL Server connection. (18452) (SQLDriverConnectW); 28000? Microsoft?SQL Server Driver?Server?Login failed for user ''. The user is not associated with a trusted SQL Server connection. (18452)")

Can anyone help me out with this? At home I connected with my local instance of SQL Server, but here at work it is proving to be more difficult.

Comment by mapbo...@yahoo.com, Nov 7, 2011

Please disregard my previous post. I rewrote the entire script and it is working fine now.

Comment by I.Remi...@gmail.com, Feb 8, 2012

I had problems connecting to the database dBase (.DBF). It turns out that this problem is common. And a Google search did not find the answers.

Here is s a solution that works on my system (Win7 (64), py2.7.2 (32), pyodbc-3.0.2.win32-py2.7.exe (32) installed). I think it works everywhere.

conn = pyodbc.connect(r"Driver = {Microsoft dBASE Driver (*.dbf)}; DBQ=<path to dir w/files>", autocommit = True)
cursor = conn.cursor()
cursor.execute("... FROM <file.dbf> ...")

In other words: 1). In the connect function must be specified "autocommit = True" 2). In DBQ need only specify the path to the folder where the desired file DBF (Not the file. The folder!) 3). DBF file is specified in an SQL query.

Example of connection with the "C:\databases\data.dbf" and getting all data:

conn = pyodbc.connect(r"Driver={Microsoft dBASE Driver(*.dbf)}; DBQ=C:\databases", autocommit=True)
cursor = conn.cursor()
cursor.execute("SELECT* FROM data.dbf")

I'm not a native speaker. Translated using Google. Sorry if that ..

Comment by brianau...@gmail.com, Aug 7, 2012

How can I use pyodbc to make a backup? The following happens:

>>> cnxn = pyodbc.connect('DRIVER={FreeTDS};SERVER=%s;DATABASE=%s;UID=%s;PWD=%s' % ('srvr.domain','mydb','usr','pass')) >>> cursor = cnxn.cursor() >>> cursor.execute("""BACKUP DATABASE ? TO DISK = ? WITH FORMAT""",['mydb','C:\\folder\\backup']) Traceback (most recent call last):

File "<stdin>", line 1, in <module>
pyodbc.ProgrammingError?: ('42000', "42000? FreeTDS?Server?Incorrect syntax near 'mydb'. (102) (SQLExecDirectW)")

Thanks!

Comment by shlomo.a...@gmail.com, Aug 15, 2012

brianauron@: Can it be that port number is missing? Generally, FreeTDS (unless DSN is specified) requires PORT specification. Sadly, this is not well documented

Comment by r...@bandanashop.com, Nov 28, 2012

Worked like a dream - probably the fastest piece of code I've ever written in 20 years! Loving Python.

Comment by meetjati...@gmail.com, Nov 28, 2012

Hi everyone, i am new to Python and pyodbc .I have to basically insert and update tables in Microsoft SQL Server 2008 R2 from a .csv file depending upon whether each row is already present in the database table or not.From my research so far it seems that python and pyodbc can be used to automate the process.It would be very helpful if someone who has done similiar thing before give some pointers how to proceed about it.Also i want to ask the source file is a CSV file,do i need to convert it to some other format.It would also be useful if someone can post some sample code for the same here.

Comment by vkabac...@gmail.com, Jan 25, 2013

Thank you very much for this quick start !!!

meetjati, see http://www.simple-talk.com/sql/database-administration/python-for-the-sql-server-dba/

Comment by tb09...@gmail.com, Mar 8, 2013

Hi, I would like to create an Access Database with pyodbc and was wondering how to do this. The methods I found didn't really lead me to the conclusion. Is it possible at all or do I always have to have at least an empty database at hand? Cheers, Tom

Comment by sourthwe...@gmail.com, Apr 21, 2013

why i connect sqlserver too slow with pyodbc. it spend 4 seconds every time my var is:py2.7 sqlserver 2008.

connectSql = 'DRIVER='+'{'+dbInfo['driver']+'};'+'SERVER='+dbInfo['server']+';DATABASE='+dbInfo['database']+';UID='+dbInfo['username']+';PWD='+dbInfo['password'] connection = pyodbc.connect(connectSql)

Comment by erpratee...@gmail.com, Jul 16, 2013

How do we test using pyodbc whether the connection has been successfully made or not? I am using MS Sql Server

Comment by Dennydr...@gmail.com, Oct 31, 2013

Hello, Im new to connecting databases with python and have a newbie question. I have paradox files that I am trying to retrieve information from. (example = paradox.db) What would a connect statement look like on windows 7 with Python 3.3 look like for connecting to these files. Thanks.

Comment by nandha....@gmail.com, Nov 25, 2013

how to get execution time without using stored procedure

Comment by miten...@gmail.com, Jan 15, 2014

In case your SQL is not Stored Proc.

usage of 'xyz != NULL' in query, will give the error -> "pyodbc.ProgrammingError?: No results. Previous SQL was not a query."

Use 'is not null' instead.

Comment by sre...@gmail.com, Apr 27, 2014

I am an ACCESS developer. Typically my SQL queries are built up from a number of intermediate queries. This helps to keep the SQL statements simple. I can't see how this could be done using the pyodbc, except by creating a table from each intermediate query. Can anyone advise?

Comment by clair.ju...@gmail.com, May 5, 2014

read also this: http://code.google.com/p/pyodbc/wiki/Cursor helped me create a login system

Comment by m.bramal...@gmail.com, Jun 2, 2014

how do i execute one query, then use the results of that query to loop through another query for example select id from orders then loop all the ids through select name from customer where order_id = ?

Comment by srichter...@gmail.com, Jun 13, 2014

DON'T USE "DATABASE" FIELD!!!! I tried for hours trying to figure out why I could not connect using the sample connect string above. By dumb luck and persistence I connected the field DRIVER to SERVER then to UID and PWD and it finally worked.

Comment by liverpoo...@gmail.com, Jul 2, 2014

Hi everyone. Is it possible to connect to an Oracle Database from a Raspberry Pi, using this library? Anyone succeeded? Thank you.

Comment by jubin.ku...@gmail.com, Jul 6, 2014

Hi All, how are stored procs called ?

Comment by shravank...@gmail.com, Oct 20, 2014

Hi all,

I want to transfer a table from teradata to greenplum using python import pyodbc statements. Is it possible to directly transfer it without making a .csv file on my system?

Comment by blueAtla...@gmail.com, Dec 21, 2014

Hi, All,

I need to access IBM netezza SQL database from python on win 7. I have installed Eclipse and python 3.2, pydev, and pypyodbc (https://pypi.python.org/pypi/pypyodbc/1.3.3). It worked well for MS SQL server.

But, it got error for IBM netezza.

import pypyodbc
conn = pypyodbc.connect("DRIVER={NetezzaSQL};SERVER={my_server_name};DATABASE={my_database};UID=a_user;PWD=apwd")
pypyodbc.DatabaseError?: ('08001', '08001? Client unable to establish connection')

I can access the database from IBM Aginity workbench without any problems on the same desktop and laptop.

Any help would be appreciated.

thanks !

Comment by nutri.do...@gmail.com, Jan 13, 2015

Schade ist für mich zu kompliziert, muss ich weiter suchen um eine leichte Möglichkeit für eine Datenbank zu finden.

Comment by adeshm...@allantgroup.com, Jan 20, 2015

Hi All,These are my connection parameters : driver : MySQL(this is a JDBC driver connection) server : idb1

my python code (i am using python 3.3.0 ):

import pyodbc conn = pyodbc.connect("DRIVER{MySQL};SERVER=idb1;PORT=3306;DATABASE=test;UID=username;PWD=password;") cursor = conn.cursor()

I am getting the following error :

pyodbc.Error: ('IM002', 'IM002? Microsoft?Driver Manager? Data source na me not found and no default driver specified (0) (SQLDriverConnect)')

am i using the correct driver i have tried using DRIVER={SQL Server},DRIVER={SQL Native Client},but none work

Comment by Richard....@gmail.com, Jan 23, 2015

For using Oracle instant client 12g on Linux via pyodbc, I was able to connect with the following connection string:

"DRIVER=/usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1;DBQ=hostname:port/sid;UID=username;PWD=password;"

Comment by 494225...@qq.com, Feb 8, 2015

Does anyone get this err: cursor.execute("select sum(id) as newid from sht ")

when run this command ,it stopped and show "Exception pypyodbc.DatabaseError?"

Comment by hfgex...@gmail.com, Apr 23, 2015

Very useful guide.

Comment by smcroc...@gmail.com, Jun 17, 2015

How can I pass a list through a where clause to get multiple rows back for a dynamic insert statement

Powered by Google Project Hosting