python创建mysql数据库_python中MySQL数据库相关操作

一 安装基本环境

1 简介

MySQL 基于TCP 协议之上的开发，但是网络连接后，传输的数据必须遵循MySQL的协议，封装好MySQL协议的包，就是驱动程序

MySQL 的驱动

MySQLDB 最有名的库，对MySQL 的C Client 封装实现，支持python2，不更新了，不支持python3

MySQL 官方的connector

pymysql 语法兼容MySQLdb，使用python写的库，支持python3

2 安装MySQL数据库

本文使用的是mariadb数据库，与MySQL相似

1 挂载镜像文件（本次使用的是本地镜像文件）

2 启动MySQL数据库

3 创建用户名和密码并刷新

grant all(表示所有操作) on .(库.表(所有的，也可以指定)) to root@localhost(设置用户名为root，链接为本地链接) identified by 'roiot123';(设置密码)

flush privileges;(刷新权限)

4 修改默认字符集：

server.cnf 中的操作（前面必须有空格，否则不生效，不能写入汉字）

client.cnf 操作同上

重启加载字符集

5 查看字符集是否加载完毕

如上，则表示加载完毕！！！

6 创建数据库

二 MySQL-python

1 安装

1 安装

2 查看安装是否完成，若完成，则不会报错

2 连接一般流程

建立连接

获取游标

执行SQL

提交事务

释放资源

2 MySQL链接基本操作及说明

1 导入MySQLdb 模块

导入名重命名重命名

2 创建连接

参数含义如下

connection 初始化常用参数

说明

host

主机

user

用户名

password

密码

database

数据库

port

端口

其中必选参数是user和passwd 其他可选

其中user 表示数据库的用户名，就是上面初始化的用户名和密码，db 是上面初始化的数据库，host 表示本地链接，可以使用IP地址或域名进行远程链接，charset 表示链接使用的字符集，如果和上面的utf8不对应，则可能出现乱码现象

3 初始化游标

4 使用游标.execute('sql')语句负责向MySQL数据库传递消息。

对于数据库的操作有 增insert 删 delete 改 update 等 查 select show 等

5 提交

创建完成后需要提交，如果不提交则不生效，提交使用的是创建的链接的关键字。

6 关闭链接

需要关闭链接，首先需要关闭的是游标，其次是链接。

7 查看

在MySQL数据库中进行查看：

3 数据库进阶

1 显示设置

显示与数据库查询相似的结果show 和 select

数据库中的显示

再次创建一个数据表以备查询所用

1 进行查看显示

使用游标.fetchone()表示每次查看一行操作，两个表示两个操作一起输出

2 显示所有查看的结果

3 显示指定的数量

4 进行查看使用cur.scroll(0,'absolute')

将其恢复游标到起始位置，可以进行多次查看，如果没有此配置，默认重上一次查询的下一行开始查询

2 多行插入

1 使用for 循环遍历的方式插入：

查看

2 将sql 语句与cur.execute 分离的方式插入

查看

3 进行多行插入

查看

当%s 没有双引号时：

查看

4 ：数据库应用：

1 生成姓名

查看生成结果：

2 判断数据库的某个表是否存在

3 应用封装mysql数据库的类

查看是否生成：

三 pymysql

1 安装pymysql

1 安装

pymysql 是第三方模块库，需要安装

pip install pymysql

2 参数简介和基本链接

1 基本参数详解

pymysql.connect() 方法返回的是connections模块下的connection类实例，connect方法传递就是给connection类的__init__提供参数

源码如下

def __init__(self, host=None, user=None, password="",

database=None, port=0, unix_socket=None,

charset='', sql_mode=None,

read_default_file=None, conv=None, use_unicode=None,

client_flag=0, cursorclass=Cursor, init_command=None,

connect_timeout=10, ssl=None, read_default_group=None,

compress=None, named_pipe=None,

autocommit=False, db=None, passwd=None, local_infile=False,

max_allowed_packet=16*1024*1024, defer_connect=False,

auth_plugin_map=None, read_timeout=None, write_timeout=None,

bind_address=None, binary_prefix=False, program_name=None,

server_public_key=None):

