access-python调用odbc数据库

发表于 分类于 本文字数： 218 阅读时长 ≈ 1 分钟

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)