2

I am saving MS Access tables as CSV files using Python. There is a table in the MS Access database that is named 'Perm_Site Info'. There is a space in the naming in MS Access. When I run the below snippet, the code blows up. I have tried having single and as well as double quotes in the cursor.execute but no fruition. I request your kind assistance in order to understand how to fix this.

import pyodbc import csv conn_string = ("DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\Access\\permissions.accdb") conn = pyodbc.connect(conn_string) cursor = conn.cursor() cursor.execute("select * from Perm_Site Info;") with open('C:\\Desktop\\Python Files\\Perms_Site_Info.csv','wb') as csvfile: writer = csv.writer(csvfile) writer.writerow([i[0] for i in cursor.description]) writer.writerows(cursor) cursor.close() conn.close() print 'All done for now'

The error:

cursor.execute("select * from Perm_Site Info;") ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC Microsoft Access Driver] The Microsoft Access database engine cannot find the input table or query 'Perm_Site'. Make sure it exists and that its name is spelled correctly. (-1305) (SQLExecDirectW)")
Improve this question

1 Answer 1

Reset to default
3

Try using brackets around the entire table name. It's barking because it doesn't know what to do with the space.

cursor.execute("select * from [Perm_Site Info];")

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thanks X0r, I tried your suggestion and now it throws out an error showing writer.writerows(cursor) UnicodeEncodeError: 'ascii' codec can't encode character u'\u2013' in position 4: ordinal not in range(128)
OK hmm.. try putting your SQL query in a string, and then pass that into your method
I am learning Python. Would you kindly show me how I can do that please?
Ugh.. power just went out at work.. Couldn't edit my comment in time. anyway.. Unicode and ascii don't like each other very much. There's another answer here that may be able to help you with this. Now you're getting something from the table but it doesn't know what to do with some weird characters. stackoverflow.com/questions/3224268/python-unicode-encode-error
@Learner re: "UnicodeEncodeError" - That is a different problem. You should accept this answer as having solved the issue you originally asked about, then ask a new question about how Python's csv module handles Unicode characters.
|