一篇文章解决Python操作数据库问题

一，Python数据库API

Python需要为操作不同的数据库使用不同的模块，但基本都遵守Python声明的DB API协议，当前该协议的最新版本是2.0。

使用Python DB API 2.0操作数据库的基本流程如下

调用connect（）方法打开数据库连接，该方法返回数据库连接对象。

通过数据库连接对象打开游标。

使用游标执行SQL语句（包括DDL，DML，select查询语句等）。如果执行的是查询语句，则处理查询数据。

关闭游标。

关闭数据库连接。

使用Python DB API 2.0操作数据库的基本流程 数据库API的核心类

遵守DB API 2.0协议的数据库模块通常会提供一个connect（）函数，该函数用于连接数据库，并返回数据库连接对象。数据库连接对象通常会具有如下方法和属性：

cursor（factory = Cursor）：打开游标。

commit（）：提交事务。

rollback（）：回滚事务。

close（）：关闭数据库连接。

isolated_level：返回或设置数据库连接中事务的隔离等级。

in_transaction：判断当前是否处于事务中。

游标对象通常会具有如下方法和属性：

execute（sql [，parameters]）：执行SQL语句。参数用作SQL语句中的参数指定值。

executemany（sql，seq_of_parameters）：重复执行SQL语句。可以通过seq_of_parameters序列为SQL语句中的参数指定值，该序列有多少个元素，SQL语句被执行多少次。

fetchone（）：获取查询结果集的下一行。如果没有下一行，则返回None。

fetchmany（size = cursor.arraysize）：返回查询结果集的下N行组成的列表。如果没有更多的数据行，则返回空列表。

fetchall（）：返回查询结果集的全部行组成的列表。

close（）：关闭游标。

对于executemany（）方法，该方法所修改的记录条数也可以通过该属性获取。

lastrowid：该预期属性可获取最后修改行的rowid。

arraysize：用于设置或获取fetchmany（）默认获取的记录条数，该属性替换为1。某些数据库模块没有该属性。

描述：该预期属性可获取最后一次查询返回的所有列的列名信息。

连接：该预期属性返回创建游标的数据库连接对象。某些数据库模块没有该属性。

二，Python操作数据库SQLite

SQLite是一种嵌入式数据库，它的数据库就是一个文件。

Python内置了SQLite3，在Python中使用SQLite，不需要安装任何东西，直接使用。

import sqlite3<font></font>

# ①、打开或创建数据库<font></font>

# 也可以使用特殊名：:memory:代表创建内存中的数据库<font></font>

conn = sqlite3.connect('first.db')<font></font>

# ②、获取游标<font></font>

c = conn.cursor()<font></font>

# ③、执行DDL语句创建数据表<font></font>

c.execute('''create table user_tb(<font></font>

    _id integer primary key autoincrement,<font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

    命名文字，</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

    通过文字，</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

    性别文字）'''）</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

＃执行DDL语句创建数据表</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

c.execute（'''''创建表order_tb（</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

    _id整数主键自动递增，</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

    item_name文字，</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

    item_price实数，</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

    item_number是实数，</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

    user_id整数，</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

    外键（user_id）引用user_tb（_id））'''）</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

＃执行DML的插入语句插入数据</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

c.execute（'插入user_tb值（null，？，？，？）'，</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

    （'孙悟空'，'123456'，'男性'））</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

c.execute（'插入order_tb值（null，？，？，？，？）'，</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

    （“鼠标”，“ 34.2”，“ 3”，1））</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

＃SQLite数据库API默认是开启了事务，需要提交事务</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

conn.commit（）</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

＃调用executemany（）方法把同一条SQL语句执行多次</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

c.executemany（'插入user_tb值（null，？，？，？）'，</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

    （（'sun'，'123456'，'male'），</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

    （“ bai”，“ 123456”，“ female”），</font></font><font></font>

    ('zhu', '123456', 'male'),<font></font>

    ('niu', '123456', 'male'),<font></font>

    ('tang', '123456', 'male')))<font></font>

# 通过rowcount获取被修改的记录条数<font></font>

print('修改的记录条数：', c.rowcount)<font></font>

conn.commit()<font></font>

# ④、关闭游标<font></font>

c.close()<font></font>

# ⑤、关闭连接<font></font>

conn.close()

三，Python操作数据库MySQL

安装MySQL驱动

由于MySQL服务器以独立的进程运行，并通过网络对外服务，所以需要支持Python的MySQL驱动来连接MySQL服务器。

MySQL官方提供了mysql-connector-python驱动，但是安装的时候需要给pip命令加上参数--allow-external：

我们演示如何连接到MySQL服务器的测试数据库：

import mysql.connector<font></font>

# ①、连接数据库<font></font>

conn = conn = mysql.connector.connect(user='root', password='123456',<font></font>

    host='localhost', port='3306',<font></font>

    database='python', use_unicode=True)<font></font>

# ②、获取游标<font></font>

c = conn.cursor()<font></font>

# ③、执行DDL语句创建数据表<font></font>

c.execute('''create table user_tb(<font></font>

    user_id int primary key auto_increment,<font></font>

    name varchar(255),<font></font>

    pass varchar(255),<font></font>

    gender varchar(255))''')<font></font>

# 执行DDL语句创建数据表<font></font>

c.execute('''create table order_tb(<font></font>

    order_id integer primary key auto_increment,<font></font>

    item_name varchar(255),<font></font>

    item_price double,<font></font>

    item_number double,<font></font>

    user_id int,<font></font>

    foreign key(user_id) references user_tb(user_id) )''')<font></font>

# 执行DML的insert语句插入数据<font></font>

c.execute('insert into user_tb values(null, %s, %s, %s)',<font></font>

    ('孙悟空', '123456', 'male'))<font></font>

c.execute('insert into order_tb values(null, %s, %s, %s, %s)',<font></font>

    ('鼠标', '34.2', '3', 1))<font></font>

# 调用executemany()方法把同一条SQL语句执行多次<font></font>

c.executemany('insert into user_tb values(null, %s, %s, %s)',<font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

    （（'sun'，'123456'，'male'），</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

    （“ bai”，“ 123456”，“ female”），</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

    （“ zhu”，“ 123456”，“ male”），</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

    （“牛”，“ 123456”，“男”），</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

    （'tang'，'123456'，'male'）））</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

＃关闭事务支持conn.autocommit = True</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

conn.commit（）</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

＃④，关闭游标</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

c.close（）</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

＃⑤，关闭连接</font></font><font></font><font style="vertical-align: inherit;"><font style="vertical-align: inherit;">

conn.close（）</font></font>

与连接SQLite比例，最大的区别在于第5行代码，程序要连接localhost主机上3306端口服务的python数据库。

与连接SQLite评分，MySQL语句中的占位符：％s

MySQL数据库模块的连接对象有一个autoconunit属性，如果将属性设置为True，则意味着关闭该连接的事务支持，程序每次执行DML语句之后都会自动提交，这样程序就无须调用连接对象的commit（ ）方法来提交事务了。