Python’s standard database interface is Python DB-API, which provides developers with a database application programming interface. Python provides a wide range of database interfaces to meet a variety of database selection needs. You can choose a suitable database according to the needs of the project
GadFly
mSQL
MySQL
2.1.1 Install PyMySQL
PyMySQL is a The MySQL driver written in Python allows us to operate the MySQL database using Python language.pip install PyMySQL
#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13
import pymysql
# 创建连接
conn = pymysql.connect(host="127.0.0.1", port=3306, user='zff', passwd='zff123', db='zff', charset='utf8mb4')
# 创建游标(查询数据返回为元组格式)
# cursor = conn.cursor()
# 创建游标(查询数据返回为字典格式)
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 1. 执行SQL,返回受影响的行数
effect_row1 = cursor.execute("select * from USER")
# 2. 执行SQL,返回受影响的行数,一次插入多行数据
effect_row2 = cursor.executemany("insert into USER (NAME) values(%s)", [("jack"), ("boom"), ("lucy")])# 3
# 查询所有数据,返回数据为元组格式
result = cursor.fetchall()
# 增/删/改均需要进行commit提交,进行保存
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
print(result)
"""
[{'id': 6, 'name': 'boom'}, {'id': 5, 'name': 'jack'}, {'id': 7, 'name': 'lucy'}, {'id': 4, 'name': 'tome'}, {'id': 3, 'name': 'zff'}, {'id': 1, 'name': 'zhaofengfeng'}, {'id': 2, 'name': 'zhaofengfeng02'}]
"""#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13
import pymysql
# 创建连接
conn = pymysql.connect(host="127.0.0.1", port=3306, user='zff', passwd='zff123', db='zff', charset='utf8mb4')
# 创建游标(查询数据返回为元组格式)
cursor = conn.cursor()
# 获取新创建数据自增ID
effect_row = cursor.executemany("insert into USER (NAME)values(%s)", [("eric")])
# 增删改均需要进行commit提交
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
new_id = cursor.lastrowid
print(new_id)
"""
8
"""#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13
import pymysql
# 创建连接
conn = pymysql.connect(host="127.0.0.1", port=3306, user='zff', passwd='zff123', db='zff', charset='utf8mb4')
# 创建游标
cursor = conn.cursor()
cursor.execute("select * from USER")
# 获取第一行数据
row_1 = cursor.fetchone()
# 获取前n行数据
row_2 = cursor.fetchmany(3)
#
# # 获取所有数据
row_3 = cursor.fetchall()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
print(row_1)
print(row_2)
print(row_3)#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13
import pymysql
# 创建连接
conn = pymysql.connect(host="127.0.0.1", port=3306, user='zff', passwd='zff123', db='zff', charset='utf8mb4')
# 创建游标
cursor = conn.cursor()
# 存在sql注入情况(不要用格式化字符串的方式拼接SQL)
sql = "insert into USER (NAME) values('%s')" % ('zhangsan',)
effect_row = cursor.execute(sql)
# 正确方式一
# execute函数接受一个元组/列表作为SQL参数,元素个数只能有1个
sql = "insert into USER (NAME) values(%s)"
effect_row1 = cursor.execute(sql, ['wang6'])
effect_row2 = cursor.execute(sql, ('wang7',))
# 正确方式二
sql = "insert into USER (NAME) values(%(name)s)"
effect_row1 = cursor.execute(sql, {'name': 'wudalang'})
# 写入插入多行数据
effect_row2 = cursor.executemany("insert into USER (NAME) values(%s)", [('ermazi'), ('dianxiaoer')])
# 提交
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()#! /usr/bin/env python # -*- coding: utf-8 -*- # __author__ = "shuke" # Date: 2018/5/13 from DBUtils.PersistentDB import PersistentDB import pymysql POOL = PersistentDB( creator=pymysql,# 使用链接数据库的模块 maxusage=None,# 一个链接最多被重复使用的次数，None表示无限制 setsession=[],# 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."] ping=0, # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always closeable=False, # 如果为False时， conn.close() 实际上被忽略，供下次使用，再线程关闭时，才会自动关闭链接。如果为True时， conn.close()则关闭链接，那么再次调用pool.connection时就会报错，因为已经真的关闭了连接（pool.steady_connection()可以获取一个新的链接） threadlocal=None,# 本线程独享值得对象，用于保存链接对象，如果链接对象被重置 host='127.0.0.1', port=3306, user='zff', password='zff123', database='zff', charset='utf8', ) def func(): conn = POOL.connection(shareable=False) cursor = conn.cursor() cursor.execute('select * from USER') result = cursor.fetchall() cursor.close() conn.close() return result result = func() print(result)
#! /usr/bin/env python # -*- coding: utf-8 -*- # __author__ = "shuke" # Date: 2018/5/13 import time import pymysql import threading from DBUtils.PooledDB import PooledDB, SharedDBConnection POOL = PooledDB( creator=pymysql,# 使用链接数据库的模块 maxconnections=6,# 连接池允许的最大连接数，0和None表示不限制连接数 mincached=2,# 初始化时，链接池中至少创建的空闲的链接，0表示不创建 maxcached=5,# 链接池中最多闲置的链接，0和None不限制 maxshared=3, # 链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。 blocking=True,# 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错 maxusage=None,# 一个链接最多被重复使用的次数，None表示无限制 setsession=[],# 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."] ping=0, # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always host='127.0.0.1', port=3306, user='zff', password='zff123', database='zff', charset='utf8' ) def func(): # 检测当前正在运行连接数的是否小于最大链接数，如果不小于则：等待或报raise TooManyConnections异常 # 否则 # 则优先去初始化时创建的链接中获取链接 SteadyDBConnection。 # 然后将SteadyDBConnection对象封装到PooledDedicatedDBConnection中并返回。 # 如果最开始创建的链接没有链接，则去创建一个SteadyDBConnection对象，再封装到PooledDedicatedDBConnection中并返回。 # 一旦关闭链接后，连接就返回到连接池让后续线程继续使用。 conn = POOL.connection() # print('连接被拿走了', conn._con) # print('池子里目前有', POOL._idle_cache, 'rn') cursor = conn.cursor() cursor.execute('select * from USER') result = cursor.fetchall() conn.close() return result result = func() print(result)
#! /usr/bin/env python # -*- coding: utf-8 -*- # __author__ = "shuke" # Date: 2018/5/13 import pymysql import threading from threading import RLock LOCK = RLock() CONN = pymysql.connect(host='127.0.0.1', port=3306, user='zff', password='zff123', database='zff', charset='utf8') def task(arg): with LOCK: cursor = CONN.cursor() cursor.execute('select * from USER ') result = cursor.fetchall() cursor.close() print(result) for i in range(10): t = threading.Thread(target=task, args=(i,)) t.start()
#! /usr/bin/env python # -*- coding: utf-8 -*- # __author__ = "shuke" # Date: 2018/5/13 import pymysql import threading CONN = pymysql.connect(host='127.0.0.1', port=3306, user='zff', password='zff123', database='zff', charset='utf8') def task(arg): cursor = CONN.cursor() cursor.execute('select * from USER ') # cursor.execute('select sleep(10)') result = cursor.fetchall() cursor.close() print(result) for i in range(10): t = threading.Thread(target=task, args=(i,)) t.start()
# cat sql_helper.py import pymysql import threading from DBUtils.PooledDB import PooledDB, SharedDBConnection POOL = PooledDB( creator=pymysql,# 使用链接数据库的模块 maxconnections=20,# 连接池允许的最大连接数，0和None表示不限制连接数 mincached=2,# 初始化时，链接池中至少创建的空闲的链接，0表示不创建 maxcached=5,# 链接池中最多闲置的链接，0和None不限制 #maxshared=3,# 链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。 blocking=True,# 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错 maxusage=None,# 一个链接最多被重复使用的次数，None表示无限制 setsession=[],# 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."] ping=0, # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always host='192.168.11.38', port=3306, user='root', passwd='apNXgF6RDitFtDQx', db='m2day03db', charset='utf8' ) def connect(): # 创建连接 # conn = pymysql.connect(host='192.168.11.38', port=3306, user='root', passwd='apNXgF6RDitFtDQx', db='m2day03db') conn = POOL.connection() # 创建游标 cursor = conn.cursor(pymysql.cursors.DictCursor) return conn,cursor def close(conn,cursor): # 关闭游标 cursor.close() # 关闭连接 conn.close() def fetch_one(sql,args): conn,cursor = connect() # 执行SQL，并返回收影响行数 effect_row = cursor.execute(sql,args) result = cursor.fetchone() close(conn,cursor) return result def fetch_all(sql,args): conn, cursor = connect() # 执行SQL，并返回收影响行数 cursor.execute(sql,args) result = cursor.fetchall() close(conn, cursor) return result def insert(sql,args): """ 创建数据 :param sql: 含有占位符的SQL :return: """ conn, cursor = connect() # 执行SQL，并返回收影响行数 effect_row = cursor.execute(sql,args) conn.commit() close(conn, cursor) def delete(sql,args): """ 创建数据 :param sql: 含有占位符的SQL :return: """ conn, cursor = connect() # 执行SQL，并返回收影响行数 effect_row = cursor.execute(sql,args) conn.commit() close(conn, cursor) return effect_row def update(sql,args): conn, cursor = connect() # 执行SQL，并返回收影响行数 effect_row = cursor.execute(sql, args) conn.commit() close(conn, cursor) return effect_row
The above is the detailed content of How to operate MySQL with Python. For more information, please follow other related articles on the PHP Chinese website!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
