Basic step analysis for Python operation Access database

This article illustrates the basic steps of Python operation on Access database. I will share it with you for your reference as follows:

The emergence of the Python programming language has brought great benefits to developers. We can take advantage of such a powerful object-oriented open source language to easily implement many specific functional requirements. For example, Python operates on the functional implementation of Access database and so on. Before Python operates the Access database, you should first install Python and Python for Windows extensions.

Step 1. Establish 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 recordset

rs = win32com.client.Dispatch(r'ADODB.Recordset')
rs_name = 'MyRecordset'# The name of the table 
rs.Open('[' + rs_name + ']', conn, 1, 3)

Step 3. Operation on the recordset

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

Step 4. Insert or update the data with SQL

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. Traverse the record

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 point. The solution is: before opening a recordset, set Cursorlocation to 3, and then open the recordset. At this point, recordcount will be valid. Such as:

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

For more information about Python, please visit our site: Python common database operations skills summary, Python + MySQL database programming tutorial ", "Python pictures skills summary", "Python data structure and algorithm tutorial", "Python Socket programming skills summary", "Python function using techniques", "Python string skills summary", "Python introduction and advanced tutorial" and "Python file and directory skills summary"

I hope this article is helpful to you Python programming.