Issue 21: Cannot write to Access Database
Status:  NoFix
Owner: ----
Closed:  Dec 2009
Reported by jake.chu...@gmail.com, Jan 5, 2009
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.


Jan 5, 2009
Project Member #1 mkleehammer
There is actually an entire set of unit tests for Access, so it isn't that it never
writes.  (In fact, I recommend downloading/checking out the source and running
accesstests.py in the tests subdirectory.  It may help.)

I tried your script (and various permutations of it) and they all worked fine.  Are
you *sure* you are looking at the right file?

By the way, I don't think it is realted, but you have backslashes in your filename,
but you have not escaped them.  I recommend using a 'raw string' by prepending an 'r':

DBfile = r'C:\Documents and Settings\jakech\Desktop\ovenscheduler_be.mdb'

I don't see anything at this point.  Can you add the following:

for row in csr.execute("select * from tmpKanban"):
    print 'value:', row


Status: Investigating
Jan 5, 2009
#2 jake.chu...@gmail.com
Mike; 

Thank you for the info.  I was successful at executing a select statement from the
database previously (I tested that early), my only issue was with writing to the DB.
 I am going to try the suggestions that you mentioned (escaping the slashes and
testing one of your tests).  

that said, I cannot seem to figure out where you are storing the unit tests.  I'll do
some more looking, but for whatever reason I cannot see where those are liked from
this google site. 

thanks
Jan 5, 2009
#3 jake.chu...@gmail.com
Mike; 

Disregard that.  I found your test code.  I will test it tomorrow to see if my
install has a problem.  Thank you

Jake
Jan 5, 2009
Project Member #4 mkleehammer
Actually I was hoping that attempting to select the statement you just inserted would
tell us something.  Can you try selecting the value 991235 after inserting it.

The unit tests are part of the source code.  You can download the source code zip
file from https://code.google.com/p/pyodbc/downloads/list

Jan 6, 2009
#5 jake.chu...@gmail.com
Mike; 

It is odd.  What I am seeing on this end is if I do a Select statement after the
insert or Delete statement, then the script works.  But if I simply execute the
Insert with no SQL statement at the end, there is no change to the DB.  I know this
by opening the DB and looking.  I have a SQL commit, but I wonder if I am missing
some string that is necessary (for whatever reason) to commit the SQL statement.  

Jan 6, 2009
Project Member #6 mkleehammer
I'm not sure what the issue is, unless you are looking at the wrong MDB.  I've run
the tests and opened the database and it is still there.

You don't even need the cnxn.commit() call since you have turned autocommit on
anyway.  Make *sure* you are looking at the right DB and have refreshed in Access.

The only other option I can think of is that there is a bug in the Access driver you
have.  Please try running the accesstests.py script and post the results.  Part of
the results includes the version of the driver you are using.  Maybe that will help.
Jan 6, 2009
#7 jake.chu...@gmail.com

Z:\>c:\scripts\accesstests.py test.accdb
  File "c:\scripts\accesstests.py", line 138
    assert colsize is None or (value is None or colsize >= len(value)), 'colsize
=%s value=%s' % (colsize, 'none' if value is None else len(value))

                                  ^
SyntaxError: invalid syntax

Z:\>
Jan 6, 2009
#8 jake.chu...@gmail.com
I've checked several times to make sure I am *sure* I am running and looking at the
file.  I did a loop to load additional values on my last test.  The test only loaded
99 of 100 values.  It seems that subsequent SQL statements commit the previous
statement somehow.  

I tried running your test script, and it seems that I am not familar with python
enough.  I get an error on line 138.  

thanks

Jake


