djhenderson / pyodbc

Automatically exported from code.google.com/p/pyodbc

Overview

This project is a Python database module for ODBC that implements the Python DB API 2.0 specification.

homepage:	http://code.google.com/p/pyodbc
source:	http://github.com/mkleehammer/pyodbc
source:	http://code.google.com/p/pyodbc/source/list

This module requires:

• Python 2.4 or greater
• ODBC 3.0 or greater

On Windows, the easiest way to install is to use the Windows installers from:

http://code.google.com/p/pyodbc/downloads/list

Source can be obtained at

http://github.com/mkleehammer/pyodbc/tree

or

http://code.google.com/p/pyodbc/source/list

To build from source, either check the source out of version control or download a source extract and run:

python setup.py build install

Module Specific Behavior

General

• The pyodbc.connect function accepts a single parameter: the ODBC connection string. This string is not read or modified by pyodbc, so consult the ODBC documentation or your ODBC driver's documentation for details. The general format is:

  cnxn = pyodbc.connect('DSN=mydsn;UID=userid;PWD=pwd')
  

• Connection caching in the ODBC driver manager is automatically enabled.

• Call cnxn.commit() since the DB API specification requires a rollback when a connection is closed that was not specifically committed.

• When a connection is closed, all cursors created from the connection are closed.

Data Types

• Dates, times, and timestamps use the Python datetime module's date, time, and datetime classes. These classes can be passed directly as parameters and will be returned when querying date/time columns.
• Binary data is passed and returned in Python buffer objects.
• Decimal and numeric columns are passed and returned using the Python 2.4 decimal class.

Convenient Additions

• Cursors are iterable and returns Row objects.

  cursor.execute("select a,b from tmp")
  for row in cursor:
      print row
  

• The DB API specifies that results must be tuple-like, so columns are normally accessed by indexing into the sequence (e.g. row[0]) and pyodbc supports this. However, columns can also be accessed by name:

  cursor.execute("select album_id, photo_id from photos where user_id=1")
  row = cursor.fetchone()
  print row.album_id, row.photo_id
  print row[0], row[1] # same as above, but less readable
  

  This makes the code easier to maintain when modifying SQL, more readable, and allows rows to be used where a custom class might otherwise be used. All rows from a single execute share the same dictionary of column names, so using Row objects to hold a large result set may also use less memory than creating a object for each row.

  The SQL "as" keyword allows the name of a column in the result set to be specified. This is useful if a column name has spaces or if there is no name:

  cursor.execute("select count(*) as photo_count from photos where user_id < 100")
  row = cursor.fetchone()
  print row.photo_count
  

• The DB API specification does not specify the return value of Cursor.execute. Previous versions of pyodbc (2.0.x) returned different values, but the 2.1 versions always return the Cursor itself.

  This allows for compact code such as:

  for row in cursor.execute("select album_id, photo_id from photos where user_id=1"):
      print row.album_id, row.photo_id
  
  row  = cursor.execute("select * from tmp").fetchone()
  rows = cursor.execute("select * from tmp").fetchall()
  
  count = cursor.execute("update photos set processed=1 where user_id=1").rowcount
  count = cursor.execute("delete from photos where user_id=1").rowcount
  

• Though SQL is very powerful, values sometimes need to be modified before they can be used. Rows allow their values to be replaced, which makes them even more convenient ad-hoc data structures.

  # Replace the 'start_date' datetime in each row with one that has a time zone.
  rows = cursor.fetchall()
  for row in rows:
      row.start_date = row.start_date.astimezone(tz)
  

  Note that columns cannot be added to rows; only values for existing columns can be modified.

• As specified in the DB API, Cursor.execute accepts an optional sequence of parameters:

  cursor.execute("select a from tbl where b=? and c=?", (x, y))
  

  However, this seems complicated for something as simple as passing parameters, so pyodbc also accepts the parameters directly. Note in this example that x & y are not in a tuple:

  cursor.execute("select a from tbl where b=? and c=?", x, y)
  

• The DB API specifies that connections require a manual commit and pyodbc complies with this. However, connections also support autocommit, using the autocommit keyword of the connection function or the autocommit attribute of the Connection object:

  cnxn = pyodbc.connect(cstring, autocommit=True)
  

  or

  cnxn.autocommit = True
  cnxn.autocommit = False
  

Goals / Design

• This module should not require any 3rd party modules other than ODBC.
• Only built-in data types should be used where possible.
  1. Reduces the number of libraries to learn.
  2. Reduces the number of modules and libraries to install.
  3. Eventually a standard is usually introduced. For example, many previous database drivers used the mxDate classes. Now that Python 2.3 has introduced built-in date/time classes, using those modules is more complicated than using the built-ins.
• It should adhere to the DB API specification, but be more "Pythonic" when convenient. The most common usages should be optimized for convenience and speed.
• All ODBC functionality should (eventually) be exposed.

pyodbc's Issues

MemoryError on Mac OS X

When trying to iterate through the results of a query, I'm getting a
MemoryError on Mac OS 10.4. The same exact code runs on Linux using a
similar ODBC stack. They're both using iODBC with the latest pyodbc
connecting to the same server.