上述初始化参数中autocommit=False，则指的是默认的事务提交是关闭的，及需要手动提交事务

Connection.ping() 方法，测试数据库服务器是否活着，有一个参数热connection表示断开与服务器连接是否重连

def ping(self, reconnect=True):

"""

Check if the server is alive.

:param reconnect: If the connection is closed, reconnect.

:raise Error: If the connection is closed and reconnect=False.

"""

if self._sock is None:

if reconnect:

self.connect()

reconnect = False

else:

raise err.Error("Already closed")

try:

self._execute_command(COMMAND.COM_PING, "")

self._read_ok_packet()

except Exception:

if reconnect:

self.connect()

self.ping(False)

else:

raise

2 conn.ping 相关测试

#!/usr/bin/poython3.6

#conding:utf-8

from pymysql.connections import Connection

conn=None

try:

conn=Connection('192.168.1.200','test','Admin@Root123','test')

print (conn.ping(False))

finally:

if conn:

print('关闭链接:',conn.ping(False))

conn.close()

结果如下

3 游标 Cursor

操作数据库，必须使用游标，需要先获取一个游标对象

Connection.cursor(cursor=None) 方法返回一个新的游标对象

连接没有关闭之前，游标对象可以反复使用

cursor参数，可以指定一个Cursor类，如果为None,则使用默认Cursor类

数据库操作需要使用Cursor 类的实例，提供的execute()方法，执行SQL语句，成功返回影响行数。

基本代码如下

#!/usr/bin/poython3.6

#conding:utf-8

from pymysql.connections import Connection

conn=None

try:

conn=Connection('192.168.1.200','test','Admin@Root123','test')

print (conn.ping(False))

# 获取游标

cursor=conn.cursor()

sql="insert into t(id,username,password) values(3,'mysql','mysql')"

line=cursor.execute(sql)

# 此处未提交事务。若关闭，则直接导致事务回滚

print (line)

cursor.close()

finally:

if conn:

print('关闭链接:',conn.ping(False))

conn.close()

结果如下

数据库结果如下

4 添加事务管理

#!/usr/bin/poython3.6

#conding:utf-8

from pymysql.connections import Connection

conn=None

try:

conn=Connection('192.168.1.200','test','Admin@Root123','test')

print (conn.ping(False))

# 获取游标

cursor=conn.cursor()

sql="insert into t(id,username,password) values(3,'mysql','mysql')"

line=cursor.execute(sql)

# 此处未提交事务。若关闭，则直接导致事务回滚

print (line)

cursor.close()

# 若没有异常，则提交事务

conn.commit()

except: # 若存在异常，则回滚事务

conn.rollback()

finally:

if conn:

print('关闭链接:',conn.ping(False))

conn.close()

结果如下

数据库结果如下

5 批量增加数据

#!/usr/bin/poython3.6

#conding:utf-8

from pymysql.connections import Connection

conn=None

cursor=None

try:

conn=Connection('192.168.1.200','test','Admin@Root123','test')

print (conn.ping(False))

# 获取游标

cursor=conn.cursor()

for i in range(5):

sql="insert into t(id,username,password) values(3,'mysql','mysql')"

line=cursor.execute(sql)

print (line)

# 此处未提交事务。若关闭，则直接导致事务回滚

# 若没有异常，则提交事务

conn.commit()

except: # 若存在异常，则回滚事务

conn.rollback()

finally:

if cursor:

cursor.close()

if conn:

conn.close()

数据库结果如下

变量的方式插入多行数据

#!/usr/bin/poython3.6

#conding:utf-8

from pymysql.connections import Connection

conn=None

cursor=None

try:

conn=Connection('192.168.1.200','test','Admin@Root123','test')

print (conn.ping(False))

# 获取游标

cursor=conn.cursor()

for i in range(10,15):

sql="insert into t(id,username,password) values({},'mysql','mysql')".format(i)

line=cursor.execute(sql)

# 此处未提交事务。若关闭，则直接导致事务回滚

