python连接使用mysql数据库

11 个月前 · 来自专栏数据分析

数小分

1、PyMySQL库、MySQLdb库

MySQLdb是支持python2版本到python3.4版本的用于连接MySQL服务器的一个库，可实现数据库的各种操作。pymysql是支持python3.5及以上版本的用于连接MySQL服务器的一个库；下载方式pip3 install PyMySQL。这两个库连接数据库本质上还是sql语句。

MySQLdb库使用C语言写的，运行速度快；而用python的PyMySQL库虽然安装简便，但是运行速度比不上MySQLdb。因为两者的用法完全一致，可用pymysql代替MySQLdb，即在python3环境中使用mysqldb的用法：

import pymysql
pymysql.install_as_MySQLdb()

#创建数据库连接对象

con=pymysql.connect(host="",user="",password="",port="",database="",charset="utf8") #charset指定正确的字符集编码

#通过数据库连接对象的cursor方法来构造游标对象，通过游标对象的execute方法或executemany方法来执行sql语句

cur=con.cursor()
#cursor.execute(query,args=none)，query表示查询的sql语句，党在查询sql中使用%s符号来做占位符时，必须在execute方法中通过args传递相应的实参。
sql="select id,name,price from goods where id=%s"
cur.execute(sql,(1,))
#cursor.executemany(query,args)，用来执行数据库的批量操作，args实参必须传递，且args中的每一项对应到数据库中的一条记录。
sql="insert into goods(name,price) values(%s,%s)"
cur.executemany(sql,[("湖南辣条",26),("东北大米",128)])
#这两个方法的返回值为数据库中受影响的行数，可通过返回值来判断是否操作成功
sql="select id,name,price from goods where id=%s"
rows=cur.execute(sql,(1,))
if rows>0:
    print("操作成功！")

#cursor用来接收返回值的方法有fetchall()接收全部结果、fetchone()返回一条结果、fetchmany(self,size=none)接收size条返回结果。但是这些方法是从数据库里取数据，取完就没有了，第二次执行该语句时就会返回空列表，不能重复查询，可以把数据取出来后放到变量里在进行处理。

result=cur.fetchall()
print(result)
#可通过pandas将数据结果放到dataframe里
#如果进行数据库的插入、更新和删除，需要通过数据库连接对象的commit方法提交当前所有事物操作
conn.commit()
#关闭光标对象和数据库连接
cursor.close()
con.close()

通过上面操作发现，pymysql更多的是实现数据库驱动连接，本身的方法用来进行数据操作，不太符合代码使用习惯（至少是本人的习惯）；我认可它地方只有两个，一个是实现数据库驱动连接，二是可以直接sql语句层面操作数据库，容易理解。

2、SQLAlchemy库

SQLAlchemy是基于python的ORM（模型关系映射）框架，框架是建立在DB-API基础上的，使用关系对象映射进行数据库操作；即将类和对象转换成sql，使用数据api执行sql并获取执行结果。

SQLAlchemy本身是无法操作数据库的，必须依赖python的数据库接口规范DB-API；Dialect用于和数据api进行交互，根据配置的不同调用不同数据库API，从而实现数据库的操作。

SQLAlchemy是在python代码层面来操作数据库，而pymysql本质是sql语言操作数据库，这是SQLAlchemy与pymysql的本质区别。

下载方式pip3 install sqlalchemy。

sqlalchemy包括框架引擎engine、connection pooling数据库链接池、数据DBAPI种类Dialect、数据库架构类型schema/types和sql表达式语言sql exprression language这5个部分。

import pymysql
from sqlalchemy import create_engine

（1）创建连接引擎对象

engine=sqlalchemy.create_engine('mysql+pymysql://frogdata05:Frogdata!1321@localhost:3306/froghd')

#engine=create_engine("dialect+driver://usrname:password@host:port/database",echo=False,pool_size=100,pool_recycle=3600,pool_pre_ping=True)，创建连接引擎对象，会根据指定数据库进行sql api的调用处理

①dialent，数据库类型，如MySQL

②driver，数据库连接驱动，没有+driver时即默认MySQLdb，选哪种驱动，就装哪个包

③username，数据库用户名，本地一般为root

④paddword，用户密码

⑤host，服务器地址，本地则为localhost

⑥port，端口，本地为3306

⑦database，要使用的数据库名

⑧echo，true时会打印sql语句

⑨pool_size，连接池的大小，默认5个，0表示连接数无限制

pool_recycle，mysql默认一个连接8小时内无任何动作就会自动断开链接并返回错误，设置该参数后可指定时间内回收链接，如3600表示1小时后链接自动回收