connection = pyodbc.connect(settings.ETOOLS_ODBC_DSN)
cursor = connection.cursor()
cursor.execute("SELECT TOP 5 * FROM tb_FSAR_Data_SampArtReq WHERE Job_ID =
49297")
    for ejob in results: 
        job = Job()

Original issue reported on code.google.com by [email protected] on 19 Nov 2008 at 9:47

Cannot write to Access Database

What steps will reproduce the problem?
1. Create an Access Database
2. Create a Python Script to write to the database such as: 

#!
import pyodbc

DBfile = 'C:\Documents and Settings\jakech\Desktop\ovenscheduler_be.mdb'
conn = pyodbc.connect('DRIVER={Microsoft Access Driver
(*.mdb)};DBQ='+DBfile, autocommit=True)
csr = conn.cursor()

# Insert Record into Access Database

csr.execute("INSERT INTO tmpKanban ( KanbanNo ) SELECT 991235;")
conn.commit()

csr.close()
conn.close()

What is the expected output? What do you see instead?
I expected it to write to the database, but no record was added.  I had the
same problem with a Delete * statement.  I am betting there is some write
access limitation, but haven't found it.  

What version of the product are you using? On what operating system?
2.4, Windows XP

Please provide any additional information below.

Original issue reported on code.google.com by [email protected] on 5 Jan 2009 at 10:50

Fails to connect to Quicken ODBC (Qodbc)

What steps will reproduce the problem?

1. import pyodbc
2. cnxn = pyodbc.connect('DSN=myQodbcDsn')
3. (Failure!)

What is the expected output? What do you see instead?

It gives a failed call to qodbc saying that it was missing some ODBC
register or something to that effect (I've since removed qodbc and pyodbc
as the project cannot continue without quickbooks connectivity)

What version of the product are you using? On what operating system?

Using qodbc version 9.0 and pyodbc 2.1.5

Please provide any additional information below.

Pyodbc works peachy with mysql, but fails on connection to qodbc. Kind of
disappointing. There's a free version of Qodbc you can tryout, assuming you
have a quickbooks file. And since it's ODBC this is a windows only issue.

Original issue reported on code.google.com by [email protected] on 14 May 2009 at 6:05

[deleted issue]

[deleted issue]

foreignKeys() doesn't work

What steps will reproduce the problem?
1. Create database from attached scripts.
2. Run appropriate (MSQQL or MySQL) attached python scripts.

What is the expected output? What do you see instead?
Expected: List of tables and foreign keys.
Instead: Just list of tables.

What version of the product are you using? On what operating system?
WinXP Prof SP3 
Python 2.6.1
pyodbc:
  pyodbc-2.1.1.win32-py2.6.exe, or
  pyodbc-2.1.2.win32-py2.6.exe, or
  pyodbc-2.1.3.win32-py2.6.exe  
SQL Server 2005 Express, or
MySQL 5.0.67 + mysql-connector-odbc-3.51.27

Please provide any additional information below.

Original issue reported on code.google.com by [email protected] on 2 Apr 2009 at 8:03

Attachments:

• init-mssql.sql
• init-mysql.sql
• test-mssql.py
• test-mysql.py

Update to SQL Server connection examples

What steps will reproduce the problem?
1. The information given on configuring for using Native Client is wrong 
for 2005.

What is the expected output? What do you see instead?

It suggests that the version number is required for using the Native Client 
driver. While that may be true for 2008, it's not true for 2005.

What version of the product are you using? On what operating system?

The installable version for 2.5.


Please provide any additional information below.

An example working connection string:
cn = pyodbc.connect("DRIVER={SQL Native 
Client};SERVER=localhost;UID=user;PWD=pass;DATABASE=database;")

Original issue reported on code.google.com by [email protected] on 13 Jan 2009 at 5:46

add sql to exceptions

It would be a nice option to make the SQL and parameters available to
exceptions and even in the default exception messages.

Since there might be passwords and other sensitive items, this should be an
option, not a default.

Original issue reported on code.google.com by mkleehammer on 14 Nov 2008 at 10:37

mssql unique identifier data type

What steps will reproduce the problem?

mssql has a uniqueidentifier data type.  When I select these fields directly I 
am getting inaccurate data.
if i cast the field inside the select statement I get back accurate data.

produces whacky results: select uniqueidnetifiercolum from sometable

produces good results: select cast(uniqueidentifiercolum as nvarchar(36)) as 
col from sometable

i can find no documentation on whether or not the unique identifier data type 
is supported in pyodbc.

What is the expected output? What do you see instead?

i see values like this:
 CF2FBFAC-FB1B-42E4-garbly gook that is different ever time I run it.

when I should get something like this:
 CF2FBFAC-FB1B-42E4-B63B-FE263350089C

What version of the product are you using? On what operating system?

environment
- Mac OSX 10.5 
- latest stable pyodbc
- mssql 2005
- latest Actual ODBC Driver

Please provide any additional information below.

In the "garbly gook" I have seen everything from random characters to the name 
of the python file I am running.

Original issue reported on code.google.com by [email protected] on 7 Apr 2009 at 8:32

Old SourceForge homepage does not redirect here

The old SourceForge homepage at http://pyodbc.sourceforge.net and its
download link do not redirect here and does not contain any hint about the
move. I only accidentally found that I was using an outdated version and
there is a newer version available here and on PyPI.

Original issue reported on code.google.com by [email protected] on 3 Dec 2008 at 5:38

Support SQL Server 2008's new DATE related data types

What steps will reproduce the problem?
1. SQL Server 2008 has new data types: DATE, DATETIME2, TIME and
DATETIMEOFFSET.
2. when selecting date for columns of these datatypes, returned values are
all unicode strings:
 * u'2008-01-01'
 * u'2008-01-01 00:00:00.0000000'
 * u'00:00:00.0000000'
 * u'2008-01-01 00:00:00.0000000 +00:00'

What is the expected output? What do you see instead?
 * I would suggest to use python's:
 - datetime.date for DATE
 - datetime.time for TIME
 - datetime.datetime for DATETIME2 and DATETIMEOFFSET


What version of the product are you using? On what operating system?
 * SQL Server 2008 Express, python 2.6 and pyodbc-2.1.1

Please provide any additional information below.

Original issue reported on code.google.com by [email protected] on 30 Nov 2008 at 9:41

Add iODBC/unixODBC build info to a variable

On Linux, there is currently no way to know if pyodbc was built for iODBC
or unixODBC.

(It would be better if we could dynamically choose, but I don't think
that's an option.)

Add a variable like __build__ with this info.  (I'll need to see what other
Python modules do.  Perhaps copy a variable name from sys?)

Original issue reported on code.google.com by mkleehammer on 18 Nov 2008 at 5:38

README not in source zip

The readme was renamed to .rst for github support, but the manifest was not
updated to include this new name.

Original issue reported on code.google.com by mkleehammer on 8 Jan 2009 at 3:28

python crashes while reading binary objects for MSSQL database

What is the expected output? What do you see instead?

Expected output is reading an image/binary objects from database.
Unfortunatelly for some binary objects I recieve this error message
followed by a crash of python:

assertion failed: getdata.cpp(222)
cbRead == 0 || pch[cbRead-element_size] != 0


What version of the product are you using? On what operating system?

Python 2.5.2
pyodbc-2.0.58.win32
Windows XP Professional SP 3
Microsoft SQL Server 2008 (but I am pretty sure it also occurs on 2005)

Do you have any idea how to solve the problem?

Original issue reported on code.google.com by [email protected] on 19 Jan 2009 at 9:05

pyodbc isn't quite DB API 2.0 compliant

The one issue I've run into is with connection strings. According to PEP 249:

    [1] As a guideline the connection constructor parameters should be
        implemented as keyword parameters for more intuitive use and
        follow this order of parameters:

          dsn         Data source name as string
          user        User name as string (optional)
          password    Password as string (optional)
          host        Hostname (optional)
          database    Database name (optional)

        E.g. a connect could look like this:

          connect(dsn='myhost:MYDB',user='guido',password='234$')

Of course, I realize that implementing this specific interface for ODBC may
be troublesome... plus the fact that this is only a "should", rather than a
"must". Still, it's just one of those things that would be nice. This did
actually cause me some trouble with django-pyodbc, with the way it
constructs its connection strings. You can see a ticket related to that
here: http://code.google.com/p/django-pyodbc/issues/detail?id=18

Preferably I would have refactored it to work as proposed to Django's
psycopg2 adaptor here: http://code.djangoproject.com/ticket/6710  however
that isn't really possible with the current connect interface in pyodbc.

I'm thinking it may also be possible to implement something like this
without breaking API compatibility with the 2.x series. Since currently,
kwargs are not the recommended usage for passing connection strings, then
perhaps if they are used, it could be taken as referring to the interface
mentioned above and otherwise it uses the current pyodbc connect interface.

Original issue reported on code.google.com by tarkatronic on 30 Dec 2008 at 3:32

Problem fetching NTEXT and NVARCHAR data

What steps will reproduce the problem?
1. Create NVARCHAR (or NTEXT) column in MSSQL 2005 Express database table.
   create table test_nvarchar (id int identity, nvarchar_col nvarchar(10))

   -- Insert capital U with double acute.
   insert into test_nvarchar values (N'Ű')
2. Run this python script on RedHat 9:

import pyodbc
cnxn = pyodbc.connect("DSN=SQLSERVER_SAMPLE")
cursor = cnxn.cursor()
cursor.execute("select * from test_nvarchar where id = 1")
for row in cursor:
    print row.nvarchar_col
    print repr(row.nvarchar_col

What is the expected output?
Ű
u'\u0170    '


What do you see instead?

u''

What version of the product are you using? On what operating system?
redhat 9
pyodbc 2.1.2
python 2.5.1 (UCS-2 build)
Easysoft SQL Server ODBC driver 1.1.4
SQL Server 2005 Express

Please provide any additional information below.
The script works as expected with the same setup if pyodbc 2.0.58 is used
or a "hybrid" 2.1.2 built with the 2.0.58 getdata.cpp.

Original issue reported on code.google.com by [email protected] on 12 Dec 2008 at 4:50

SQL Server 2005: XML data type is not supported?

SQL Server 2005 includes a new data type "xml". See
http://msdn.microsoft.com/en-gb/library/ms187339.aspx

When using the "SQL Native Client" driver PyODBC encounters an xml column
in a results set, an error is raised.

No error is raised when "SQL Server" driver used to connect to same server
- a unicode object is returned instead.

What steps will reproduce the problem?

1. If you don't have a suitable table to hand, create a new table with an
XML column:

CREATE TABLE [dbo].[xml_test]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[test] [xml] NULL
)

2. connect to SQL Server 2005 database using the "SQL Native Client" driver

3. execute a select from the table, no rows in the table are required

my_connection.execute( "select * from xml_test" )


What is the expected output? What do you see instead?

I would expect a unicode string containing the contents of the xml field.
Not worried about anything more fancy ;-)

What I get is an exception raised:

pyodbc.Error: ('ODBC data type -152 is not supported.  Cannot read column
test.', 'HY000')


What version of the product are you using? On what operating system?

PyODBC 2.1.5, reproduced on: Windows XP SP2 and Windows Server 2003

Original issue reported on code.google.com by [email protected] on 24 Apr 2009 at 4:04

Issue with 64-bit Linux

On my 64-bit Kubuntu Linux box I enyounter the following problem:

Decimal-values with NULL-value produce an exception when reading. This
happens because in src/getdata.cpp, function GetDataDecimal, the variable
cbFetched is of type SQLLEN which is 64 bits long. The return value -1 for
a NULL value is a 32 bit number, so it is not -1 in 64 bits and the
function tries to convert the empty string '' to a decimal which raises an
exception.

Possible solution to the problem: cbFetched must be of a 32-bit type,
regardless of the systems bit size. I am sorry, but I don't know, which
type could be the right one for this.

Regards, Axel

Original issue reported on code.google.com by [email protected] on 8 Jan 2009 at 7:45

pyodbc.connect method no longer handles autocommit passed as non-keyword argument

What steps will reproduce the problem?
1. Call pyodbc.connect("DSN=MyDSN", True)
2.
3.

What is the expected output? What do you see instead?
This call worked with version 2.0.36. In 2.1.3, this error is the result:
TypeError: function takes at most 1 non-keyword argument

What version of the product are you using? On what operating system?
2.1.3, on Windows XP SP3

Please provide any additional information below.
According to the documentation, the method should support up to 3 non-
keyword args:
connect(str, autocommit=False, ansi=False, **kwargs)

Original issue reported on code.google.com by [email protected] on 22 Jan 2009 at 3:00

Building pyodbc with mingw32

With the attached changes I can successfully build pyodbc using Mingw32 on
windows and python 2.5.

Original issue reported on code.google.com by [email protected] on 18 Feb 2009 at 4:52

Attachments:

• pyodbc.diff

2.1.3 doesn't compile under Linux

The current version 2.1.3 doesn't compile under (my) Linux. I had to apply
the attached patches which, for me, seem to be harmless and just syntactic
sugar.

Regards

Axel

Original issue reported on code.google.com by [email protected] on 31 Dec 2008 at 8:44

Attachments:

• patch

problems with decimal type on db2

I am successfully connected to a db2-database server (on a mainframe as
well as on a aix workstation).

Doing a select on a table with columns with decimals (with positions after
the decimal point) only yields values without positions after the decimal
point (the value is 10 to the power of the positions after the decimal point).

Example: The value in the table is 3.14 and the type is decimal(n, 2). Then
the result in Python is Decimal("314").

Original issue reported on code.google.com by [email protected] on 12 Dec 2008 at 8:53

Trap exceptions from SQL Server

I'm having trouble trapping exceptions returned from pyodbc cursor
execute calls. Maybe I am referencing the wrong exceptions ???? I have
googled around quite a bit and it seems my code should
work...Basically, I have tried trapping all the error codes, but none
of them work. I'm connected to MS SQL Server database.

conn = pyodbc.connect( 'dsn=MyDSN')
cur = conn.cursor()
try:
     cur.execute ( 'SelectJJJJJ * from Test')
except pyodbc.Error:
     print 'Error exc'
except pyodbc.DataError:
     print 'Data Error'
except pyodbc.DatabaseError:
     print 'Database error'
..... catch rest of the errors......
except:
     print 'Why did I get here!!!!! %s' % sys.exc_info()

I always end up with 'Why did I get here!!!!!!!'
(<type 'instance'>, <pyodbc.ProgrammingError instance at 0xb7d0e32c>,
<traceback object at 0xb7d0fb1c>) 

python 2.4.3
RedHat 2.6.18-128.elxen
pyodbc 2.1.4
easySoft ODBC driver for SQL Server 1.1.4

Thanks

Original issue reported on code.google.com by [email protected] on 6 Mar 2009 at 11:28

Not error raised

What steps will reproduce the problem?
1.
2.
3.

What is the expected output? What do you see instead?
[The output must be (Using console SQL from MSSQL Server)]
(1 filas afectadas)

@@error1 is 0.
Servidor: mensaje 547, nivel 16, estado 1, línea 1
Instrucción INSERT en conflicto con la restricción COLUMN CHECK
'CK_tm_asunto'. El conflicto ha aparecido en la base de datos 'ctrl_emi',
tabla 'taller_movimiento', column 'asunto'.


[What happend instead inside of try block, not enter in except block]
>>> u.execute(s)
1

What version of the product are you using? On what operating system?
Windows XP SP3
SQL Server 2000
pyodbc 2.0.58 (I use Zope so I need using with python 2.4. I not know how
compile with this version)

Please provide any additional information below.
The query is
SET XACT_ABORT ON
BEGIN TRANSACTION
DECLARE @err int
UPDATE taller SET
  comercial =  ''
where taller = 1
select @err = @@error
PRINT '@@error1 is ' + ltrim(str(@@error)) + '.'
IF (@ERR <> 0) BEGIN
   raiserror('error al actualizar',16,1)
   ROLLBACK TRANSACTION 
END
insert into taller_movimiento (estado, taller, fecha, asunto, observacion
) values(
  '14',
  1,
  '',
  '',
  ''
)
select @err = @@error
PRINT '@@error2 is ' + ltrim(str(@@error)) + '.'
IF (@ERR <> 0) BEGIN
   raiserror('error al insertar',16,1)
   ROLLBACK TRANSACTION 
END
COMMIT TRANSACTION

Original issue reported on code.google.com by [email protected] on 1 Dec 2008 at 7:21

Cannot reliably INSERT into Access db if

What steps will reproduce the problem?
I'm trying to do simple INSERTs into an Access 2007 (.accdb) database which
is connected via ODBC. I noticed that if I attempt to insert multiple rows
one after the other into a table, the last row does not get inserted. This
happened to me only if I set autocommit=True when creating the db
connection. The problem does not happen if the commits are performed
manually. No errors were ever returned.

What version of the product are you using? On what operating system?
pyodbc-2.1.3.win32-py2.6 on Windows XP x64

Please provide any additional information below.
This was really frustrating. I wonder if the problem has to do with pyodbc
or the ODBC Access driver. I find autocommit=True to be really useful for
simple transactions, and I'd prefer not having to do the commit manually
for these.

Original issue reported on code.google.com by [email protected] on 5 Apr 2009 at 8:40

Insertion into Excel file via ODBC

I'm using the latest version of pyodbc with Python 2.5 on Windows.

I connect to a simple Excel file which is linked through ODBC, with
autocommit=True. I can do SELECT queries without any trouble. When I an
INSERT query, it seems like it goes through, but it really doesn't. After
doing the insert query, if I do a select query, I can actually see the
newly inserted rows. However, after I close Python and open the Excel file,
I don't see any of the newly inserted rows there.

I know that read only is disabled in the ODBC data source configuration,
and I also tried disabling it explicitly in the connection string, but this
was of no use.

I have had no problem inserting stuff into other data sources such as
Access files, etc.

This issue is driving me nuts.

Original issue reported on code.google.com by [email protected] on 4 Feb 2009 at 4:59

Support for VALUES IDENTITY_VAL_LOCAL() INTO :hostvar

This is an enhancement request to add support for the IDENTITY_VAL_LOCAL
function as described in:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.
udb.doc/admin/r0004231.htm

There are a couple of C code examples at the end of that page.

In any case, thank you for creating pyodbc.

DenesL

Original issue reported on code.google.com by [email protected] on 3 Mar 2009 at 3:24

karrigell crash

with karrigell
when refresh webpage, python.exe crashes

Original issue reported on code.google.com by [email protected] on 7 Feb 2009 at 11:04

pyodbc.Error 'HYC00'

What steps will reproduce the problem?
1. Create a System DSN named gb with dbase driver by ODBC data source manager.
2. Open Python Interpreter;import pyodbc;
3. Call [[[ conn = pyodbc.connect("DSN=gb") ]]]

What is the expected output? What do you see instead?
see additional information.

What version of the product are you using? On what operating system?

pyodbc-2.1.3.win32-py2.6.exe & windows XP + SP2 Simplified Chinese

Please provide any additional information below.

================
>>> conn = pyodbc.connect("DSN=gb")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
pyodbc.Error: ('HYC00', '[HYC00] [Microsoft][ODBC dBase
Driver]\xbf\xc9\xd1\xa1\
xb5\xc4\xb9\xa6\xc4\xdc\xce\xb4\xca\xb5\xcf\xd6  (106)
(SQLSetConnnectAttr(SQL_A
TTR_AUTOCOMMIT))')
>>>
================

Original issue reported on code.google.com by [email protected] on 29 Jan 2009 at 9:02

Attachments:

• odbc.jpg

Add a timeout attribute to the connection

Here is a patch to set the timeout for connections and queries.

The timeout is set once for a cursor when it is created. The query timeout
of a cursor can therefore not be changed.

Original issue reported on code.google.com by [email protected] on 17 Apr 2009 at 12:17

Attachments:

• pyodbc_timeout.patch

add support for file dsn's

(copied from SourceForge issue 2306749)

I cannot find any online information to show that pyodbc supports file
dsn's. I would like to request this feature be added, as for now I am
forced to use other scripting solutions that does support file dsn's such
as perl's DBI:ODBC module.

Original issue reported on code.google.com by mkleehammer on 18 Nov 2008 at 4:28

Eliminate hashlib/sha1 use

Not necessary and is error-prone.

Original issue reported on code.google.com by mkleehammer on 11 Dec 2008 at 3:46

char field unicode problem on Mac OSX 10.5

> What version of the product are you using? On what operating system?
Mac OSX 10.5, Python 2.5, latest stable pyodbc, MS SQL DB using the
Actual SQL ODBC driver.

I am getting the following error message when trying to access certain
char fields:

Assertion failed: (size >= 0), function PyUnicodeUCS2_EncodeUTF8, file
Objects/unicodeobject.c, line 1416. 

Original issue reported on code.google.com by [email protected] on 20 Jan 2009 at 9:17

Unicode query strings

I am unable to define a unicode query string to pass to the Cursor.execute().  
The following code 
illustrates my problem.  I apologize if this is not a real issue and simply my 
ignorance.

What steps will reproduce the problem?

#!/usr/bin/env python
# encoding: utf-8
"""
pyodbc_unicode_test.py

environment
- Mac OSX 10.5 
- Python 2.5.1
- latest pyodbc
- mssql
- latest Actual ODBC Driver

"""
import pyodbc as db

unicode = u'\ufffeselect * from wasp..locations'
string  = 'select * from wasp..locations'
utf8 = unicode.encode('utf-8')

con = db.connect('DSN=xxx;UID=xxx;PWD=xxx')
cursor = con.cursor()

f = cursor.execute(utf8)
s = cursor.execute(string)
u = cursor.execute(unicode)

assert type(f) is db.Cursor, "executing utf8 fails: %s" % f
assert type(s) is db.Cursor, "executing string fails: %s" % s
assert type(u) is db.Cursor, "executing unicode fails: %s" % u

con.close()

#output
#######
#Traceback (most recent call last):
# File "pyodbc_unicode_test.py", line 29, in <module>
#    assert type(u) is db.Cursor, "executing unicode fails: %s" % u
#AssertionError: executing unicode fails: -1


What is the expected output? What do you see instead?

I expected the u variable to be of type pyodbc.Cursor after the execute 
function was called.  
Instead it returned a -1.


What version of the product are you using? On what operating system?
environment
- Mac OSX 10.5 
- Python 2.5.1
- latest pyodbc
- mssql
- latest Actual ODBC Driver

Please provide any additional information below.

Thanks in advance for any help you can provide with this issue.

Original issue reported on code.google.com by [email protected] on 28 Jan 2009 at 2:22

Implement SQLFetchScroll

In Python based Web applications it is important to do pagination. SQL
syntax for this varies from a server to server (limit by,
ROW_NUMBER() OVER () etc). However ODBC function SQLFetchScroll provides a
mechanism that could be used to retrieve just a window of resulting data
set in DBMS independent way.

It could work as follows: a new method skip(count) on Cursor objects would
use SQLFetchScroll to skip a given number of rows.

Original issue reported on code.google.com by [email protected] on 26 Dec 2008 at 3:47

Regain performance lost since 2.0.36

As reported by Joe Salmeri (via email), the 2.1.x is slower than the 2.0.x
branch.

After examining some ODBC trace logs, we determined that it was extra
SQLDescribeParam calls that were added to work around binding None/NULL.

When passing parameters, pyodbc has to tell the driver the data type of the
parameter.  When None is passed, pyodbc obviously doesn't know -- it is
NULL and there is no way to tell if it is going into a character column, an
integer, etc.

In the 2.0 branch, pyodbc always set the data type to char, but SQL Server
will not let you insert a character value into a binary column even if you
are passing NULL.  To fix this, I use SQLDescribeParam, if supported by the
driver, to determine the correct data type.  Unfortunately, this adds 1
driver call per parameter for every query, which is way too much overhead.

Original issue reported on code.google.com by mkleehammer on 29 Dec 2008 at 4:37

Cannot access linked tables in Access through pyodbc

What steps will reproduce the problem?
1. In an Access db, create a link to a table in a SQL Server db using a DSN.
2. Connect to the Access db with pyodbc using a DSN.
3. Execute a SQL string that selects data from the linked SQL Server table. 

What is the expected output? What do you see instead?
I expect the results of the query to get returned to the cursor, but
instead I get this error message (rocky2lemma is the name of the SQL Server
DSN): 

Traceback (most recent call last):
  File "C:\lemma_code\test_pyodbc.py", line 17, in <module>
    cursor.execute(sqlStr)
pyodbc.Error: ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver]
ODBC-
-connection to 'rocky2lemma' failed. (-2001) (SQLExecDirectW)")

What version of the product are you using? On what operating system?
python 2.5, pyodbc 2.1.3, Windows XP

Please provide any additional information below.
I can select data from any of the local Access tables using pyodbc, but I
can't select data from any of the linked SQL Server tables through pyodbc.
If I open the Access db, I can open the linked SQL Server tables and run
queries against them so there is no problem with the connection or
permissions. It's only when I try to select data from the linked tables
through pyodbc that I get the error. 

Sample Code:
import pyodbc

# coords is a system DSN to an Access db
connectStr = 'DSN=coords;Trusted_Connection=yes'
db = pyodbc.connect(connectStr)
cursor = db.cursor()

# PLOTS_ALL is a local Access table 
# The following code runs successfully
sqlStr = 'SELECT * FROM PLOTS_ALL'
cursor.execute(sqlStr)
print 'selected from plots_all'

# MODEL_REGION is a linked table in a SQL Server db
# The following code returns a pyodbc error before it gets to the print stmt
sqlStr = 'SELECT * FROM MODEL_REGION'
cursor.execute(sqlStr)
print 'selected from model_region'

cursor.close()
db.close()

Original issue reported on code.google.com by [email protected] on 25 Feb 2009 at 5:27

cursor.skip(n) only skips 1 not n

Using pyodbc-2.1.3.win32-py2.5.exe on win2003 sp2 and xp sp2

cursor.execute('select * from table')
cursor.skip(10)
cursor.fetchall()

here skip(10) is equivalent to skip(1)

I would expect to obtain the same result as doing
cursor.skip(1) ten times

Tested against MSSQL 2005 and db2/400 v5r3.

Original issue reported on code.google.com by [email protected] on 6 Mar 2009 at 10:02

How to always return unicode instead of str in rows

I always want unicode strings in the result set rows.

The attached patch adds the keyword argument unicode_strings to
pyodbc.connect(). If it's true, then strings are returned as unicode objects.

Original issue reported on code.google.com by [email protected] on 18 Feb 2009 at 6:00

Attachments:

• unicode_strings.diff

pyodbc seems to always use autocommit with Oracle

Is this a known problem?

I'm using the Oracle 10g client on WinXP and data is written to the
database even though I haven't set autocommit (or set it explicitely to
false) and never call the commit() method.

