22

I am trying to create tables in a MS Access DB with python using pyodbc but when I run my script no tables are created and no errors are given. My code:

#!/usr/bin/env python
import pyodbc

con = pyodbc.connect(r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=Z:\Data\Instruments\testDB.accdb; Provider=MSDASQL;')
cur = con.cursor()
string = "CREATE TABLE TestTable(symbol varchar(15), leverage double, shares integer, price double)"
cur.execute(string)

What could be wrong?

wDroter
  • 1,209
  • 4
  • 17
  • 25

2 Answers2

25

You need to commit the transaction:

import pyodbc

con = pyodbc.connect(r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=Z:\Data\Instruments\testDB.accdb; Provider=MSDASQL;')
cur = con.cursor()
string = "CREATE TABLE TestTable(symbol varchar(15), leverage double, shares integer, price double)"
cur.execute(string)
con.commit()
garnertb
  • 9,454
  • 36
  • 38
  • Thank you! Half a day debugging why my DB was completely stuck. Create table was never committed and kept entire DB waiting. – Jari Turkia Jun 22 '21 at 13:03
3

Additional solutions that do not require a manual commit are:

Set autocommit = True when the connection instance is created.

Eg:

con = pyodbc.connect(your_connection_string, autocommit = True)

OR

Use a with statement that, according to Python Database connection Close, will commit anything before the connection is deleted at the end of the with block.

Eg:

with pyodbc.connect(your_connection_string) as con:

    CREATE_TABLE_CODE_WITHOUT_COMMIT

UNRELATED_CODE
johnDanger
  • 1,990
  • 16
  • 22