# 若没有异常，则提交事务

conn.commit()

except: # 若存在异常，则回滚事务

conn.rollback()

finally:

if cursor:

cursor.close()

if conn:

conn.close()

结果如下

6 查询返回结果

1 概念

cursor类的获取查询结果集的方法有fetchone(),fetchmany(size=None),fetchall() 三种

fetchone()方法，获取结果集的下一行

fetchmany(size=None) 方法，size指定返回的行数的行，None则返回用组

fetchall() 方法，获取所有行

返回多行，如果走到末尾，就返回空元组，否则返回一个元组，其元素就是每一行的记录，每一行的记录都装载在一个元组中。

注意：fetch操作的是结果集，结果集是保存在客户端的，也就是说fetch的时候，查询已经结束了。

2 普通元祖返回

#!/usr/bin/poython3.6

#conding:utf-8

from pymysql.connections import Connection

conn=None

cursor=None

try:

conn=Connection('192.168.1.200','test','Admin@Root123','test')

print (conn.ping(False))

# 获取游标

cursor=conn.cursor()

sql="select * from t"

line=cursor.execute(sql)

print ('获取一个',cursor.fetchone()) # 获取一个

print ('获取下面两个',cursor.fetchmany(2)) # 获取下面两个

print ('获取所有',cursor.fetchall()) # 获取所有

cursor.rownumber=0 # 游标初始化，支持负索引，当大于len的索引，则会不存在

# 此处未提交事务。若关闭，则直接导致事务回滚

# 若没有异常，则提交事务

print ('获取所有',cursor.fetchall()) # 获取所有

print ('获取一个',cursor.fetchone()) #此处无法获取到了

conn.commit()

finally:

if cursor:

cursor.close()

if conn:

conn.close()

结果如下

3 字典返回(字典游标)

Cursor类有一个Mixin的子类DictCursor

只需要cursor=conn.cursor(DictCursor)就行了

返回多行，放在列表中，元素是字典，代表行，返回是列表

#!/usr/bin/poython3.6

#conding:utf-8

from pymysql.connections import Connection

from pymysql.cursors import DictCursor

conn=None

cursor=None

try:

conn=Connection('192.168.1.200','test','Admin@Root123','test')

print (conn.ping(False))

# 获取游标

cursor=conn.cursor(DictCursor)

sql="select * from t"

line=cursor.execute(sql)

print (cursor.fetchall()) # 获取一个

finally:

if cursor:

cursor.close()

if conn:

conn.close()

结果如下

7 SQL 注入

1 概念

猜测后台数据的查询语句使用拼接字符串的方式，从而经过设计为服务端传递参数，令其拼接处特殊的字符串，返回用户想要的结果

2 常用的SQL注入方式

1 数字拼接

#!/usr/bin/poython3.6

#conding:utf-8

from pymysql.connections import Connection

from pymysql.cursors import DictCursor

conn=None

cursor=None

try:

conn=Connection('192.168.1.200','test','Admin@Root123','test')

print (conn.ping(False))

# 获取游标

cursor=conn.cursor()

sql="select * from t where id={}".format('5 or 1=1')

print (sql)

line=cursor.execute(sql)

print (cursor.fetchall()) # 获取一个

finally:

if cursor:

cursor.close()

if conn:

conn.close()

结果如下

2 字符串拼接

#!/usr/bin/poython3.6

#conding:utf-8

from pymysql.connections import Connection

from pymysql.cursors import DictCursor

conn=None

cursor=None

try:

conn=Connection('192.168.1.200','test','Admin@Root123','test')

print (conn.ping(False))

# 获取游标

name="'test'"

passwd="'test' or 1=1"

cursor=conn.cursor()

sql="select * from t where username={} and password ={}".format(name,passwd)

print (sql)

line=cursor.execute(sql)

print (cursor.fetchall()) # 获取一个

finally:

if cursor:

cursor.close()

if conn:

conn.close()

结果如下

3 解决SQL 注入方式

1 概念

参数化查询，可以有效防止SQL注入，并提高查询效率