Original issue reported on code.google.com by [email protected] on 3 Dec 2008 at 3:05

Python 2.6 switch '-3' produces DeprecationWarning on import of pyodbc

The '-3' switch was added to Python 2.6 in order to provide warnings for
deprecated usages in Py3k. Currently the only warning I have seen is the
following, upon import:
pyodbc.pyd:6: DeprecationWarning: the sha module is deprecated; use the
hashlib module instead

This will break compatibility with Python 3.0 (although I'm not sure this
is even in the immediate roadmap)

However, given that hashlib was introduced in 2.5, you will need to use
some sort of try/except import hack to maintain the current level of
backwards compatibility. Something along the lines of:
try:
    from hashlib import sha1 as sha
except ImportError:
    from sha import sha

Original issue reported on code.google.com by tarkatronic on 1 Dec 2008 at 8:47

GIL problems

I've noticed in my multithreaded app using pyodbc with MS SQL Server 
occasional application freezes typical of the GIL not being released. It 
always seemed to happen when two threads were both calling stored 
procedures. In every case I noticed that there was a blocking SQL Server 
process with last command "exec sp_sproc_columns", I assume this is called 
by the driver when you are trying to build up the cursor.description. The 
fact that all my app threads freeze means the GIL is not released. As 
pyodbc is the only C python extension in use, it must be the one not 
releasing the GIL. 

