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)")
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418

1 Answers1

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];")

X0r
  • 113
  • 5
  • 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) –  Nov 16 '16 at 18:40
  • OK hmm.. try putting your SQL query in a string, and then pass that into your method – X0r Nov 16 '16 at 18:42
  • I am learning Python. Would you kindly show me how I can do that please? –  Nov 16 '16 at 18:44
  • 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. http://stackoverflow.com/questions/3224268/python-unicode-encode-error – X0r Nov 16 '16 at 19:00
  • @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. – Gord Thompson Nov 16 '16 at 20:11
  • http://stackoverflow.com/questions/40641820/ascii-codec-cant-encode-character-error. I have asked another question. –  Nov 16 '16 at 20:37