使用Python创建新的Access数据库和表 [英] Create new Access database and tables using Python

问题描述

我正在尝试在Python中创建一个Access数据库，并向其中添加两个新表.我正在使用win32com，并设法创建了数据库，但是无法创建表.我得到的只是无用的Windows错误.谁能帮我吗?

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?

推荐答案

由于MS Access既是GUI .exe应用程序又是后端数据库，因此创建数据库与创建 Tables ，视图(即存储的查询)，甚至过程.

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.

作为比较，另一个文件级RDMS SQLite必须将其.exe shell打开为CREATE DATABASE.服务器级RDMS(SQL Server，MySQL，Postgres)必须登录到服务器实例才能运行该命令. MS Access没有外壳程序或实例工具，只有一个应用程序对象.

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.

因此，在使用 CreateDatabase 方法，请考虑对CREATE TABLE运行DDL SQL语句，您可以使用

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接口

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 数据库API

虽然CREATE DATABASE在MS Access SQL中不可用，但是只有在创建数据库文件之后，才可以使用CREATE TABLE命令将上述Execute与任何Python ODBC 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()

这篇关于使用Python创建新的Access数据库和表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！