python链接mysql方法简介

作者：铁头同学
转载链接：https://blog.youkuaiyun.com/zeroooorez/article/details/99689445

1.python使用pymysql连接数据库

import pymysql
user = input('请输入用户名：')
pwd = input('请输入密码：')

# 1.连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', db='db', charset='utf8')

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

#注意%s需要加引号
sql = "select * from userinfo where username='%s' and pwd='%s'" %(user, pwd)
print(sql)

# 3.执行sql语句
cursor.execute(sql)

result=cursor.execute(sql) #执行sql语句，返回sql查询成功的记录数目
print(result)

# 关闭连接，游标和连接都要关闭
cursor.close()
conn.close()

a) sql注入原因：sql的注释（-- ）

sql = "select * from userinfo where username='%s' and pwd='%s'" %(user, pwd)
#1、sql注入之：用户存在，绕过密码
mjj' -- 任意字符

#2、sql注入之：用户不存在，绕过用户与密码
xxx' or 1=1 -- 任意字符

b) 解决方式，避免自己拼接字符串：

sql="select * from userinfo where name=%s and password=%s"  #！！！注意%s需要去掉引号
result=cursor.execute(sql,[user,pwd])
#如果args是列表或元组或字典，则%s可以用作查询中的占位符.

c) 增删改查

注：增删改需要提交，执行commit()

import pymysql
username = input('请输入用户名：')
pwd = input('请输入密码：')

# 1.连接
conn = pymysql.connect(host='localhost', port=3306, user='root', password='', db='db', charset='utf8')

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

# 操作
# 增
# sql = "insert into userinfo(username,pwd) values (%s,%s)"

# effect_row = cursor.execute(sql,(username,pwd))
#同时插入多条数据
#cursor.executemany(sql,[('李四'，'110'),（'王五'，'119'）]) 

# print(effect_row)#

# 改
# sql = "update userinfo set username = %s  where id = 2"
# effect_row = cursor.execute(sql,username)
# print(effect_row)

# 删
sql = "delete from userinfo  where id = 2"
effect_row = cursor.execute(sql)
print(effect_row)

#一定记得commit
conn.commit()

# 4.关闭游标
cursor.close()

# 5.关闭连接
conn.close()

# 查：
fetchone():获取下一行数据，第一次为首行；
fetchall():获取所有行数据源
fetchmany(4):获取4行数据

#默认返回值是元组：((1, 'mjj', '123'), (3, '张三', '110'), (4, '李四', '119'))
#设置返回置为字典：cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) 

#指针移动方法：
cursor.scroll(1,mode='relative')  # 相对当前位置移动
cursor.scroll(2,mode='absolute') # 相对绝对位置移动，表头
#第一个值为移动的行数，整数为向下移动，负数为向上移动，mode指定了是相对当前位置移动，还是相对于首行移动

2.ORM框架：SQLAchemy

a) 链接数据库

from sqlalchemy import create_engine
engine = engine = create_engine("mysql+pymysql://root:123456@localhost:3306/tests?charset=utf8",encoding="utf-8", echo=True)

b) 创建表

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index,CHAR,VARCHAR
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

Base = declarative_base()

# 创建单表
class UserType(Base):
    __tablename__ = 'usertype'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(VARCHAR(32), nullable=True, index=True)

class Users(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(VARCHAR(32), nullable=True, index=True)
    email = Column(VARCHAR(16), unique=True)
    # user_type_id = Column(Integer,ForeignKey("usertype.id"))

    # user_type = relationship("UserType",backref='xxoo')
    # __table_args__ = (
    #     UniqueConstraint('id', 'name', name='uix_id_name'),
    #     Index('ix_n_ex','name', 'email',),
    # )


def create_db():
    engine = create_engine("mysql+pymysql://root:123456@localhost:3306/db4?charset=utf8", max_overflow=5)
    Base.metadata.create_all(engine)

def drop_db():
    engine = create_engine("mysql+pymysql://root:123456@localhost:3306/db4?charset=utf8", max_overflow=5)
    Base.metadata.drop_all(engine)

create_db() #创建
# drop_db()  #删除

c) 修改数据（增删改）

engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/db4?charset=utf8", max_overflow=5)
Session = sessionmaker(bind=engine)
session = Session()

# 类 -> 表
# 对象 -> 行
# ###### 增加 ######
#增加一条数据
# obj1 = UserType(title='普通用户')
# session.add(obj1)

#增加多条数据
# objs =[
#   UserType(title='超级用户'),
#   UserType(title='白金用户'),
#   UserType(title='黑金用户'),
# ]
# session.add_all(objs)

#删
session.query(Users).filter(Users.id > 2).delete()
session.commit()

#改
session.query(Users).filter(Users.id > 2).update({"name" : "099"})
session.query(Users).filter(Users.id > 2).update({Users.name: Users.name + "099"}, synchronize_session=False)
session.query(Users).filter(Users.id > 2).update({"num": Users.num + 1}, synchronize_session="evaluate")
#synchronize_session用于query在进行delete或者update操作的时候，对session的同步策略 默认值为evaluate
session.commit()

d)查

# ###### 查 ######
# print(session.query(UserType))
# user_type_list = session.query(UserType).all()
# for row in user_type_list:
#     print(row.id,row.title)

# select xxx  UserType where
# user_type_list = session.query(UserType.id,UserType.title).filter(UserType.id > 2)
# for row in user_type_list:
#     print(row.id,row.title)

e)其他操作

#　条件
ret = session.query(Users).filter_by(name='alex').all()
ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()
ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all()
ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()
ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()     //非
ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all()
from sqlalchemy import and_, or_
ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()
ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()
ret = session.query(Users).filter(
    or_(
        Users.id < 2,
        and_(Users.name == 'eric', Users.id > 3),
        Users.extra != ""
    )).all()


# 通配符
ret = session.query(Users).filter(Users.name.like('e%')).all()
ret = session.query(Users).filter(~Users.name.like('e%')).all()

# 限制
ret = session.query(Users)[1:2]

# 排序
ret = session.query(Users).order_by(Users.name.desc()).all()
ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()

# 分组
from sqlalchemy.sql import func

ret = session.query(Users).group_by(Users.extra).all()
ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).all()

ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()

# 连表

ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()

ret = session.query(Person).join(Favor).all()

ret = session.query(Person).join(Favor, isouter=True).all()


# 组合
q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union(q2).all()

q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union_all(q2).all()


# 分组，排序，连表，通配符，子查询，limit，union,where,原生SQL、
# ret = session.query(Users, UserType)
# select * from user,usertype;
#
# ret = session.query(Users, UserType).filter(Users.usertype_id==UserType.id)
# select * from user,usertype whre user.usertype_id = usertype.id

# result = session.query(Users).join(UserType)
# print(result)

# result = session.query(Users).join(UserType,isouter=True)
# print(result)

# 1.
# select * from b where id in (select id from tb2)

# 2 select * from (select * from tb) as B
# q1 = session.query(UserType).filter(UserType.id > 0).subquery()
# result = session.query(q1).all()
# print(result)

# 3
# select
#   id ,
#   (select * from users where users.user_type_id=usertype.id)
# from usertype;

# session.query(UserType,session.query(Users).filter(Users.id == 1).subquery())
# session.query(UserType,Users)
# result = session.query(UserType.id,session.query(Users).as_scalar())
# print(result)
# result = session.query(UserType.id,session.query(Users).filter(Users.user_type_id==UserType.id).as_scalar())
# print(result)

# 问题1. 获取用户信息以及与其关联的用户类型名称(FK,Relationship=>正向操作)
# user_list = session.query(Users,UserType).join(UserType,isouter=True)
# print(user_list)
# for row in user_list:
#     print(row[0].id,row[0].name,row[0].email,row[0].user_type_id,row[1].title)

# user_list = session.query(Users.name,UserType.title).join(UserType,isouter=True).all()
# for row in user_list:
#     print(row[0],row[1],row.name,row.title)


# user_list = session.query(Users)
# for row in user_list:
#     print(row.name,row.id,row.user_type.title)


# 问题2. 获取用户类型
# type_list = session.query(UserType)
# for row in type_list:
#     print(row.id,row.title,session.query(Users).filter(Users.user_type_id == row.id).all())

# type_list = session.query(UserType)
# for row in type_list:
#     print(row.id,row.title,row.xxoo)


