Analysis of basic steps for operating Access database in Python

The example of this article analyzes the basic steps of operating Access database in Python. Share it with everyone for your reference, the details are as follows:

The emergence of the Python programming language has brought great benefits to developers. We can use such a powerful object-oriented open source language to easily implement many specific functional requirements. For example, the function implementation of Python operating Access database and so on. Before operating Access databases in Python, first, you should 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 record set

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

Step 3, operate the record set

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. Traverse records

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 cause an error because the recordcount is invalid at this time. The solution is: Before opening a record set, set Cursorlocation to 3, and then open the record set. At this time, recordcount will be 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

For more articles related to analysis of the basic steps of Python operating Access database, please pay attention to the PHP Chinese website!