| Issue 21: | Cannot write to Access Database | |
| 2 people starred this issue and may be notified of changes. | Back to list |
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
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
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
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
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
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
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
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
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
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
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
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
Try without autocommit=True.
Oct 8, 2009
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
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
(No comment was entered for this change.)
Status:
NoFix
|
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