Changing my app to use adodbapi fixed the problem 

Sorry I can't provide a test case that duplicates this problem. My app is 
huge and it would be a lot of work for me to whittle it down to a simple 
test case. If you make any changes that you think might fix this problem 
let me know and I'll rerun my app using pyodbc to see if the problem goes 
away. 

Original issue reported on code.google.com by [email protected] on 4 Dec 2008 at 3:08

.Execute returns an integer rather than a cursor object

Hello all;

I have a rather interesting problem.  I'm using Python to insert a large 
amount of data into a MS SQL database.  A portion of this data is HTML 
code.  When I attempt to run the queries that I have generated, the 
shorter ones work fine, but the HTML insertion ones fail.  And by fail, I 
mean that the connection.execute(query) command returns an integer rather 
than a cursor object.

What steps will reproduce the problem?
1. Execute the attatched query through pyodbc's connection.execute

What is the expected output? What do you see instead?
The expected output is a cursor object, containing one tuple with one 
value in it.  Instead, I am seeing an integer ... in this case, 1.

What version of the product are you using? On what operating system?
I'm running Windows XP, and I am using Python 2.6.1 and pyodbc-2.1.3.win32-
py.

Any help you can provide would be greatly appreciated.

Thanks!

Original issue reported on code.google.com by [email protected] on 18 Mar 2009 at 4:32

