使用Python对Access读写操作
作者:骑着螞蟻流浪 时间:2021-01-07 04:20:36
学习Python的过程中,我们会遇到Access的读写问题,这时我们可以利用win32.client模块的COM组件访问功能,通过ADODB操作Access的文件。
需要下载安装pywin32与AccessDatabaseEngine.exe
pywin32下载地址:https://www.jb51.net/softs/695840.html
AccessDatabaseEngine.exe下载 https://www.jb51.net/softs/291508.html
64位下载:https://www.jb51.net/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
来源:http://www.cnblogs.com/mayi0312/p/6646957.html
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Asp测试网速代码(带进度条)
![](https://img.aspxhome.com/file/UploadPic/200711/12/20071112131916460s.jpg)
python使用yaml 管理selenium元素的示例
![](https://img.aspxhome.com/file/2023/9/79579_0s.png)
php 图片上添加透明度渐变的效果
Python人工智能深度学习模型训练经验总结
![](https://img.aspxhome.com/file/2023/4/120964_0s.png)
在Django的View中使用asyncio的方法
Python 学习教程之networkx
Python对FTP交互封装的实现
python 字符串只保留汉字的方法
js控制表单不能输入空格的小例子
python3中set(集合)的语法总结分享
Python实现堆排序的方法详解
![](https://img.aspxhome.com/file/2023/9/84119_0s.jpg)
oracle 触发器 学习笔记
Oracle开发之报表函数
Python实现复杂对象转JSON的方法示例
Python字节码与程序执行过程详解
![](https://img.aspxhome.com/file/2023/0/64600_0s.png)
推荐个辅助软件FastStone Capture
![](https://img.aspxhome.com/file/UploadPic/200812/23/screenruler1-61s.jpg)
Python爬虫番外篇之Cookie和Session详解
![](https://img.aspxhome.com/file/2023/9/89209_0s.jpg)
对Django 转发和重定向的实例详解
![](https://img.aspxhome.com/file/2023/2/61442_0s.jpg)