pool_pre_ping，true时每次从链接池拿链接的时候都会向数据库发送测试查询语句判断服务器是否正常运行，连接出现disconnect时连接连同pool中其他连接都会被收回。

（2）创建连接会话对象

from sqlalchemy.orm import sessionmaker,scoped_session
#创建数据库连接引擎对象后，需要获取和指定数据库之间的连接，通过连接进行数据库数据的增删查改。
session=sessionmaker(bind=engine)
se=session()
#一般session对象是全局的，多线程情况多个线程共享一个session，数据处理就会发生错误，为保证线程安全，就要使用scoped_session方法
session=scoped_session(sessionmaker(bind=engine))
se=session()

（3）ORM的Object操作

程序中的对象要使用sqlalchemy的管理，实现对象的orm操作，就需要按照框架指定方式进行类型的创建，sqlalchemy封装了基础类的声明操作和字段属性定义限制方式。基础类封装在sqlalchemy.ext.declarative.declarative_base模块中，如sqlalchemy.Column定义属性。

from sqlalchemy.ext.declarative import declarative_base
#创建基础类
base=declarative_base()
#创建模型类，要继承基础类base，通过指定__tablename__确定和数据库中某个数据表之间的关联，指定某列类型为设定的主键，其他就是通过Column指定自定义属性。构建出自己的accessors访问器对象，就可以进行数据库中数据的操作了。
from sqlalchemy import Column,String,Integer,ForeignKey
class User_login(base):
    #定义表名
    __tablename__="user_login"
    #创建字段类型
    id=Column(Integer,primary_key=True)
    name=Column(String(50))
    age=Column(Integer)

（4）模型类映射操作

型类定义完成后，declarative就会通过python的metaclas对模型类进行操作，根据定义的模型类创建table对象，构建程序中类型和数据库table对象间的映射关系。通过类型对象的metadata可实现和数据库间的交互。

#删除所有模型类创建的表格
base.metadata.drop_all(engine)
#创建所有模型类的表格，会自动检查数据库中是否有需要创建的表，有则不创建
base.metadata.create_all(engine)

（5）对数据库中的数据进行增删查改操作

session提供了query对象实现数据库中数据的查询操作，返回结果为对象列表，若结尾加上all()可显示全部查询结果，first()则显示第一行结果，one()查询到一个结果，one_or_none()查询一个结果若查不出则返回none，scalar()与one_or_noe()效果一样，get()根据主键返回查询结果。

#(1)降序查询
se.query(User_login).order_by(-user.id).all()
#(2)查询指定列,并取别名
se.query(User_login.id,User_login.user_id.label("userid"))
#(3)limit()限制返回条数+offset()偏倚，实现切片分页,如返回第二条以后的三条数据
se.query(User_login).limit(3).offset(3).all()
#(4)slice()和列表切片也可实现切片分页
se.query(User_login).slice(2,5).all()
se.query(User_login).all()[2:5]
#(5)分组查询
se.query(User_login).group_by(User_login.id)
#(6)查询常用聚合函数，max,min,sum,avg,count
from sqlalchemy.sql import func
se.query(func.max(User_login.user_id),func.count(User_login.user_id))
#(7)去重查询
from sqlalchemy import distinct
se.query(distinct(User_login.user_id))
#(8)条件筛选filter，==，！=，%%，in/not_in，is_，isnot()，or_，like，notlike
se.query(User_login).filter(Use_login.user_id=="1")
se.query(User_login).filter(User_login.user_id.notlike("%1%"))
se.query(User_login).filter(~User_login.user_id.in_([1,2,3]))
se.query(User_login).filter(User_login.user_id.is_(None))
from sqlalchemy import and_,or_
se.query(User_login).filter(and_(User_login.user_id=="1",User_login.client_id=="10"))
se.query(User_login).filter(or_(User_login.user_id=="1",User_login.client_id=="10"))
#(9)多表查询
#不使用join的话，可以直接联表查询
se.query(User_login1.user_id,User_login2.cilent_id).filter(User_login1.user_id==User_login2.user_id).all()
#内连接用join完成，如果不写join的条件，则默认使用外键作为条件连接，否则就是正常的条件连接。查询结果如何跟join后面的内容无关，而是取决于query方法中传了什么参数
re.query(user).join(article).group_by(user_id).order_by(func.count(article.id).desc()).all()
re.query(user.name,func.count(article.id)).join(article,user.id==article.uid).group_by(user.id).order_by(func.count(article.id).desc()).all()
#左联查询outerjoin,没有右联查询