Attachments:

• PythonCode.txt
• query.txt

connection to excel fle using pyodbc fails

What steps will reproduce the problem?
1. set up an Excel file as a SystemDSN using Microsoft Excel Driver
2. try to connect using pyodbc.connect('DSN=<source>')
3.

What is the expected output? What do you see instead?
pyodbc.Error:('HYC00','[HYC00][Microsoft][ODBC Excel Driver]Optional 
feature not implemented (106) (SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT))')

What version of the product are you using? On what operating system?
pyodbc 2.1.2, Python 2.5.1, Excel 2002, Windows XP

Please provide any additional information below.

I get the same result using autocommit=False in the connection string, and 
using a dsn-less connection string for the same file

Original issue reported on code.google.com by [email protected] on 12 Feb 2009 at 5:01

hard crash when passing a list of sets to executemany

What steps will reproduce the problem?

<code>
import pyodbc
print pyodbc.version

SERVER, DATABASE = "SVR17", "TDI"
DSN = "Driver={SQL Server};Server=%s;Database=%s;TrustedConnection=Yes" %
(SERVER, DATABASE)
db = pyodbc.connect (DSN)

q = db.cursor ()
q.execute ("CREATE TABLE #words (word VARCHAR (100))")
q.close ()

words = set (['a'])
q = db.cursor ()
q.executemany ("INSERT INTO #words (word) VALUES (?)", [words])
q.close ()

