Everything about python and Microsoft Access (Step by Step) :
First download and install Microsoft Access Database Engine 2010 Redistributable from this link if you haven’t :
https://www.microsoft.com/en-us/download/details.aspx?id=13255
Then you should install pyodbc module.
- In Pycharm please go to File>Settings>Project:YourProjectName > Project interpreter : then on the right side click on + and search pyodbc
Generally , jetbrains is very interested in complicating his menus .
- In Thoony go to tools > Manage Packages… and search pyodbc
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 use Table1Array.append(row) to add all fields or use row[0],row[1],row[2],… to add any fields of table you need.
To insert in to the access table :
ConFileName=(r'c:\mydb\myaccess.mdb') conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' +ConFileName+ ';') cursor = conn.cursor() for ta in TableArray: Sql_insert_query = "INSERT INTO Table1(ID, Value1,Value2,Value3,Value4,Value5,Value6) " \ "VALUES ('{a}','{b}','{c}','{d}','{e}','{f}','{g}')".format(a=str(ta[0]),b=str(ta[1]),c=str(ta[2]),d=str(ta[3]),e=str(ta[4]),f=str(ta[5]),g=str(ta[6])) cursor.execute(Sql_insert_query) conn.commit() cursor.close()
To Create the new Access database :
import win32com.client ConFileName = r'Catalog:\mydb\NewAccessDB.mdb' try: Catalog = win32com.client.Dispatch('ADOX.Catalog') Catalog.Create('Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' + ConFileName + ';') Catalog = None except: Exception as e: print("Database generation failed, Error="+str(e)) print("NewAccessDB.mdb created successfully")