mysql模块

pymysql

使用pymysql前，首先下载pymsql模块

pip install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple

快速使用pymysql

#1.导入模块
import pymysql

#2. 建立mysql连接，并创建游标
conn = pymysql.connect(
    host = "localhost",
    port = 3306,
    user = "root",
    password = "XXXXXX",
    database = "flask1106",
    charset = "utf8")
cursor = conn.cursor()

#3 执行sql语句，并返回结果
sql = "select * from userinfo where username = %s and password = %s"%(user,pwd,)
cursor.execute(sql)
result = cursor.fetchone()

#4.关闭游标，关闭连接，打印结果
cursor.close()
conn.close()

print(result)

注：sql语句可能出现sql注入问题，不建议使用sql语句拼接方式。

pymysql连接使用方法1

#pymysql连接方法1
#每次访问都要连接一次数据库，同时存在并发问题

CONN = pymysql.connect(host="127.0.0.1",
                       port=3306,
                       user = 'root',
                       password='XXXXXX',
                       db='flask_data')
def fetchall(sql):
    cursor = CONN.cursor()
    cursor.execute = (sql)
    result = cursor.fetchall()
    cursor.close()
    return result

def login():
    result = fetchall('select * from auth_permission')
    return result

pymysql 数据库连接池方式

import pymysql
from dbutils.pooled_db import PooledDB
from threading import Thread

POOL = PooledDB(
    creator = pymysql, #使用链接数据库模块
    maxconnections = 6, #连接也允许最大的连接数，0和None表示不限制连接数
    mincached = 2, #初始化时，链接池中至少创建链接，0表示不创建
    blocking = True, #连接池中如果没有可用连接后，是否阻塞等待。True 等待；False 不等待后报错
    ping = 0,  #检查mysql服务端是否可用，如0＝None=Never; 1=default=whatever it is requtested;2=when a cursor is created,4=when a query is executed 7=always
    host="127.0.0.1",
    port=3306,
    user = 'root',
    password='xxxx',
    db='flask_data',
    charset = 'utf8',
)

def task(num):
    conn = Pool.connect()
    cursor = conn.cursor()
    #cursor.execute("select * from qinjia")
    cursor.execute("select sleep(3)")
    re = cursor.fetchall()
    cursor.close()
    conn.close()

    print(num,"-->" re)

for i in range(10):
    t = Thread(target = task,args=(i,))
    t.start()

'''
1 创建PooledDB,同时配置数据库连接
2 从连接池里获取一个链接 conn = Pool.connection()
3 获取游标 cursor = conn.cursor()
4 执行原生sql cursor.execute(sql)
5 获取结果 result = cursor.fetchall()
6 关闭游标 cursor.close()
7 关闭连接，向连接池归还链接 cursor.close()
'''

pymysql连接使用方法3

#单臂方式连接

import pymysql
from dbutils.pooled_db import PooledDB

#Singleton pattern DBPools

class DBHelper(object):
    def __init__(self):
        self.pool = PooledDB(
                creator=pymysql,
                maxconnections=50,  # 连接池允许最大连接数，0和None表示不限制连接数
                mincached=2,  # 初始化时，链接池中至少创建空闲的链接，0表示不创建
                maxcached=3,  # 链接池最大闲置的链接，0和None表示不限制
                blocking=True,  # 链接池中如果沿江有可用链接后，是否阻塞等待，True等待；False 不等待报错
                setsession=[],  # 开始会话前执行的命令列表
                ping=0,
                host='127.0.0.1',
                port=3306,
                user='root',
                password='xxxxxxxx',
                database='xxxxxxx',
                charset='utf8',
            )

    def get_conn_cursor(self):
        conn = self.pool.connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        return conn,cursor

    def close_conn_cursor(self,*args):
        for item in args:
            item.clolse()

    def exec(self,sql,**kwargs):
        conn,cursor =self.get_conn_cursor()

        cursor.execute(sql,**kwargs)
        cursor.commit()

        self.close_conn_cursor(conn,cursor)


    def fetch_one(self,sql,**kwargs):
        conn,cursor=self.get_conn_cursor()

        cursor.execute(sql,kwargs)
        result = cursor.fetchone()

        self.close_conn_cursor(conn,cursor)
        return result

    def fetch_all(self,sql,**kwargs):
        conn,cursor=self.get_conn_cursor()

        cursor.execute(sql,kwargs)
        result = cursor.fetchall()

        self.close_conn_cursor(conn,cursor)
        return result