</code>

What is the expected output? What do you see instead?

Expected an error message (as you get if you use q.execute
rather than q.executemany). Instead, crashed hard.

What version of the product are you using? On what operating system?

2.1.3 from on Python 2.6.1

Original issue reported on code.google.com by [email protected] on 7 Apr 2009 at 11:56

Missing MANIFEST.in prevents "python setup.py bdist_rpm" from working

The source distribution of pyodbc-2.1.1 is missing a MANIFEST.in file. This
means that when you try to run "python setup.py bdist_rpm" (to build an rpm
of pyodbc), it fails because the .cpp and .h files haven't been packaged
into the build environment.

If a simple MANIFEST.in file is supplied, then running "python setup.py
bdist_rpm" works flawlessly.

The MANIFEST.in should look something like:
 recursive-include . *.py
 recursive-include src *.h
 recursive-include src *.cpp

What version of the product are you using? On what operating system?
 pyodbc 2.1.1 (from zip)
 Fedora 8

Original issue reported on code.google.com by [email protected] on 4 Dec 2008 at 12:41

add context manager support to connection to automatically close

Would like to write code like this:

with pyodbc.connection(conn_string) as conn:
    #do cool things here

Original issue reported on code.google.com by [email protected] on 2 Jan 2009 at 4:33

