Basic Steps Analysis of Operating Access Database with Python

This article analyzes the basic steps of operating Access databases in Python. Share with everyone for reference, as follows:

The emergence of the Python programming language has brought great benefits to developers. We can use this powerful object-oriented open-source language to easily achieve many specific functional requirements. For example, the implementation of Python's operation on Access databases, etc. Before operating Access databases in Python, first, you should have installed Python and Python for Windows extensions.

Step1Establish 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)

Step2Open a recordset

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

Step3Recordset operations

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

Step4Use 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()

Step5Traversal 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 recordcount is invalid at this time.The solution is:Before opening a record set, set the Cursorlocation to3After that, open the record set, and 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

Readers who are interested in more content related to Python can check the special topic of this site: Python Common Database Operation Skills Summary, Python+MySQL Database Program Design Tutorial, Python Image Operation Skills Summary, Python Data Structures and Algorithms Tutorial, Python Socket Programming Skills Summary, Python Function Usage Skills Summary, Python String Operation Skills Summary, Python Basic and Advanced Classic Tutorial, and Python File and Directory Operation Skills Summary

Hoping that the content described in this article will be helpful to everyone's Python program design.

Declaration: The content of this article is from the Internet, and the copyright belongs to the original author. The content is contributed and uploaded by Internet users spontaneously. This website does not own the copyright, has not been manually edited, and does not assume any relevant legal liability. If you find any content suspected of copyright infringement, please send an email to: notice#oldtoolbag.com (Please replace # with @ when sending an email to report abuse, and provide relevant evidence. Once verified, this site will immediately delete the content suspected of infringement.)