Read and write to Access using Python

In the process of learning Python, we will encounter the read-write problem of Access, and then we can use the COM component of win32.client module to access the Access files through ADODB.

You need to download and install pywin32 and AccessDatabaseEngine.exe

pywin32 download address: https: / / www ofstack. com softs / 695840. html

AccessDatabaseEngine. Download exe https: / / www ofstack. com softs / 291508. html

64 downloads: https: / / www ofstack. com softs / 291504. html

1. Import the module

import win32com.client

2. Establish a database connection

conn = win32com.client.Dispatch(r"ADODB.Connection")
DSN = 'PROVIDER = Microsoft.Jet.OLEDB.4.0;DATA SOURCE = test.mdb'
conn.Open(DSN)

3. Open a record set

rs = win32com.client.Dispatch(r'ADODB.Recordset')
rs_name = 'MEETING_PAPER_INFO'
rs.Open('[' + rs_name + ']', conn, 1, 3)

4. Operate the recordset

rs.AddNew() # add 1 A new record 
rs.Fields.Item(0).Value = "data" # New record no 1 A record for "data"
rs.Update() # update 

5. Use SQL statement to add, delete and change data

#  increase 
sql = "Insert Into [rs_name] (id, innerserial, mid) Values ('002133800088980002', 2, '21338')" #sql statements 
conn.Execute(sql) # perform sql statements 
#  delete 
sql = "Delete * FROM " + rs_name + " where innerserial = 2"
conn.Execute(sql)
#  change 
sql = "Update " + rs_name + " Set mid = 2016 where innerserial = 3"
conn.Execute(sql)

6. Traverse the record

rs.MoveFirst() # Move the cursor to the first record 
count = 0
while True:
 if rs.EOF:
 break
 else:
 for i in range(rs.Fields.Count):
 # Field name: field content 
 print(rs.Fields[i].Name, " : ", rs.Fields[i].Value)
 count += 1
 rs.MoveNext()

7. Close the database

conn.close()

supplement

If it's python3, it looks like you need pypyodbc

Don't say more, see on the code!

Use modules: pypyodbc

Examples and installation details:

https://github.com/jiangwen365/pypyodbc/

#!/usr/bin/env python
# -*- coding:utf-8 -*-
__author__ = "loki"
import time
import pypyodbc as mdb

#  The connection mdb file 
connStr = (r'Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\MDB_demo\demo.mdb;'
   r'Database=bill;'
   )
conn = mdb.win_connect_mdb(connStr)

# connStr = (
#  r'Driver={SQL Sever};'
#  r'Server=sqlserver;'
#  r'Database=bill;'
#  r'UID=sa;'
#  r'PWD=passwd'
# )
#
# conn = mdb.connect(connStr)
#  Create a cursor 
cur = conn.cursor()

cur.execute('SELECT * FROM bill;')

for col in cur.description:
 #  Display line description 
 print(col[0], col[1])
result = cur.fetchall()

for row in result:
 #  Displays the values of the fields 
 print(row)
 print(row[1], row[2]

Official example given by mdb

# Microsoft Access DB
import pypyodbc 

connection = pypyodbc.win_create_mdb('D:\\database.mdb')

SQL = 'CREATE TABLE saleout (id COUNTER PRIMARY KEY,product_name VARCHAR(25));'
connection.cursor().execute(SQL)
connection.close()

#SQL Server 2000/2005/2008 (and probably 2012 and 2014)

#SQL Server 2000/2005/2008 (and probably 2012 and 2014)
import pypyodbc as pyodbc # you could alias it to existing pyodbc code (not every code is compatible)
db_host = 'serverhost'
db_name = 'database'
db_user = 'username'
db_password = 'password'
connection_string = 'Driver={SQL Server};Server=' + db_host + ';Database=' + db_name + ';UID=' + db_user + ';PWD=' + db_password + ';'
db = pyodbc.connect(connection_string)
SQL = 'CREATE TABLE saleout (id COUNTER PRIMARY KEY,product_name VARCHAR(25));'
db.cursor().execute(SQL)

# Doing a simple SELECT query
connStr = (
 r'Driver={SQL Server};'
 r'Server=sqlserver;'
 #r'Server=127.0.0.1,52865;' +
 #r'Server=(local)\SQLEXPRESS;'
 r'Database=adventureworks;'
 #r'Trusted_Connection=Yes;'
 r'UID=sa;'
 r'PWD=sapassword;'
 )
db = pypyodbc.connect(connStr)
cursor = db.cursor()

# Sample with just a raw query:
cursor.execute("select client_name, client_lastname, [phone number] from Clients where client_id like '01-01-00%'")

# Using parameters (IMPORTANT: YOU SHOULD USE TUPLE TO PASS PARAMETERS)
# Python note: a tuple with just one element must have a trailing comma, otherwise is just a enclosed variable
cursor.execute("select client_name, client_lastname, [phone number] "
"from Clients where client_id like ?", ('01-01-00%', ))

# Sample, passing more than one parameter
cursor.execute("select client_name, client_lastname, [phone number] "
"from Clients where client_id like ? and client_age < ?", ('01-01-00%', 28))

# Method 1, simple reading using cursor
while True:
 row = cursor.fetchone()
 if not row:
  break
 print("Client Full Name (phone number): ", row['client_name'] + ' ' + row['client_lastname'] + '(' + row['phone number'] + ')')

# Method 2, we obtain dict's all records are loaded at the same time in memory (easy and verbose, but just use it with a few records or your app will consume a lot of memory), was tested in a modern computer with about 1000 - 3000 records just fine...
import pprint; pp = pprint.PrettyPrinter(indent=4)
columns = [column[0] for column in cursor.description]
for row in cursor.fetchall():
 pp.pprint(dict(zip(columns, row)))

# Method 3, we obtain a list of dict's (represents the entire query)
query_results = [dict(zip([column[0] for column in cursor.description], row)) for row in cursor.fetchall()]
pp.pprint(query_results)

# When cursor was used must be closed, if you will not use again the db connection must be closed too.
cursor.close()
db.close()

How to use it without install (the latest version from here)

Just copy the latest pypyodbc.py downloaded from this repository on your project folder and import the module.

Install
If you have pip available (keep in mind that the version on pypi may be old):

[

pip install pypyodbc

]

Or get the latest pypyodbc.py script from GitHub (Main Development site)

[

python setup.py install

]