problem with variable assignment

Hello,

What steps will reproduce the problem?
1. connected to MsSQL server
2. run the query including some assigmnets
3. runtime error raised

Example:
DECLARE @l int
SET @l = 15
SELECT @l

What is the expected output? What do you see instead?
Output should be 15.

Traceback (most recent call last):
  File "./get_all.py", line 29, in <module>
    for r in result:
pyodbc.ProgrammingError: No results.  Previous SQL was not a query.


What version of the product are you using? On what operating system?
* Ubuntu, 9.04. 
* libodbc++-dev 0.2.3+0.2.4pre compiled from sources without the unicode
* pyodbc-2.1.5 compilation with python2.6 fails;
* compiling with python25 is OK, however linking against -lodbc does not
work, so I replaced it with -lodbc++-mt
* tdsodbc 0.82-4 (from the distribution)

Please provide any additional information below.
Normal SELECT/INSERT queries are OK.

Thanks,
 Józsi

Original issue reported on code.google.com by [email protected] on 8 May 2009 at 12:18

inconsistent comparison of unicode/non-unicode strings with MySQL / ODBC

using MySQL with pyodbc, result values from table selects appear to come
back as Python unicode objects (though oddly, not from literal string
selects).   But then it also will fail to compare a unicode value to a
non-unicode value on the server, leading to this contradictory behavior:

    import pyodbc
    c= pyodbc.connect("dsn=mysql_test;TrustedConnection=Yes")

    cursor = c.cursor()

    # passes
    cursor.execute("drop table foo")
    cursor.execute("create table foo(data varchar(30))")
    cursor.execute("insert into foo values (?)", u'test')
    cursor.execute("select * from foo")
    x = cursor.fetchone()[0]
    cursor.execute("select count(1) from foo where data=?", x)
    assert cursor.fetchone()[0] == 1

    # fails
    cursor.execute("drop table foo")
    cursor.execute("create table foo(data varchar(30))")
    cursor.execute("insert into foo values (?)", 'test')
    cursor.execute("select * from foo")
    x = cursor.fetchone()[0]
    cursor.execute("select count(1) from foo where data=?", x)
    assert cursor.fetchone()[0] == 1