Jan 6, 2009
Project Member #9 mkleehammer
I believe the (x if conditional else y) isn't supported in 2.4.  I need to change
that to code that is 2.4 compliant.  (I've run it with 2.5 and 2.6.)

Just comment out line 138 for now.

I'll look into this some more...

Jan 7, 2009
#10 jake.chu...@gmail.com
Z:\>python c:\scripts\accesstests.py c:\scripts\empty.accdb
Did not find the pyodbc library in the build directory.  Will use an installed v
ersion.
python: 2.4.2 (#67, Sep 28 2005, 12:41:11) [MSC v.1310 32 bit (Intel)]
pyodbc: 2.1.3 c:\python24\lib\site-packages\pyodbc.pyd
odbc:   03.52.0000
driver: ACEODBC.DLL Microsoft Office 2007 Access database engine
        supports ODBC version 03.51
os:     Windows

======================================================================
ERROR: test_executemany_failure (__main__.AccessTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "c:\scripts\accesstests.py", line 555, in test_executemany_failure
    self.failUnlessRaises(pyodbc.Error, self.cursor.executemany, "insert into t1
(a, b) value (?, ?)", params)
  File "c:\python24\lib\unittest.py", line 322, in failUnlessRaises
    return
SystemError: 'finally' pops bad exception

----------------------------------------------------------------------
Ran 117 tests in 5.547s

FAILED (errors=1)

Z:\>python c:\scripts\accesstests.py c:\scripts\empty.accdb -vv
Did not find the pyodbc library in the build directory.  Will use an installed v
ersion.
python: 2.4.2 (#67, Sep 28 2005, 12:41:11) [MSC v.1310 32 bit (Intel)]
pyodbc: 2.1.3 c:\python24\lib\site-packages\pyodbc.pyd
odbc:   03.52.0000
driver: ACEODBC.DLL Microsoft Office 2007 Access database engine
        supports ODBC version 03.51
os:     Windows

ANSI to memo 0 ... ok
ANSI to memo 1 ... ok
ANSI to memo 1023 ... ok
ANSI to memo 1024 ... ok
ANSI to memo 10240 ... ok
ANSI to memo 2047 ... ok
ANSI to memo 2048 ... ok
ANSI to memo 20480 ... ok
ANSI to memo 254 ... ok
ANSI to memo 255 ... ok
ANSI to memo 256 ... ok
ANSI to memo 270 ... ok
ANSI to memo 304 ... ok
ANSI to memo 4000 ... ok
ANSI to memo 4095 ... ok
ANSI to memo 4096 ... ok
ANSI to memo 4097 ... ok
ANSI to memo 508 ... ok
ANSI to memo 510 ... ok
ANSI to memo 511 ... ok
ANSI to memo 512 ... ok
ansi 0 ... ok
ansi 1 ... ok
ansi 254 ... ok
ansi 255 ... ok
test_autocommit (__main__.AccessTestCase) ... ok
binary 0 ... ok
binary 1 ... ok
binary 254 ... ok
binary 255 ... ok
test_bit (__main__.AccessTestCase) ... ok
test_bit_null (__main__.AccessTestCase) ... ok
Make sure using a Cursor after closing its connection doesn't crash. ... ok
test_concatenation (__main__.AccessTestCase) ... ok
test_datasources (__main__.AccessTestCase) ... ok
test_datetime (__main__.AccessTestCase) ... ok
test_decimal (__main__.AccessTestCase) ... ok
test_different_bindings (__main__.AccessTestCase) ... ok
test_executemany (__main__.AccessTestCase) ... ok
test_executemany_failure (__main__.AccessTestCase) ... ERROR
test_float (__main__.AccessTestCase) ... ok
test_getinfo_bool (__main__.AccessTestCase) ... ok
test_getinfo_int (__main__.AccessTestCase) ... ok
test_getinfo_smallint (__main__.AccessTestCase) ... ok
test_getinfo_string (__main__.AccessTestCase) ... ok
test_guid (__main__.AccessTestCase) ... ok
image 0 ... ok
image 1 ... ok
image 1023 ... ok
image 1024 ... ok
image 10240 ... ok
image 2047 ... ok
image 2048 ... ok
image 20480 ... ok
image 254 ... ok
image 255 ... ok
image 256 ... ok
image 270 ... ok
image 304 ... ok
image 4000 ... ok
image 4095 ... ok
image 4096 ... ok
image 4097 ... ok
image 508 ... ok
image 510 ... ok
image 511 ... ok
image 512 ... ok
test_int (__main__.AccessTestCase) ... ok
Ensure pyodbc.lowercase forces returned column names to lowercase. ... ok
Unicode to memo 0 ... ok
Unicode to memo 1 ... ok
Unicode to memo 1023 ... ok
Unicode to memo 1024 ... ok
Unicode to memo 10240 ... ok
Unicode to memo 2047 ... ok
Unicode to memo 2048 ... ok
Unicode to memo 20480 ... ok
Unicode to memo 254 ... ok
Unicode to memo 255 ... ok
Unicode to memo 256 ... ok
Unicode to memo 270 ... ok
Unicode to memo 304 ... ok
Unicode to memo 4000 ... ok
Unicode to memo 4095 ... ok
Unicode to memo 4096 ... ok
Unicode to memo 4097 ... ok
Unicode to memo 508 ... ok
Unicode to memo 510 ... ok
Unicode to memo 511 ... ok
Unicode to memo 512 ... ok
test_money (__main__.AccessTestCase) ... ok
More than one bind and select on a cursor ... ok
test_negative_decimal_scale (__main__.AccessTestCase) ... ok
test_negative_float (__main__.AccessTestCase) ... ok
test_negative_int (__main__.AccessTestCase) ... ok
test_negative_real (__main__.AccessTestCase) ... ok
test_negative_row_index (__main__.AccessTestCase) ... ok
test_null_image (__main__.AccessTestCase) ... ok
test_null_memo (__main__.AccessTestCase) ... ok
test_real (__main__.AccessTestCase) ... ok
test_row_description (__main__.AccessTestCase) ... ok
test_row_repr (__main__.AccessTestCase) ... ok
test_row_slicing (__main__.AccessTestCase) ... ok
test_rowcount_delete (__main__.AccessTestCase) ... ok
test_rowcount_nodata (__main__.AccessTestCase) ... ok
Ensure rowcount is reset to -1 ... ok
test_rowcount_select (__main__.AccessTestCase) ... ok
test_smallint (__main__.AccessTestCase) ... ok
Ensure parameter markers work in a subquery ... ok
test_tinyint (__main__.AccessTestCase) ... ok
unicode 0 ... ok
unicode 1 ... ok
unicode 254 ... ok
unicode 255 ... ok
test_unicode_null (__main__.AccessTestCase) ... ok
test_unicode_query (__main__.AccessTestCase) ... ok
test_version (__main__.AccessTestCase) ... ok

======================================================================
ERROR: test_executemany_failure (__main__.AccessTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "c:\scripts\accesstests.py", line 555, in test_executemany_failure
    self.failUnlessRaises(pyodbc.Error, self.cursor.executemany, "insert into t1
(a, b) value (?, ?)", params)
  File "c:\python24\lib\unittest.py", line 322, in failUnlessRaises
    return
SystemError: 'finally' pops bad exception

----------------------------------------------------------------------
Ran 117 tests in 5.422s

FAILED (errors=1)
Jan 8, 2009
#11 tarkatronic
Looks like your suspicion about 2.4 is right:

Python 2.4.3
>>> 1 if True else 0
  File "<stdin>", line 1
    1 if True else 0
       ^
SyntaxError: invalid syntax

Python 2.5.1
>>> 1 if True else 0
1

However, the following works in both (As well as 2.6 and 3.0):
>>> True and 1 or 0
1
>>> False and 1 or 0
0
Mar 18, 2009
Project Member #12 mkleehammer
Where are we on this?

BTW, I fixed the 'finally pops a bad exception' and the Python 2.4 problem (in
0e212c8df466f95aa1cadee5797cf6faa6fdacda).  These fixes will be in 2.1.5.

Apr 24, 2009
#13 abbconsu...@gmail.com
Try without autocommit=True.
Oct 8, 2009
#14 m...@adfan.be
Hello,

I have had a similar problem: on one database, I could insert but not on the other 
one.  The difference: in the first case, a primary key was defined but not on the 
other one.  I guess Access drivers prevents you from inserting rows in a table where 
no primary key is defined.
Hope this helps.

Dec 31, 2009
Project Member #15 mkleehammer
It looks like this is fixed.

Thanks mvh, that is important and should be put into a FAQ or something.

Status: Invalid
Nov 21, 2010
Project Member #16 mkleehammer
(No comment was entered for this change.)
Status: NoFix