Python Access Database basic steps analysis, pythonaccess

Python Access Database basic steps analysis, pythonaccess

This document analyzes the basic steps for using Python to Access a database. We will share this with you for your reference. The details are as follows:

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. Establish a database connection

import win32com.clientconn = 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. perform operations on 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 = 0while 1:if rs.EOF:breakelse:countcount = count + 1rs.MoveNext()

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

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