Create new Access database and tables using Python

问题:

I'm trying to create an Access database in Python and add two new tables to it. I'm using win32com and have managed to create the database but can't create the tables. All I get is unhelpful Windows errors. Can anyone help me?

The following code works fine:

dbname = r'C:/Users/Guest/Desktop/NewDB.mdb'
db = Dispatch("Access.Application")
dbEngine = db.DBEngine
workspace = dbEngine.Workspaces(0)

dbLangGeneral = ';LANGID=0x0409;CP=1252;COUNTRY=0'
newdb = workspace.CreateDatabase(dbname, dbLangGeneral, 64)

How do I add new tables to this database?

回答1:

Because MS Access is both a GUI .exe application and a backend database, to create a database is a different process than creating database objects like Tables, Views (i.e., stored queries), and even Procedures.

As a comparison, the other file-level RDMS, SQLite, one must open its .exe shell to CREATE DATABASE. And the server-level RDMS's (SQL Server, MySQL, Postgres) one must log into the server instance to run the command. MS Access does not have the shell or instance facilities, just an application object.

Therefore, after creating a database with CreateDatabase method, consider running a DDL SQL statement to CREATE TABLE which you can do with the Execute() method.

Python COM Interface

from win32com.client import Dispatch

try:
    dbname = r'C:/Users/Guest/Desktop/NewDB.mdb'
    accApp = Dispatch("Access.Application")
    dbEngine = accApp.DBEngine
    workspace = dbEngine.Workspaces(0)

    dbLangGeneral = ';LANGID=0x0409;CP=1252;COUNTRY=0'
    newdb = workspace.CreateDatabase(dbname, dbLangGeneral, 64)

    newdb.Execute("""CREATE TABLE Table1 (
                      ID autoincrement,
                      Col1 varchar(50),
                      Col2 double,
                      Col3 datetime);""")

except Exception as e:
    print(e)

finally:
    accApp.DoCmd.CloseDatabase
    accApp.Quit
    newdb = None
    workspace = None
    dbEngine = None
    accApp = None

Python DB API

While the CREATE DATABASE is not available in MS Access SQL you can run the above Execute with any Python ODBC API with a CREATE TABLE command only after database file is created:

import pypyodbc

dbname = r'C:/Users/Guest/Desktop/NewDB.mdb'     
constr = "DRIVER={{Microsoft Access Driver (*.mdb, *.accdb)}};DBQ={0};".format(dbname)

dbconn = pypyodbc.connect(constr)

cur = dbconn.cursor()
cur.execute("""CREATE TABLE Table1 (
                 ID autoincrement,
                 Col1 varchar(50),
                 Col2 double,
                 Col3 datetime);""")
dbconn.commit()

回答2:

newdb = workspace.CreateDatabase(dbname, dbLangGeneral, 64)

has already created a DAO.Database object named newdb, so you should be able to simply go ahead and use its Execute method to run a DDL statement like this

newdb.Execute("CREATE TABLE [Table1] ([ID] COUNTER PRIMARY KEY, [TextField] TEXT(50))")

回答3:

It looks like you would execute a SQL statement CREATE Table on the Database object i.e. on newdb, via its Execute() method.

But I don't recommend using COM on MS Access Application object. For an MS Access database, the recommended method of interacting with the DB is to directly open the DB through ODBC API, rather than through an Access.Application instance. You should only use the latter if you need features from the app like reports, printing, etc. For interacting with MS Access DB file via ODBC, I have had good experience with pyodbc and pypyodbc, which do not require COM. A lower level access through DAO API via win32com (but not Access.Application) seems quite good too but I have not tried. Check out http://www.icodeguru.com/WebServer/Python-Programming-on-Win32/ch13.htm for a good discussion on various connection API available and some code examples.

Other useful resources:

• ms access application automation API: the examples use vba but win32com provides same API.
• intro to the above with links for ADO-based tasks that therefore don't need the ms access application: links show c# but win32com provides same API.

回答4:

For the benefit of future readers, the sample code in the question uses win32com to create the new Access database file. A simpler alternative would be to use my (free) msaccessdb Python module, e.g.,

import msaccessdb
import pyodbc

db_file = r'C:\path\to\new.accdb'
msaccessdb.create(db_file)
cnxn_str = (
    'DRIVER={{Microsoft Access Driver (*.mdb, *.accdb)}};'
    'DBQ={}'.format(db_file)
)
cnxn = pyodbc.connect(cnxn_str)