access-python调用odbc数据库

1. 安装 Access (以access 2016 为例)

2. 安装 pyodbc

1
pip install pyodbc

3. 新建数据库并进行操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
import pyodbc

# 数据库路径
accdb_dir = 'C:/Users/xxx/Documents/Database1.accdb'


# 1.1 创建数据表
create_table_sql = '''\
create table suanfa
(
id autoincrement primary key,
value1 varchar(255),
value2 varchar(255),
result varchar(255)
)
'''

# 2.2 查询、插入、更新
select_sql = 'select * from suanfa where id>0'

insert_sql = 'insert into suanfa(value1, value2, result) values (?, ?, ?)'

update_sql = 'update suanfa(result,) values (?,)'


# 2 连接数据库
connection = pyodbc.connect(rf'Driver={{Microsoft Access Driver (*.mdb, *.accdb)}};DBQ={accdb_dir};')
connection.autocommit = True

# 2.1 创建数据表,插入数据
with connection.cursor() as cursor:
cursor.execute(create_table_sql)

with connection.cursor() as cursor:
for i in range(10):
cursor.execute(insert_sql, i, i, i+1)


# 2.2. 读取、更新数据
with connection.cursor() as cursor:
cursor.execute(select_sql)
values = cursor.fetchall()
print(values)



aa, bb = values[0], values[1]
rst = aa * bb # 改成我们的算法: rst = f(a, b)
cursor.execute(update_table_sql, rst)

for row in cursor.execute(select_sql):
print(row)