How do you create a mdb database file in Python?

Question

I would like to create a mdb database file in windows with Python and can't seem to figure it out with the Python Docs. Everything I read about is related to making a connection and what to do with the cursor.

Any thoughts? Thanks...

Isomerize

By mdb database, do you mean a Microsoft Access database? – Tinytinya

Updated my answer with an example. – Battue

Answer 1

My experience with the comtypes module has been fairly good. You'll probably want to have an Access DAO/ADO/VBA reference handy for the methods that are used, however, as the comtypes module generates COM library wrappers dynamically, so there's no built-in documentation.

Here's a brief example of how it works. (Go ahead and test it out yourself.)

from comtypes.client import CreateObject

access = CreateObject('Access.Application')

from comtypes.gen import Access

DBEngine = access.DBEngine
db = DBEngine.CreateDatabase('test.mdb', Access.DB_LANG_GENERAL)
      # For me, test.mdb was created in my My Documents folder when I ran the script 

db.BeginTrans()

db.Execute("CREATE TABLE test (ID Text, numapples Integer)")
db.Execute("INSERT INTO test VALUES ('ABC', 3)")

db.CommitTrans()
db.Close()

(Moved the second import statement after the CreateObject line for cases where the Python wrapper module for the typelibrary didn't previously exist.)

Battue

If you can create a DBEngine variable then you could also use all the DAO table, field, index and relationships objects. These give you much more detailed control of the objects created. See the TempTables.MDB page at my website which illustrates how to use a temporary MDB in your app. granite.ab.ca/access/temptables.htm – Social

Sorry to be so new to this, but I installed the comtypes and tried to run the code, but then I got the following exception: "ImportError: cannot import name Access". Any ideas? – Isomerize

@Tony Toews: Yes, well... it may have been because I didn't actually insert data into the table I created in that manner, but when I created a table and added a field using DAO, it didn't show up in the database when I opened it in Access afterwards, and since this was just meant as an example of using comtypes to create an MDB file I didn't feel like spending more time figuring it out. – Battue

@GuidoS: You could use the GetModule method of comtypes.client in an interpreter to generate the proper Python modules if you know how to refer to the Access typelibrary; if you don't know the specifics of that, then you can just open up an interpreter and type from comtypes.client import CreateObject, then CreateObject('Access.Application'), and it should automatically generate the Python wrapper module if it doesn't already exist. ...Of course, this is all moot if you don't actually have Microsoft Access installed on the computer you're running the script from in the first place. – Battue

Also modified my answer with what is probably a simpler solution. – Battue

@Battue thanks, this works now. I will go learn about comtypes a little more so I can understand this better in the future. – Isomerize

Answer 2

First download and install Microsoft Access Database Engine 2010 Redistributable if you haven’t.

Then you should install pyodbc module.

Now you can connect to access database :

ConFileName=(r'c:\mydb\myaccess.mdb')
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' + ConFileName + ';')
cursor = conn.cursor()

To select from any table in the database please use this simple code :

ConFileName=(r'c:\mydb\myaccess.mdb')
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' + ConFileName + ';')
cursor = conn.cursor()
cursor.execute('select * from table1')
for row in cursor.fetchall():
    Table1Array.append((row[0],row[1],row[2])
print(str(len(Table1Array))+" records in Table1 loaded successfully.")

You can follow this link to get more information about working with MS Access by Python :

https://elvand.com/python-and-ms-access/

Cinthiacintron