db = DBHelper()

pymysql操作练习1

conn = pymysql.connect(host='127.0.0.1',
                       port=3306,
                       user='root',
                       password='xxxxxxx',
                       db='xxxxxxx',
                       charset='utf8')

cursor = conn.cursor()

#插入数据
a = "insert into test_usertype (name) values ('游客')"

#删除数据
c = "delete from test_usertype where id >=5"

#修改数据
d = "update test_usertype set name = '游客中心' where id =4 "

cursor.execute(d)
conn.commit()



#查询
b ="select * from test_usertype"


cursor.execute(b)
data1 =cursor.fetchone()
data2 = cursor.fetchall()
print(data1)

cursor.close()
conn.close()

pymysql操作练习2

import pymysql

conn = pymysql.connect(host='127.0.0.1',
                       port=3306,
                       user='root',
                       password='XXXXXXX',
                       db='xxxxxxx,
                       charset='utf8')

cursor = conn.cursor()

def register():
    print("用户注册练习")
    print("++++++++++++++")
    username = input("请输入用户名:")
    password = input("请输入密码:")

    sql = "insert into t_user (fusername,fpassword) values ('{}','{}')" .format(username,password)

    cursor.execute(sql)
    conn.commit()

    cursor.close()
    conn.close()

    print("注册成功，用户名：{}，密码：{}".format(username,password))


def login():
    username = input("请输入用户名:")
    password = input("请输入密码:")

    #可能出现sql注入的安全问题
    sql1 = "select * from t_user where fusername = '{}' and fpassword ='{}'".format(username,password)
    #cursor.execute(sql1)


    cursor.execute("select * from t_user where fusername = %(username)s and fpassword = %(password)s",{"username":username,"password":password})
    result =cursor.fetchone()

    cursor.close()
    conn.close()

    if result:
        print("登陆成功")
    else:
        print("登陆失败")

#register()

login()

SQLAlchemy

使用前需要下载pymsql 和sqlalchemy 二个模块
pip install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple

pip install sqlalchemy -i https://pypi.tuna.tsinghua.edu.cn/simple

连接
from sqlalchmey imprt create_engine
enginge = mssql+pymssql://<username>:<password>@<freetds_name>/?charset=utf8,echo =True

• echo＝True 会显示每条执行的sql语句

声明映像
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

字段
from sqlalchemy import Column,Integer,String
id = Column(Integer,primary_key=True)

创建会话
from sqlalchemy.orm import sessionmake
Sessioin = sessionmaker(bind=engine)

session.commit() #刷新结果
session.rollback() #回滚

sqlalchemy的快速编写

sqlalchemy查询快速编写

#创建配置，并做一个查询
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine(
    "mysql+pymysql://root:XXXXX#@127.0.0.1:3306/flask_data1107?charset=utf8",echo=True,)
conn = engine.connect()


sql = sqlalchemy.text("select * from userinfo")
res_set = conn.execute(sql)
print(res_set)

for i in res_set:
    print(i)
conn.close()
engine.dispose()

通过sqlalchemy创建一个表

创建表并插入数据

import sqlalchemy

from apps.config.settings import engine,meta_data

person = sqlalchemy.Table(
    "person",meta_data,
    sqlalchemy.Column("fid",sqlalchemy.Integer,primary_key=True,autoincrement=True),
    sqlalchemy.Column("fpersoncode",sqlalchemy.String(100),unique=True,nullable=False),
    sqlalchemy.Column("fname",sqlalchemy.String(100),nullable=False),
    sqlalchemy.Column("fentrydate",sqlalchemy.Date,nullable=False),
    sqlalchemy.Column("fnote",sqlalchemy.String(255))
)
#创建数据库表
meta_data.create_all(engine)

#删除数据库表
meta_data.drop_all(engine)

#插入一条数据
person_insert = person.insert()
insert_v1 = person_insert.values(fpersoncode="001",fname="zzs",fentrydate="2020-01-01")

