Analysis on the basic operation steps for Python Access Database Operations

The emergence of the Python programming language brings great benefits to developers. We can use this powerful object-oriented open-source language to easily implement many specific functional requirements. For example, Python is used to operate the Access database. Before using Python to operate the Access database, you must first install Python and Python for Windows extensions.

Step 1: Create a database connection

 
 

  
  
import win32com.client   
  
  
conn = win32com.client.Dispatch(r'ADODB.Connection')   
  
  
DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:/MyDB.mdb;'   
  
  
conn.Open(DSN) 
 
 

Step 2: open a record set

 
 

  
  
Rs = win32com. client. Dispatch (r 'ADODB. recordset ')
  
  
Rs_name = 'myrecordset' # Table Name
  
  
Rs. Open ('[' + rs_name + ']', conn, 1, 3)
 
 

Step 3 of Python's Access Database Operations

 
 

  
  
rs.AddNew()   
  
  
rs.Fields.Item(1).Value = 'data'   
  
  
rs.Update() 
 
 

Step 4: use SQL to insert or update data

 
 

  
  
conn = win32com.client.Dispatch(r'ADODB.Connection')   
  
  
DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:/MyDB.mdb;'   
  
  
sql_statement = "Insert INTO [Table_Name] ([Field_1], 
[Field_2]) VALUES ('data1', 'data2')"   
  
  
conn.Open(DSN)   
  
  
conn.Execute(sql_statement)   
  
  
conn.Close() 
 
 

Step 5 of Python Access Database Operations

 
 

  
  
rs.MoveFirst()   
  
  
count = 0   
  
  
while 1:   
  
  
if rs.EOF:   
  
  
break   
  
  
else:   
  
  
countcount = count + 1   
  
  
rs.MoveNext() 
 
 

Note: If a record is empty, moving the pointer to the first record will result in an error because recordcount is invalid at this time. Solution: Set Cursorlocation to 3 before opening a record set. In this case, recordcount is valid. For example:

 
 

  
  
rs.Cursorlocation = 3 # don't use parenthesis here   
  
  
rs.Open('Select * FROM [Table_Name]', conn) # be sure conn is open   
  
  
rs.RecordCount # no parenthesis here either 
 
 

The above section describes how to operate an Access database using Python.