cursor.execute(query,args=None)

args, 必须是元祖，列表或者字典，如果查询字符串使用%(name)s，就必须使用字典

2 查询效率高原因

参数化查询为什么能提高效率

原因就是SQL语句缓存

数据库服务器一般都会对SQL语句编译和缓存，编译只是对SQL部分，所以参数化就算有SQL指令也不会被执行

编译过程，需要词法分析，语法分析，生成AST，优化，生成执行计划等过程，比较耗资源，服务端会先查询是否对同一条语句进行缓存，如果缓存未失效，则不需要再次编译，从而降低了编译的成本，减低了内存消耗。

可以认为SQL语句字符串就是一个key，如果使用拼接方案，每次发过去的SQL语句都不一样，都需要重新编译并缓存。

大量查询的时候，首选使用参数化查询，以节省资源。

开发时，应该使用参数化查询

注意:这里说的是查询字符串的缓存，不是查询结果的缓存>

慢查询一般用不上缓存。

3 具体代码

字典参数化查询

#!/usr/bin/poython3.6

#conding:utf-8

from pymysql.connections import Connection

from pymysql.cursors import DictCursor

conn=None

cursor=None

try:

conn=Connection('192.168.1.200','test','Admin@Root123','test')

d={'name':"'test'",'passwd':"'test' or 1=1"}

cursor=conn.cursor()

sql="select * from t where username=%(name)s and password=%(passwd)s"

line=cursor.execute(sql,d)

print (cursor.fetchall()) # 获取一个

finally:

if cursor:

cursor.close()

if conn:

conn.close()

结果如下

#!/usr/bin/poython3.6

#conding:utf-8

from pymysql.connections import Connection

from pymysql.cursors import DictCursor

conn=None

cursor=None

try:

conn=Connection('192.168.1.200','test','Admin@Root123','test')

d={'id': '1 or 1=1'}

cursor=conn.cursor()

sql="select * from t where id=%(id)s"

line=cursor.execute(sql,d)

print (cursor.fetchall()) # 获取一个

finally:

if cursor:

cursor.close()

if conn:

conn.close()

元祖处理

#!/usr/bin/poython3.6

#conding:utf-8

from pymysql.connections import Connection

from pymysql.cursors import DictCursor

conn=None

cursor=None

try:

conn=Connection('192.168.1.200','test','Admin@Root123','test')

cursor=conn.cursor()

L1=[ (i,'admin','admin') for i in range(20,23)]

for x in L1:

sql="insert into t(id,username,password) values(%s,%s,%s)"

line=cursor.execute(sql,x)

print (line)

conn.commit()

finally:

if cursor:

cursor.close()

if conn:

conn.close()

结果如下

列表如下

#!/usr/bin/poython3.6

#conding:utf-8

from pymysql.connections import Connection

from pymysql.cursors import DictCursor

conn=None

cursor=None

try:

conn=Connection('192.168.1.200','test','Admin@Root123','test')

cursor=conn.cursor()

L1=[ [i,'admin','admin'] for i in range(24,26)]

for x in L1:

sql="insert into t(id,username,password) values(%s,%s,%s)"

line=cursor.execute(sql,x)

conn.commit()

finally:

if cursor:

cursor.close()

if conn:

conn.close()

结果如下

8 上下文支持

1 查看连接和游标部分源码

1 连接上下文相关源码如下：

def __enter__(self):

"""Context manager that returns a Cursor"""

warnings.warn(

"Context manager API of Connection object is deprecated; Use conn.begin()",

DeprecationWarning)

return self.cursor()

def __exit__(self, exc, value, traceback):

"""On successful exit, commit. On exception, rollback"""

if exc:

self.rollback()

else:

self.commit()

有上述代码可得，链接在使用上下文时会自动返回cursor游标，并在链接关闭时会自动判断执行语句是否出错，若出错，则直接回滚，否则提交，但未定义相关的关闭链接的操作

2 游标相关源码

def __enter__(self):

return self

def __exit__(self, *exc_info):

del exc_info

self.close()