conn.execute(insert_v1)
conn.commit()
conn.close()

#插入多条数据，使用上下文管理，
with engine.connect() as conn:
    conn.execute(person_insert,[
        {"fpersoncode":"002","fname":"nemo","fentrydate":"2023-01-01","fnote":"123"},
        {"fpersoncode": "003", "fname": "v1", "fentrydate": "2023-01-02", "fnote": "111"},
        {"fpersoncode": "004", "fname": "v2", "fentrydate": "2023-01-03", "fnote": "11234"},
    ])
    conn.commit()

sqlalchemy创建表的另一种方式

创建表的另一种方式

import sqlalchemy

from apps.config.settings import engine,meta_data,conn,Base

person = sqlalchemy.Table(
    "person",meta_data,
    sqlalchemy.Column("fid",sqlalchemy.Integer,primary_key=True,autoincrement=True),
    sqlalchemy.Column("fpersoncode",sqlalchemy.String(100),unique=True,nullable=False),
    sqlalchemy.Column("fname",sqlalchemy.String(100),nullable=False),
    sqlalchemy.Column("fentrydate",sqlalchemy.Date,nullable=False),
    sqlalchemy.Column("fnote",sqlalchemy.String(255))
)

class Userinfo(Base):
    __tablename__ = "userinfo"
    fid = sqlalchemy.Column(sqlalchemy.Integer,primary_key=True)
    fuseranme = sqlalchemy.Column(sqlalchemy.String(100),nullable=False)
    fpassword = sqlalchemy.Column(sqlalchemy.String(100),nullable=False)
    fnote = sqlalchemy.Column(sqlalchemy.String(255))
#创建表
def init_db():
    meta_data.create_all(engine)

sqlalchemy查询数据

查询数据

import sqlalchemy

from apps.config.settings import engine,meta_data,conn

person = sqlalchemy.Table(
    "person",meta_data,
    sqlalchemy.Column("fid",sqlalchemy.Integer,primary_key=True,autoincrement=True),
    sqlalchemy.Column("fpersoncode",sqlalchemy.String(100),unique=True,nullable=False),
    sqlalchemy.Column("fname",sqlalchemy.String(100),nullable=False),
    sqlalchemy.Column("fentrydate",sqlalchemy.Date,nullable=False),
    sqlalchemy.Column("fnote",sqlalchemy.String(255))
)

query = person.select().where(person.c.fid>'5')
res_set = conn.execute(query)
result = res_set.fetchall()
print(result)

sqlalchemy添加数据

sqlalchemy配置

import pymysql
import sqlalchemy
from dbutils.pooled_db import PooledDB

from sqlalchemy import create_engine,MetaData
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine(
    "mysql+pymysql://root:Sunhu1018#@127.0.0.1:3306/flask_data1107?charset=utf8",echo=True,max_overflow=10)

conn = engine.connect()

meta_data = sqlalchemy.MetaData()

Base = declarative_base()

Session = sessionmaker(bind=engine)

session =Session()

添加数据

import sqlalchemy
from sqlalchemy import Column,Integer,String
from apps.config.settings import engine,meta_data,conn,Base,session

person = sqlalchemy.Table(
    "person",meta_data,
    sqlalchemy.Column("fid",sqlalchemy.Integer,primary_key=True,autoincrement=True),
    sqlalchemy.Column("fpersoncode",sqlalchemy.String(100),unique=True,nullable=False),
    sqlalchemy.Column("fname",sqlalchemy.String(100),nullable=False),
    sqlalchemy.Column("fentrydate",sqlalchemy.Date,nullable=False),
    sqlalchemy.Column("fnote",sqlalchemy.String(255))
)

class Userinfo(Base):
    __tablename__ = "userinfo"
    id = Column(Integer,name="fid",primary_key=True)
    username = Column(String(100),name="fusername",nullable=False)
    password = Column(String(100),name="fpassword",nullable=False)
    note = Column(String(255),name="fnote")

def init_db():
    meta_data.create_all(engine)


obj1 = Userinfo(username= "qq1",password= "!")
session.add(obj1)

session.commit()
session.close()