使用Python对Access读写操作_网络技术_宿州市腾雀网络科技有限公司

使用Python对Access读写操作

发布时间 - 2026-01-11 00:27:18 点击率：次

学习Python的过程中，我们会遇到Access的读写问题，这时我们可以利用win32.client模块的COM组件访问功能，通过ADODB操作Access的文件。

需要下载安装pywin32与AccessDatabaseEngine.exe

pywin32下载地址：https://www./softs/695840.html

AccessDatabaseEngine.exe下载 https://www./softs/291508.html

64位下载：https://www./softs/291504.html

1、导入模块

import win32com.client

2、建立数据库连接

conn = win32com.client.Dispatch(r"ADODB.Connection")
DSN = 'PROVIDER = Microsoft.Jet.OLEDB.4.0;DATA SOURCE = test.mdb'
conn.Open(DSN)

3、打开一个记录集

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

4、对记录集操作

rs.AddNew() #添加一条新记录
rs.Fields.Item(0).Value = "data" #新记录的第一个记录为"data"
rs.Update() #更新

5、用SQL语句来增、删、改数据

# 增
sql = "Insert Into [rs_name] (id, innerserial, mid) Values ('002133800088980002', 2, '21338')" #sql语句
conn.Execute(sql) #执行sql语句
# 删
sql = "Delete * FROM " + rs_name + " where innerserial = 2"
conn.Execute(sql)
# 改
sql = "Update " + rs_name + " Set mid = 2016 where innerserial = 3"
conn.Execute(sql)

6、遍历记录

rs.MoveFirst() #光标移到首条记录
count = 0
while True:
 if rs.EOF:
 break
 else:
 for i in range(rs.Fields.Count):
 #字段名：字段内容
 print(rs.Fields[i].Name, "：", rs.Fields[i].Value)
 count += 1
 rs.MoveNext()

7、关闭数据库

conn.close()

补充

如果是python3好像需要用到pypyodbc

# 话不多说，码上见分晓！

使用模块： pypyodbc

例子和安装详见：

https://github.com/jiangwen365/pypyodbc/

#!/usr/bin/env python
# -*- coding:utf-8 -*-
__author__ = "loki"
import time
import pypyodbc as mdb

# 连接mdb文件
connStr = (r'Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\MDB_demo\demo.mdb;'
   r'Database=bill;'
   )
conn = mdb.win_connect_mdb(connStr)

# connStr = (
#  r'Driver={SQL Sever};'
#  r'Server=sqlserver;'
#  r'Database=bill;'
#  r'UID=sa;'
#  r'PWD=passwd'
# )
#
# conn = mdb.connect(connStr)
# 创建游标
cur = conn.cursor()

cur.execute('SELECT * FROM bill;')

for col in cur.description:
 # 展示行描述
 print(col[0], col[1])
result = cur.fetchall()

