pyodbc操作Access数据库

桃扇骨 2022-04-11 14:18 363阅读 0赞

现在各种各样的数据库层出不穷，什么MySQL、PostgreSQL、MongoDB这些都是比较火的，还有一些稍微小众一点的数据库就更多了。相比之下，微软Office套件里面附带的Access数据库究落寞了很多。不过好歹Access数据库还是有些用处的，作为一个既是数据库又是前端界面的软件，对一些非科班又有数据库需求的人来说还是比较好用的。本文利用pyodbc来简单介绍一下如何操作Access数据库。

## 安装pyodbc ##

最简单的方式自然是pip安装了：

pip install pyodbc

如果会使用pipenv的话就更好了，一整套Python虚拟环境管理方案，强烈推荐：

pipenv install pyodbc

最近又了解了一下faker类库，所以顺便用faker添加了一些模拟数据。所以顺便还要安装faker：

pip install faker

## 安装Access数据库引擎 ##

Access数据库稍微有点特殊，还需要安装一个额外的数据库引擎。注意版本要和安装的Office版本一样，如果安装的Office是32位而安装了64位的引擎，也没办法使用。如果安装的是Office 2019的话，用2016引擎就好了。其他版本的没试过。

*  [Office 2016引擎][Office 2016]
 *  [Office 2010引擎][Office 2010]

安装完毕后打开ODBC数据源，查看一下平台，如果显示出了具体位数，说明安装成功。当然没安装以前也可以先查看一下，应该是N/A，表示尚未安装。

![ODBC数据源][ODBC]

这样一来准备工作就结束了。

## 创建Access数据库 ##

首先打开Access软件，创建一个数据库。创建完毕后记得关闭数据库，否则接下来代码会报错。

## 增删查改 ##

pyodbc库的用法和一般的Python SQL驱动类似，我就不做过多介绍了，很简单的示例代码，做了一些简单注释。首先创建了3000条用户数据，然后简单查询了一下所有公务员。数据库字段完全按照faker提供的模拟数据来设计的。代码只用了简单的SQL添加和查询功能，不过更新和删除也很简单，就不写了。

import pyodbc
    import faker
    
    create_table_sql = '''\ create table user ( id autoincrement primary key, username varchar(255) unique, nickname varchar(255) not null, password varchar(20) not null, address varchar(255), birthday date, company varchar(30), job varchar(20), telephone varchar(14) ) '''
    
    insert_table_sql = '''\ insert into user(username, nickname, password, address, birthday, company, job, telephone) values (?, ?, ?, ?, ?, ?, ?, ?) '''
    
    select_public_servant_sql = '''\ select * from user where job = '公务员' '''
    
    # 准备模拟数据
    fake = faker.Faker('zh_CN')
    # 设置种子值，不设的话每次随机的都不一样
    fake.seed(47)
    
    db_file_location = r'D:\desktop\db.accdb'
    # 这里用的是Python3.5的语法，如果是低版本Python的话需要改成普通方式
    connection = pyodbc.connect(
        rf'Driver={ {Microsoft Access Driver (*.mdb, *.accdb)}};DBQ={db_file_location};')
    
    connection.autocommit = True
    
    # 第一次创建表，将其设置为False
    table_exists = False
    if not table_exists:
        with connection.cursor() as cursor:
            cursor.execute(create_table_sql)
    
    # 添加数据
    with connection.cursor() as cursor:
        for _ in range(3000):
            cursor.execute(insert_table_sql, (fake.pystr(min_chars=6, max_chars=10),
                                              fake.name(),
                                              fake.password(length=10),
                                              fake.address(),
                                              fake.date_of_birth(minimum_age=0, maximum_age=120),
                                              fake.company(),
                                              fake.job(),
                                              fake.phone_number()))
    
        # 查询一下所有公务员
        cursor.execute(select_public_servant_sql)
        results = cursor.fetchall()
        for row in results:
            print(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], sep='\t')

代码完成之后，打开Access数据库看一下，效果很完美，几乎和真的一样（滑稽）。当然这只是简单的演示了一下，感觉对于一些简单的项目，又希望能直接查看数据库内容，可以考虑一下Access数据库。

![添加的模拟数据][1240]

一开始我在研究的时候，还出现了`Database you are trying to open requires a newer version of Microsoft Access`这么一个错误，我还有点纳闷，我明明用的已经是Access 2019了，为啥还提示我需要更新版本的Access。最后发现可能是我一开始代码写的有问题，或者是忘了关闭数据库，导致的数据库文件损坏了。删掉了原来的accdb文件重新创建了一个，就能完美运行了。

[Office 2016]: https://www.microsoft.com/en-us/download/details.aspx?id=54920
[Office 2010]: https://www.microsoft.com/en-us/download/details.aspx?id=13255
[ODBC]: https://upload-images.jianshu.io/upload_images/832668-b6f29aa25bfd3e94.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240
[1240]: https://upload-images.jianshu.io/upload_images/832668-2c0805a42a15c64c.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240