Exporting data from Access database to Python code

Question

I've been deep diving though the forums and I couldn't find a way to extract data from a database in Access. I had two ways to do it, first and second option, I hope it is understandable but if it is not clear, please ask me, I am literally stacked... My code is:

# first option
DRIVER_NAME = "Microsoft Access Driver (*.mdb, *.accdb)"

# Path. 
DB_PATH = getcwd() + "/Regitros_Export_History.accdb"

# Connection.

conn = pyodbc.connect("Driver={%s};DBQ=%s;" % (DRIVER_NAME, DB_PATH))

# second option
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, * 
.accdb)};DBQ=C:\Users\enriq\Dropbox\Proyectos\Escission\Amura\Subvenciones\Puertos 
4.0\Desarrollo de proyecto\Documentación\Archivos compartidos por Coqui 20210818\PROYECTO 
MODELOS ML - JIT\Regitros_Export_History.accdb;')

# Create a cursor to make the queries
cursor=conn.cursor()
cursor.execute('SELECT * FROM 2021-08-15')

rows = q.fetchall()
# Recorrer cada una de las filas e imprimirlas en pantalla.

if rows is not None:
    for row in rows:
        print(row)
else:
    print("No hay datos en la tabla.")

# Cerrar la conexión y, opcionalmente, el cursor antes de finalizar.
cursor.close()
conn.close()

I'm getting an error in line:

ProgrammingError                          Traceback (most recent call last)
<ipython-input-53-56f8241c5894> in <module>
      9 # Crear cursor para ejecutar consultas.
     10 cursor=conn.cursor()
---> 11 cursor.execute('SELECT * FROM 2021-08-15')
     12 
     13 rows = q.fetchall()

ProgrammingError: ('42000', '[42000] [Microsoft][Controlador ODBC Microsoft Access] Error de 
sintaxis en la cláusula FROM. (-3506) (SQLExecDirectW)')

Any of the methods worked and I don't know what else I could try. The table in the database is called 2021-08-15, should it be a query?

Thank you in advanced,

Cheers.

Enrique.

Erik A · Accepted Answer · 2021-09-08 09:37:08Z

1

You need to bracket table names if they contain special characters (like numbers, minuses, etc)

cursor.execute('SELECT * FROM [2021-08-15]')

answered Sep 8, 2021 at 9:37

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

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

2 Comments

Enrique Over a year ago

Thank you, now it works. Should I bracket the table name always or only when a special character is in the name? What about the spaces?

Erik A Over a year ago

I bracket always. It's only required when there are special characters including spaces and keywords (things like SELECT, FROM, Password, etc.), but it never hurts.

Collectives™ on Stack Overflow

Exporting data from Access database to Python code

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related