# ###### 删除 ######
# session.query(UserType.id,UserType.title).filter(UserType.id > 2).delete()

# ###### 修改 ######
# session.query(UserType.id,UserType.title).filter(UserType.id > 0).update({"title" : "黑金"})
# session.query(UserType.id,UserType.title).filter(UserType.id > 0).update({UserType.title: UserType.title + "x"}, synchronize_session=False)
# session.query(UserType.id,UserType.title).filter(UserType.id > 0).update({"num": Users.num + 1}, synchronize_session="evaluate")


session.commit()
session.close()

注：设置外检的另一种方式 ForeignKeyConstraint(['other_id'], ['othertable.other_id'])

如果sqlalchemy在执行过程中报以下错误：

说明你的事务会话没有及时关闭

错误A：

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2006, "MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))")

错误B：

sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back

解决方案：不要让您的连接长时间打开，执行完事务最好关闭会话。SQLAlchemy文档还共享相同的解决方案：session basics

session = Session()
try:
    # this is where the "work" happens!
    yield session
    # always commit changes!
    session.commit()
except:
    # if any kind of exception occurs, rollback transaction
    session.rollback()
    raise
finally:
    session.close() #执行完必须关闭session

多线程控制数据库连接数：

用python写数据库连接时要用到数据连接池于是就想到了DBUtils ，这个简单pip install DBUtile
有时候可能会出错：

 from DBUtils.PooledDB import PooledDB
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ImportError: No module named DBUtils.PooledDB

官网查阅：
已经安装了呀
版本2.0可能是版本导致的
https://pypi.org/project/DBUtils/
https://webwareforpython.github.io/DBUtils/main.html
果然2.0的写法是：

from dbutils.pooled_db import PooledDB

1.3的写法是：

from DBUtils.PooledDB import PooledDB

以下是多线程结合连接池的实例：

db_pool = PooledDB(
    creator=pymysql,
    maxconnections=10,  # 连接池允许的最大连接数，0和None表示不限制连接数
    mincached=0,  # 初始化时，连接池中至少创建的空闲的链接，0表示不创建
    maxcached=0,  # 连接池中最多闲置的连接，0和None不限制
    maxshared=0, # 连接池中最多共享的连接数量，0和None表示全部共享。
    blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True
    maxusage=None,  # 一个连接最多被重复使用的次数，None表示无限制
    **mysql_config # 数据库账户等信息
)

sql='SELECT user FROM user limit 1'

# connect_data(sql)

def connect_data(sql, mode='c', select=False):
    '''
    m:多线程模式，c:连接池模式
    '''
    if mode == 'm':
        conn=pymysql.connect(**db_info)
    else:
        conn = db_pool.connection()
    data = None
    if select:
        try:
            with conn.cursor() as cur:
                cur.execute(sql)
                results= cur.fetchall()
                fields = [field[0] for field in cur.description]
                # 序列化 成字典 zip  把两个可迭代对象合并成2维元组。然后用dict 转化为字典。
                data = [dict(zip(fields, result)) for result in results]
        except pymysql.MySQLError as err:
            conn.rollback()
            print(err)
        finally:
            # PooledDB连接池关闭方法其实不是真的把该连接关闭，而是将该连接由放入池的队列里，在后文会看到该逻辑的实现
            conn.close()
    else:
        try:
            with conn.cursor() as cur:
                resl=cur.execute(sql)
                conn.commit()
        except pymysql.MySQLError as err:
            conn.rollback()
        finally:
            # PooledDB连接池关闭方法其实不是真的把该连接关闭，而是将该连接由放入池的队列里，在后文会看到该逻辑的实现
            conn.close()
    print(data)
    return data

def multi_insert(mode,nums=30):
    treads=[]
    for i in range(nums):
        t=threading.Thread(target=connect_data,args=(sql,mode,True))
        treads.append(t)
    for t in treads:
        t.start()
    for t in treads:
        t.join()

def run(mode,request_nums):
    start=time.time()
    multi_insert(mode,request_nums)
    end=time.time()
    cost=end-start
    print('cost:{0:.3} s'.format(cost))


if __name__=='__main__':
    run('c',100)