Python数据库编程（MySQL）

前言

导入pymysql模块，若没有，执行

pip install PyMySQL

一、connection对象

1、生成对象的方式

2、connect对象支持的方法

3、测试实例

# -*- coding: utf-8 -*-

import pymysql

db_connection = pymysql.connect(
    host='127.0.0.1',
    user='xxxx',
    password='xxxx',
    database='python2test',
    charset='utf8'
)
cursors = db_connection.cursor()

if __name__ == '__main__':
    print(db_connection)
    print(cursors)

4、运行结果

<pymysql.connections.Connection object at 0x0000000004EFD358>
<pymysql.cursors.Cursor object at 0x00000000050F4518>

二、cursor对象

游标对象：用于执行查询和获取结果

1、cursor对象支持的方法

2、其中execute方法：执行SQL将结果从数据库获取到客户端的过程如下：

3、fetch*()方法的过程如下：

三、执行简单查询数据的版本

# -*- coding: utf-8 -*-

import pymysql

db_connection = pymysql.connect(
    host='127.0.0.1',
    user='xxxx',
    password='xxxx',
    database='python2test',
    charset='utf8'
)
# 使用 cursor() 方法创建一个游标对象 cursor
cursors = db_connection.cursor()


# 使用 execute()  方法执行 SQL 查询
cursors.execute("SELECT VERSION()")

# 使用 fetchone() 方法获取单条数据.
data = cursors.fetchone()

print ("Database version : %s " % data)

# 关闭数据库连接
db_connection.close()
运行结果：

Database version : 5.5.53 

四、创建数据库表

# -*- coding: utf-8 -*-

import pymysql

# 打开数据库连接
db = pymysql.connect(
                    host='127.0.0.1',
                    user='XXXX',
                    password='XXXX',
                    database='python2test',
                    charset='utf8'
                    )

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 使用 execute() 方法执行 SQL，如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# 使用预处理语句创建表
sql = """CREATE TABLE EMPLOYEE (
            FIRST_NAME  CHAR(20) NOT NULL,
            LAST_NAME   CHAR(20),
            AGE         INT,
            SEX         CHAR(1),
            INCOME      FLOAT
            )"""

cursor.execute(sql)

# 关闭数据库连接
db.close()
运行结果：

创建之前查询数据库中存在的表如下：

mysql> USE PYTHON2TEST;
Database changed
mysql> SHOW TABLES;
Empty set (0.00 sec)
创建之后查询数据库中存在的表如下：

mysql> SHOW TABLES;
+-----------------------+
| Tables_in_python2test |
+-----------------------+
| employee              |
+-----------------------+
1 row in set (0.00 sec)

五、数据库插入操作

# encoding:utf-8

import pymysql

# 打开数据库连接
db = pymysql.connect(
    host='127.0.0.1',
    user='xxxx',
    password='xxxx',
    database='python2test',
    charset='utf8'
)

# 使用cursor()方法获取操作游标
cursor = db.cursor()

# SQL 插入语句
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
    # 执行sql语句
    cursor.execute(sql)
    # 提交到数据库执行
    db.commit()
    print("插入成功！")
except:
    # 如果发生错误则回滚
    db.rollback()
    print("插入失败！")

# 关闭数据库连接
db.close()
运行结果：

运行之前的查询的结果：

mysql> SELECT * FROM EMPLOYEE;
Empty set (0.00 sec)
运行之后的查询的结果：

mysql> SELECT * FROM EMPLOYEE;
+------------+-----------+------+------+--------+
| FIRST_NAME | LAST_NAME | AGE  | SEX  | INCOME |
+------------+-----------+------+------+--------+
| Mac        | Mohan     |   20 | M    |   2000 |
+------------+-----------+------+------+--------+
1 row in set (0.00 sec)

六、数据库查询操作

Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。

• fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
• fetchall(): 接收全部的返回结果行.
• rowcount: 这是一个只读属性，并返回执行
• execute()方法后影响的行数。

# encoding:utf-8

import pymysql

# 打开数据库连接
db = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='root',
    database='python2test',
    charset='utf8'
)
# 使用cursor()方法获取操作游标
cursor = db.cursor()

# SQL 查询语句
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > '%d'" % (1000)
try:
    # 执行SQL语句
    cursor.execute(sql)
    # 获取所有记录列表
    results = cursor.fetchall()
    print("FIRST_NAME\tLAST_NAME\tAGE\tSEX\tINCOME |")
    for row in results:
        fname = row[0]
        lname = row[1]
        age = row[2]
        sex = row[3]
        income = row[4]
        # 打印结果
        print("%s\t\t\t%s\t\t\t%d\t%s\t%d" %
              (fname, lname, age, sex, income))
except:
    print("Error: unable to fetch data")

# 关闭数据库连接
db.close()
运行结果：

FIRST_NAME  LAST_NAME   AGE SEX INCOME |
TIC         TOC         20  M   2000
ONE         INF         20  M   10000
[Finished in 0.2s]

七、数据库更新操作

# encoding:utf-8

import pymysql

# 打开数据库连接
db = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='root',
    database='python2test',
    charset='utf8'
)
# 使用cursor()方法获取操作游标
cursor = db.cursor()

# SQL 更新语句
sql = "UPDATE EMPLOYEE SET INCOME = INCOME * 1.2 WHERE SEX = '%c'" % ('M')
try:
    # 执行SQL语句
    cursor.execute(sql)
    # 提交到数据库执行
    db.commit()
except:
    # 发生错误时回滚
    db.rollback()

# 关闭数据库连接
db.close()
运行结果：

mysql> SELECT * FROM EMPLOYEE;
+------------+-----------+------+------+--------+
| FIRST_NAME | LAST_NAME | AGE  | SEX  | INCOME |
+------------+-----------+------+------+--------+
| TIC        | TOC       |   20 | M    |   2400 |
| ONE        | INF       |   20 | M    |  12000 |
+------------+-----------+------+------+--------+
2 rows in set (0.00 sec)

八、数据库删除操作

# encoding:utf-8

import pymysql

# 打开数据库连接
db = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='root',
    database='python2test',
    charset='utf8'
)
# 使用cursor()方法获取操作游标
cursor = db.cursor()

# SQL 删除语句
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
    # 执行SQL语句
    cursor.execute(sql)
    # 提交修改
    db.commit()
except:
    # 发生错误时回滚
    db.rollback()

# 关闭连接
db.close()