有上述可知，游标的上下文返回的是自己，关闭链接时游标会自动关闭链接

2 连接相关上下文代码如下

#!/usr/bin/poython3.6

#conding:utf-8

from pymysql.connections import Connection

from pymysql.cursors import DictCursor

conn=None

cursor=None

try:

conn=Connection('192.168.1.200','test','Admin@Root123','test')

with conn as cursor: #此处返回一个cursor链接

sql="select * from t"

cursor.execute(sql)

print (cursor.fetchmany(5))

finally:

if cursor: #上述的代码中未使用cursor的上下文，因此此处还是需要的

cursor.close()

if conn:

conn.close()

结果如下

3 游标相关代码如下

#!/usr/bin/poython3.6

#conding:utf-8

from pymysql.connections import Connection

from pymysql.cursors import DictCursor

conn=None

cursor=None

try:

conn=Connection('192.168.1.200','test','Admin@Root123','test')

with conn as cursor: #此处返回一个cursor链接

with cursor: #此处使用cursor的上下文，默认会关闭其游标

sql="select * from t"

cursor.execute(sql)

print (cursor.fetchmany(5))

finally:

if conn:

conn.close()

结果如下

连接不应该随随便便销毁，应该形成重复使用的习惯，应该是多个cursor共享一个连接 。

四 简单连接池的实现

1 概念

这里的连接池，指的是数据库的连接池

连接池，是一个容器，里面存放着已经连接到数据库的连接，如果需要连接，使用者可以直接从池中获取一个连接，使用完后归还即可

从而减少频繁的创建，销毁数据库连接的过程，提高了性能。

2 分析

一个连接池，应该是一个可以设置大小的容器，里面存放着数据库的连接。

使用者需要连接，从池中获取一个连接，用完则需要归还。

3 设计

面向对象的设计思路，构建一个连接池

构建时，传入连接的服务器相关参数(主机，端口，用户名，密码，库名称)，还有提供一个池的容量

考虑多线程的使用，使用者从池中get一个连接，用完后归还该连接即可。

4 基本代码实现

#!/usr/local/bin/python3.6

#coding:utf-8

from pymysql.connections import Connection

from threading import local

import queue

class ConnPool:

def __init__(self,size,*args,**kwargs):

self.__size=size

self.__pool=queue.Queue(size) # 此处用于保存连接

self.local=local() # 此处用于隔离线程间数据，用于对不同的conn进行区分，具体在线程基础一章所有介绍

for i in range(size): # 此处用于生成连接池，此处是创建连接

conn=Connection(*args,**kwargs) # 初始化连接

self.__pool.put(conn)

@property

def maxsize(self):

return self.__size

@property

def size(self):

return self.__pool.qsize()

def __getconn(self): # 此处用于返回conn连接

return self.__pool.get()

def __returnconn(self,conn:Connection): # 此处用于归还连接

if isinstance(conn,Connection): # 此处若是一个连接，则可用于返回

self.__pool.put(conn)

def __enter__(self): # 此处用于返回一个游标，当然可以返回一个连接

if getattr(self.local,'conn',None) is None: # 若不存在，则进行添加，若存在，则返回

self.local.conn=self.__getconn()

return self.local.conn.cursor()

def __exit__(self, exc_type, exc_val, exc_tb):

if exc_type:

self.local.conn.rollback()

else:

self.local.conn.commit()

self.__returnconn(self.local.conn)

self.local.conn=None # 此处若重置此链接，则不会导致cursor的不可用，因为cursor中保存着conn的相关信息，但若被删除，则会导致全部出错，del

# 语句不可轻易执行

pool=ConnPool(3,'192.168.1.120','root','666666','test')

with pool as cursor:

with cursor:

sql="select * from login"

cursor.execute(sql)

print (cursor.fetchall())

sql="SHOW PROCESSLIST;" #此处是SQL 本身自带的用于查看进程的命令

cursor.execute(sql)

for x in cursor:

print (x)

上述的cursor能够被遍历的原因是其中有iter方法，具体源码如下

def __iter__(self):

return iter(self.fetchone, None)

结果如下