一、Python数据库编程

Python 标准数据库接口为 Python DB-API，Python DB-API 为开发人员提供了数据库应用编程接口。Python 数据库接口支持非常多的数据库，你可以选择适合你项目的数据库。不同的数据库你需要下载不同的 DB API 模块，例如你需要访问 Oracle 数据库和MySQL 数据库，你就需要下载 Oracle 和 MySQL各自对应的 python 数据库模块。

DB-API 是一个规范，它定义了一系列必须的对象和数据库存取方式，以便为各种各样的底层数据库系统和多种多样的数据库接口程序提供一致的访问接口 。Python 的 DB-API 为大多数的数据库实现了接口，使用它连接各数据库后，就可以用相同的方式操作各数据库。

Python DB-API 使用流程：

• 引入 API 模块。
• 获取与数据库的连接。
• 执行 SQL 语句和存储过程。
• 关闭数据库连接。

1. MySQL 数据库

MySQL 数据库数据库的安装请看：https://codeboy.blog.csdn.net/article/details/121604835

2. MySQL 驱动

由于 MySQL 服务器以独立的进程运行，并通过网络对外服务，所以，需要支持 Python 的 MySQL 驱动
来连接到 MySQL 服务器。python 想要使用 MySQL，有以下两个驱动可以使用：
（1） mysql-connector，MySQL 官方提供的 python 驱动
（2） pymysql，Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2 中则用 mysqldb

开发过程中，选择任一个都可以。
使用驱动之前，需要先安装，可以通过 pip install 命令来进行安装。

pip install mysql-connector
pip install pymysql

3. 创建数据库连接

（1） Connection 对象

• 用于建立与数据库的连接
• 创建对象：调用 connect()方法

conn=connect(参数列表)

• 参数 host：连接的 mysql 主机，如果本机是’localhost’
• 参数 port：连接的 mysql 主机的端口，默认是 3306
• 参数 db：数据库的名称
• 参数 user：连接的用户名
• 参数 password：连接的密码
• 参数 charset：通信采用的编码方式，要求与数据库创建时指定的编码一致，否则中文会乱码

connection 对象的方法

• close()，关闭数据库连接
• commit()，提交事务，对数据库做出的修改需要提交才会生效
• rollback()，回滚事务，放弃之前的操作
• cursor()，返回 Cursor 对象，用于执行 sql 语句并获得结果

（2） Cursor 对象

•  执行 sql 语句
•  创建对象：调用 Connection 对象的 cursor()方法

cursor1=conn.cursor()
cursor 对象的方法

• close()，关闭游标
• execute(operation [, parameters ])，执行语句，返回受影响的行数
• fetchone()，执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
• next()，执行查询语句时，获取当前行的下一行
• fetchall()，执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回
• scroll(value[,mode])，将行指针移动到某个位置
  1.mode 表示移动的方式
  2.mode 的默认值为 relative，表示基于当前行移动到 value，value 为正则向下移动， value 为负则向上移动
  3.mode 的值为 absolute，表示基于第一条数据的位置，第一条数据的位置为 0

cursor 对象的属性

• rowcount，只读属性，表示最近一次 execute()执行后受影响的行数
• connection，获得当前连接对象

4. 常用操作举例

创建数据库
创建数据库使用 “CREATE DATABASE” 语句，以下创建一个名为 runoob_db 的数据库：

demo_mysql_test.py:
import mysql.connector
mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456" )
mycursor = mydb.cursor() #创建游标
mycursor.execute("CREATE DATABASE test_db")

创建数据库前我们也可以使用 “SHOW DATABASES” 语句来查看数据库是否存在：

demo_mysql_test.py:
import mysql.connector
mydb = mysql.connector.connect( host="localhost", user="root", passwd="123456" )
mycursor = mydb.cursor()
mycursor.execute("SHOW DATABASES")
for x in mycursor: 
    print(x)

或者我们可以直接连接数据库，如果数据库不存在，会输出错误信息：

demo_mysql_test.py:
import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="root",passwd="123456",database="test_db" )

二、数据表操作

1.创建数据表

创建数据表使用 “CREATE TABLE” 语句，创建数据表前，需要确保数据库已存在，以下创建一个名为 sites 的数据表：

demo_mysql_test.py:
import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="root",passwd="123456",database="test_db")
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE sites (name VARCHAR(255), url VARCHAR(255))")

执行成功后，我们可以看到数据库创建的数据表 sites，字段为 name 和 url。

我们也可以使用 “SHOW TABLES” 语句来查看数据表是否已存在：