for row in result:
 # 展示个字段的值
 print(row)
 print(row[1], row[2]

官方给的例子mdb

# Microsoft Access DB
import pypyodbc 

connection = pypyodbc.win_create_mdb('D:\\database.mdb')

SQL = 'CREATE TABLE saleout (id COUNTER PRIMARY KEY,product_name VARCHAR(25));'
connection.cursor().execute(SQL)
connection.close()

#SQL Server 2000/2005/2008 (and probably 2012 and 2014)

#SQL Server 2000/2005/2008 (and probably 2012 and 2014)
import pypyodbc as pyodbc # you could alias it to existing pyodbc code (not every code is compatible)
db_host = 'serverhost'
db_name = 'database'
db_user = 'username'
db_password = 'password'
connection_string = 'Driver={SQL Server};Server=' + db_host + ';Database=' + db_name + ';UID=' + db_user + ';PWD=' + db_password + ';'
db = pyodbc.connect(connection_string)
SQL = 'CREATE TABLE saleout (id COUNTER PRIMARY KEY,product_name VARCHAR(25));'
db.cursor().execute(SQL)

# Doing a simple SELECT query
connStr = (
 r'Driver={SQL Server};'
 r'Server=sqlserver;'
 #r'Server=127.0.0.1,52865;' +
 #r'Server=(local)\SQLEXPRESS;'
 r'Database=adventureworks;'
 #r'Trusted_Connection=Yes;'
 r'UID=sa;'
 r'PWD=sapassword;'
 )
db = pypyodbc.connect(connStr)
cursor = db.cursor()

# Sample with just a raw query:
cursor.execute("select client_name, client_lastname, [phone number] from Clients where client_id like '01-01-00%'")

# Using parameters (IMPORTANT: YOU SHOULD USE TUPLE TO PASS PARAMETERS)
# Python note: a tuple with just one element must have a trailing comma, otherwise is just a enclosed variable
cursor.execute("select client_name, client_lastname, [phone number] "
"from Clients where client_id like ?", ('01-01-00%', ))

# Sample, passing more than one parameter
cursor.execute("select client_name, client_lastname, [phone number] "
"from Clients where client_id like ? and client_age < ?", ('01-01-00%', 28))

# Method 1, simple reading using cursor
while True:
 row = cursor.fetchone()
 if not row:
  break
 print("Client Full Name (phone number): ", row['client_name'] + ' ' + row['client_lastname'] + '(' + row['phone number'] + ')')

# Method 2, we obtain dict's all records are loaded at the same time in memory (easy and verbose, but just use it with a few records or your app will consume a lot of memory), was tested in a modern computer with about 1000 - 3000 records just fine...
import pprint; pp = pprint.PrettyPrinter(indent=4)
columns = [column[0] for column in cursor.description]
for row in cursor.fetchall():
 pp.pprint(dict(zip(columns, row)))

# Method 3, we obtain a list of dict's (represents the entire query)
query_results = [dict(zip([column[0] for column in cursor.description], row)) for row in cursor.fetchall()]
pp.pprint(query_results)

# When cursor was used must be closed, if you will not use again the db connection must be closed too.
cursor.close()
db.close()

How to use it without install (the latest version from here)

Just copy the latest pypyodbc.py downloaded from this repository on your project folder and import the module.

Install
If you have pip available (keep in mind that the version on pypi may be old):

pip install pypyodbc

Or get the latest pypyodbc.py script from GitHub (Main Development site)

python setup.py install

以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，同时也希望多多支持！

# python # 操作access # access # 读写 # python读写access # Python操作Access数据库基本步骤分析 # Python3.7 pyodbc完美配置访问access数据库 # python连接access数据库两种方式总结 # 记录集 # 第一个 # 下载地址 # 见分晓 # 遍历 # 多说 # 移到 # 可以利用 # 过程中 # 首条 # 字段名 # coding # pypyodbc # utf # env # usr # bin # github # True

相关栏目：【网站优化151355 】【网络推广146373 】【网络技术251813 】【 AI营销90571 】

上一篇：如何在 PHP 中安全高效地获取类方法中定义的字段属性值以构

下一篇：如何确保建站宝盒Logo在多平台上保持视觉统一？

相关栏目网站优化
网络推广
网络技术
AI营销

最新文章 Sublime怎么一键压缩JS代码 Su sublime如何在搜索中使用正则表达式 Sublime如何设置透明窗口效果 Su mysql如何设计商品表结构_mysql css属性背景图不显示怎么办_通过检查路如何使用Golang实现排序_Golan 农历闰月是怎么回事_为合回归年加一月调整塑造《刺客信条》艾吉奥传奇的编剧离开育碧 1英里等于多少公里 1mile和km的换 css grid布局中行和列是如何定义的 PS批量旋转和翻转图片，快速校正图片方向 C# Swagger UI自定义方法 C OPPO手机九宫格和全键盘怎么切换_OP Go语言如何实现用户登录注册_Golan 1节飞行速度多少公里每小时 1节是多少公纸嫁衣8千子树第五章庙门怎么开启庙门 Laravel 多行数据编辑表单中实现逐明日之后如何提升钓鱼等级明日之后提升钓支付宝怎样查年度账单_支付宝年度账单查看 C# 多线程UI更新Dispatcher

上一篇：如何在 PHP 中安全高效地获取类方法中定义的字段属性值以构

下一篇：如何确保建站宝盒Logo在多平台上保持视觉统一？