Python3操作MySQL数据库(驱动：pymysql)

建库建表

create database wuSir default character set utf8 collate utf8_general_ci;

use wuSir;


create table auth_info(
    aid int not null auto_increment primary key,
    auth_name varchar(32),
    unique(auth_name)
    )engine=innodb default charset=utf8;


create table user_info(
    uid int not null auto_increment primary key,
    name varchar(32),
    passwd varchar(32),
    sex ENUM("男","女")
    )engine=innodb default charset=utf8;

create table user_auth(
    id int,
    auth_id int,
    constraint auth_info foreign key(auth_id) references auth_info(aid),
    constraint user_info foreign key(id) references user_info(uid)
    )engine=innodb default charset=utf8;



insert into auth_info(auth_name) values("订单管理"),("用户管理"),("菜单管理"),("权限分配"),("Bug管理");

insert into user_info(name,passwd,sex) values("alex",123,"男"),("egon",123,"男");

insert into user_auth(id,auth_id) values(1,1),(2,1),(2,2),(2,3);

原生模块pymysql

一、安装PyMySQL

Python是编程语言，MySQL是关系数据库，它们是两种不同的技术；要想使Python操作MySQL数据库需要使用驱动。这里选用pymysql驱动。

$ pip3 install pymysql  //安装
$ pip3 show pymysql   //查看是否安装成功

二、创建MySQL表

执行下面的SQL语句，创建一张users 表。

CREATE TABLE `users` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(255) COLLATE utf8_bin NOT NULL,
    `password` VARCHAR(255) COLLATE utf8_bin NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
AUTO_INCREMENT=1 ;

三、Python操作MySQL

3.0 执行SQL

# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')

# 创建游标
cursor = conn.cursor()

# 执行SQL语句,并返回受影响行数
# 方式一：
effect_row = cursor.execute("update hosts set host = '1.1.1.2'")
# 方式二：
sql = "select * from course where cid=1"
effect_row = cursor.execute(sql)
# 方式三：
sql = "select * from course where cid='%s'" %(1,)
effect_row = cursor.execute(sql)
# 方式四：普通 列表
sql = "select * from course where cid='%s'"
effect_row = cursor.execute(sql,1)
effect_row = cursor.execute(sql,[1])
# 方式五：字典格式
sql = "select * from course where cid='%(u)s'"
effect_row = cursor.execute(sql,{"u":1})

# 提交，不然无法保存新建或者修改的数据
conn.commit()

# 获取最新自增ID
new_id = cursor.lastrowid
# 获取第一行数据
row_1 = cursor.fetchone()
# 获取前n行数据
# row_2 = cursor.fetchmany(3)
# 获取所有数据
# row_3 = cursor.fetchall()
注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：

cursor.scroll(1,mode='relative')  # 相对当前位置移动
cursor.scroll(2,mode='absolute') # 相对绝对位置移动
注：关于默认获取的数据是元祖类型，如果想要或者字典类型的数据，即：
# 游标设置为字典类型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 关闭游标
cursor.close()
# 关闭连接
conn.close()

3.1 插入数据：

import pymysql.cursors

# 连接MySQL数据库
connection = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='198876', db='guest', 
                             charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)

# 通过cursor创建游标
cursor = connection.cursor()

# 创建sql 语句，并执行
sql = "INSERT INTO `users` (`email`, `password`) VALUES ('huzhiheng@itest.info', '123456')"
cursor.execute(sql)

#插入一行
# sql = "insert into student_info(sname,gender,class_id) VALUES('alex1','女',2)"

#插入多行
sql = "insert into student_info(sname,gender,class_id) VALUES('alex1','女',2),('alex2','女',2),('alex3','女',2)"
r = cursor.execute(sql)

#或

sql = "insert into userinfo(username,password) values(%s,%s)"
# 受影响的行数
r = cursor.executemany(sql,[('egon','sb'),('laoyao','BS')])

# 提交SQL
connection.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

host为数据库的主机IP地址
port为MySQL的默认端口号
user为数据的用户名
password为数据库的登录密码
db为数据库的名称。
cursor()方法创建数据库游标。
execute()方法执行SQL语句。
commit()将数据库的操作真正的提交到数据。

3.2 查询数据：

import pymysql.cursors

# 连接MySQL数据库
connection = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='198876', db='guest', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)

# 通过cursor创建游标
cursor = connection.cursor()

# 执行数据查询
sql = "SELECT `id`, `password` FROM `users` WHERE `email`='huzhiheng@itest.info'"
cursor.execute(sql)

#查询数据库单条数据
result = cursor.fetchone()
print(result)

print("-----------华丽分割线------------")

# 执行数据查询
sql = "SELECT `id`, `password` FROM `users`"
cursor.execute(sql)

#查询数据库多条数据
result = cursor.fetchall()
for data in result:
    print(data)


# 关闭数据连接
connection.close()

运行结果：

{'password': '123456', 'id': 1}
-----------华丽分割线------------
{'password': '123456', 'id': 1}
{'password': '654321', 'id': 2}

fetchone() 用于查询单条数据。
fetchall() 用于查询多条数据。
close() 最后不要忘记了关闭数据连接。

ORM框架 SQLAchemy

不同数据库连接方式

MySQL-Python
    mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>

pymysql
    mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]

MySQL-Connector
    mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>

cx_Oracle
    oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]

更多详见：http://docs.sqlalchemy.org/en/latest/dialects/index.html

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import create_engine


engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)

# 执行SQL
# cur = engine.execute(
#     "INSERT INTO hosts (host, color_id) VALUES ('1.1.1.22', 3)"
# )

# 新插入行自增ID
# cur.lastrowid

# 执行SQL
# cur = engine.execute(
#     "INSERT INTO hosts (host, color_id) VALUES(%s, %s)",[('1.1.1.22', 3),('1.1.1.221', 3),]
# )


# 执行SQL
# cur = engine.execute(
#     "INSERT INTO hosts (host, color_id) VALUES (%(host)s, %(color_id)s)",
#     host='1.1.1.99', color_id=3
# )

# 执行SQL
# cur = engine.execute('select * from hosts')
# 获取第一行数据
# cur.fetchone()
# 获取第n行数据
# cur.fetchmany(3)
# 获取所有数据
# cur.fetchall()

参考资料
https://www.cnblogs.com/xuyaping/p/7106443.html