Where in the second test, the value of 'test' in the database does not
compare to that of u'test'.  not sure if theres an ODBC setting I should be
using here.  

Original issue reported on code.google.com by [email protected] on 15 Jan 2009 at 12:46

python.exe crashes while connecting to MSSQL Database using pyodbc.connect

What steps will reproduce the problem?
1. i have MSSQL Database on a remote machine
2. i have created a System DSN in my local machine using python script 
which uses ctypes module
3. using pyodbc module i'm trying to connect to remote database.

What is the expected output? What do you see instead?
establish connection without crashing the python

What version of the product are you using? On what operating system?
OS-: win 2003 server, 32bit
Installed python products: python 2.5,pyodbc-2.0.52,pywin32-211

Please provide any additional information below.
1) The script just exists with return code 128.
2) Sometime after(approx 20-30 mins) if i run the scripts,i was able to 
connect and no crash found.
3)After establishing the connection,if we terminate (Keyboard Interrupt)
the script without closing the connection and once again try to 
connect,python.exe crashes

The scripts terminates abnormally and doesnt give any traceback.
i can see the dumps only when i reboot the machine.

Following is the code snippet of my script that are used to do the above 
tasks
Script to Create a System DSN
----------------------------------------------
def create_sys_dsn(driver, **kw):
    "Create a  system DSN"
    ODBC_ADD_SYS_DSN = 4
    nul = chr(0)
    attributes = []
    for attr in kw.keys():
        atrbStr = "%s=%s" % (attr, kw[attr])
        #print atrbStr
        attributes.append(atrbStr)
    #print driver
    return ctypes.windll.ODBCCP32.SQLConfigDataSource(0, ODBC_ADD_SYS_DSN, 
driver, nul.join(attributes))

retCode = create_sys_dsn("SQL Server", SERVER=server, DESCRIPTION="TestDB 
DSN", DSN="TestDB", Database="testDB")
if retCode != 1:
    mesg = "Cannot Create TestDB DSN"
    print mesg
    sys.exit(1)
else:
    mesg = "TestDB DSN Created"
    print mesg
    sys.exit(0)
---------------------------------------------------------------------------

Script to connect to database
---------------------------------------------------------------------------
import pyodbc
try:
            DBCon = pyodbc.connect("DSN=TestDB;UID=tester;PWD=tester")
except pyodbc.Error, erno:
            mesg = "Unable to connect testDB Database check the UserID and 
Password"
            print mesg,erno
            return 1
return 0
:
:
:
:
DBCon.close()

Original issue reported on code.google.com by [email protected] on 17 Dec 2008 at 6:43

cursor.description type_code inconsistency

Environment:
 * SQL Server 2008
 * pyodbc version 2.1.3 (python 2.6)


What steps will reproduce the problem?
1. create a databse table with 2 columns:
  * col1    decimal(24,12)
  * col2    float(24)
2. insert one row with values (5.1, 5.1)
3. using pyodbc, run sql "SELECT * FROM TEST_TABLE", and get
cursor.description, which contains:
('col1', <type 'float'>, None, 24, None, None, True)
('col2', <type 'float'>, None, 24, None, None, True)
4. the data retrieved is:
(Decimal('5.100000000000'), 5.0999999046325684)

I expect the type for 'col1' to be decimal, not float.
So data is of correct type, but the cursor.description provides incorrect
information.

Or is there a way to get information about the python type that will be used?

Original issue reported on code.google.com by [email protected] on 1 Apr 2009 at 3:51