demo_mysql_test.py:
import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="root",passwd="123456",database="test_db")
mycursor = mydb.cursor()
mycursor.execute("SHOW TABLES")
for x in mycursor: 
    print(x)

2.主键设置

创建表的时候我们一般都会设置一个主键（PRIMARY KEY），我们可以使用"INTAUTO_INCREMENT PRIMARY KEY" 语句来创建一个主键，主键起始值为 1，逐步递增。

如果我们的表已经创建，我们需要使用 ALTER TABLE 来给表添加主键：

import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="root",passwd="123456",database="test_db")
mycursor = mydb.cursor()
mycursor.execute("ALTER TABLE sites ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")

如果你还未创建 sites 表，可以直接使用以下代码创建。

demo_mysql_test.py:
import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="root",passwd="123456",database="test_db")
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE sites (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), url VARCHAR(255))")

3.插入数据

插入数据使用 “INSERT INTO” 语句：

import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="root",passwd="123456",database="test_db")
mycursor = mydb.cursor()
sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
val = ("RUNOOB", "https://www.runoob.com")
mycursor.execute(sql, val)
mydb.commit() # 数据表内容有更新，必须使用到该语句
print(mycursor.rowcount, "记录插入成功。")

批量插入

批量插入使用 executemany() 方法，该方法的第二个参数是一个元组列表，包含了我们要插入的数据：

import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="root",passwd="123456",database="test_db")
mycursor = mydb.cursor()
sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
val = [ ('Google', 'https://www.google.com'), ('Github', 'https://www.github.com'), ('Taobao','https://www.taobao.com'), ('stackoverflow', 'https://www.stackoverflow.com/') ]
mycursor.executemany(sql, val)
mydb.commit() # 数据表内容有更新，必须使用到该语句
print(mycursor.rowcount, "记录插入成功。")

如果我们想在数据记录插入后，获取该记录的 ID ，可以使用以下代码：

import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="root",passwd="123456",database="test_db")
mycursor = mydb.cursor()
sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
val = ("Zhihu", "https://www.zhihu.com")
mycursor.execute(sql, val)
mydb.commit()
print("1 条记录已插入, ID:", mycursor.lastrowid)

4.查询数据

import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="root",passwd="123456",database="test_db")
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM sites")
myresult = mycursor.fetchall() # fetchall() 获取所有记录
for x in myresult: 
    print(x)

也可以读取指定的字段数据：

import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="root",passwd="123456",database="test_db")
mycursor = mydb.cursor()
mycursor.execute("SELECT name, url FROM sites")
myresult = mycursor.fetchall()
for x in myresult: 
    print(x)

如果我们只想读取一条数据，可以使用 fetchone() 方法：

import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="root",passwd="123456",database="test_db")
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM sites")
myresult = mycursor.fetchone()
print(myresult)

where 条件语句

import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="root",passwd="123456",database="test_db")
mycursor = mydb.cursor()
sql = "SELECT * FROM sites WHERE name ='RUNOOB'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult: 
    print(x)

也可以使用通配符 %：

import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="root",passwd="123456",database="test_db")
mycursor = mydb.cursor()
sql = "SELECT * FROM sites WHERE url LIKE '%oo%'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult: 
    print(x)

排序

import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="root",passwd="123456",database="test_db")
mycursor = mydb.cursor()
sql = "SELECT * FROM sites ORDER BY name"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult: 
    print(x)

Limit

import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="root",passwd="123456",database="test_db")
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM sites LIMIT 3")
myresult = mycursor.fetchall()
for x in myresult: 
    print(x)

也可以指定起始位置，使用的关键字是 OFFSET：

import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="root",passwd="123456",database="test_db")
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM sites LIMIT 3 OFFSET 1") # 0 为 第一条，1 为第二条，以此类推
myresult = mycursor.fetchall()
for x in myresult: 
    print(x)

5.删除记录

import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="root",passwd="123456",database="test_db")
mycursor = mydb.cursor()
sql = "DELETE FROM sites WHERE name = 'stackoverflow'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, " 条记录删除")

注意：要慎重使用删除语句，删除语句要确保指定了 WHERE 条件语句，否则会导致整表数据被删除。

6.更新表数据

import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="root",passwd="123456",database="test_db")
mycursor = mydb.cursor()
sql = "UPDATE sites SET name = 'ZH' WHERE name = 'Zhihu'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, " 条记录被修改")

注意：UPDATE 语句要确保指定了 WHERE 条件语句，否则会导致整表数据被更新。

7.删除表

import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="root",passwd="123456",database="test_db")
mycursor = mydb.cursor()
sql = "DROP TABLE IF EXISTS sites" # 删除数据表 sites
mycursor.execute(sql)