MySQLdb操作数据库--sql_alchemy(ORM)之基本增删改查以及常用的操作

SQLAlchemy是一个ORM框架。

作用： 帮助我们使用类和对象快速实现数据库操作。

一、MySQLdb操作数据库

MySQLdb操作数据库与pymysql操作数据库用法是一样的。但是MySQLdb只支持python2。

1.1、插入数据

import MySQLdb
  
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')
  
cur = conn.cursor()
  
reCount = cur.execute('insert into UserInfo(Name,Address) values(%s,%s)',('alex','usa'))
# reCount = cur.execute('insert into UserInfo(Name,Address) values(%(id)s, %(name)s)',{'id':12345,'name':'wupeiqi'})
  
conn.commit()
  
cur.close()
conn.close()
  
print reCount

批量插入数据

import MySQLdb

conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')

cur = conn.cursor()

li =[
     ('alex','usa'),
     ('sb','usa'),
]
reCount = cur.executemany('insert into UserInfo(Name,Address) values(%s,%s)',li)

conn.commit()
cur.close()
conn.close()

print reCount

1.2、删除数据

import MySQLdb
 
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')
 
cur = conn.cursor()
 
reCount = cur.execute('delete from UserInfo')
 
conn.commit()
 
cur.close()
conn.close()
print reCount

1.3、修改数据

import MySQLdb
 
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')
 
cur = conn.cursor()
 
reCount = cur.execute('update UserInfo set Name = %s',('alin',))
 
conn.commit()
cur.close()
conn.close()
print reCount

1.4、查数据

# ############################## fetchone/fetchmany(num)  ##############################
 
import MySQLdb
 
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')
cur = conn.cursor()
 
reCount = cur.execute('select * from UserInfo')
 
print cur.fetchone()
print cur.fetchone()
cur.scroll(-1,mode='relative')
print cur.fetchone()
print cur.fetchone()
cur.scroll(0,mode='absolute')
print cur.fetchone()
print cur.fetchone()
 
cur.close()
conn.close()
 
print reCount
 
 
 
# ############################## fetchall  ##############################
 
import MySQLdb
 
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')
#cur = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
cur = conn.cursor()
 
reCount = cur.execute('select Name,Address from UserInfo')
 
nRet = cur.fetchall()
 
cur.close()
conn.close()
 
print reCount
print nRet
for i in nRet:
    print i[0],i[1]

二、SQLAlchemy的使用

安装：pip3 install sqlalchemy

2.1、单表基本的增删改查

models.py：数据库需要自己创建

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import datetime
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index

Base = declarative_base()

# 创建单表
class Users(Base):
    # 表名
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=False)
    # email = Column(String(32), unique=True)
    # ctime = Column(DateTime, default=datetime.datetime.now)
    # extra = Column(Text, nullable=True)

    __table_args__ = (
        # UniqueConstraint('id', 'name', name='uix_id_name'),
        # Index('ix_id_name', 'name', 'email'),
    )


def init_db():
    """
    根据类创建数据库表
    :return: 
    """
    engine = create_engine(
        "mysql+pymysql://用户名:密码@127.0.0.1:3306/库名?charset=utf8",
        max_overflow=0,  # 超过连接池大小外最多创建的连接
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置），-1表示不回收
    )

    Base.metadata.create_all(engine)


def drop_db():
    """
    根据类删除数据库表
    :return: 
    """
    engine = create_engine(
        "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8",
        max_overflow=0,  # 超过连接池大小外最多创建的连接
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
    )

    Base.metadata.drop_all(engine)


if __name__ == '__main__':
    # drop_db()
    init_db()  # 右键运行即可创建

单表的基本增删改查

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

from SQLAlchemy.models import Users

# 先创建连接
engine = create_engine(
        "mysql+pymysql://root:123456@127.0.0.1:3306/fls?charset=utf8",
        max_overflow=0,
        pool_size=5,
        pool_timeout=30,
        pool_recycle=-1
    )
SessionFactory = sessionmaker(bind=engine)
# 实例化
session = SessionFactory()

# 1、单条增加
obj = Users(name='lzp')
session.add(obj)
session.commit()  # 提交

# 2、多条增加
session.add_all([
    Users(name='lwb'),
    Users(name='shj')
])
session.commit()

#  3、查询
# 查询所有
res = session.query(Users).all()
print(res)  # 拿到的是对象列表
for row in res:
    print(row.id, row.name)

# 过滤查询
res = session.query(Users).filter(Users.id > 2)
for i in res:
    print(i.name)

# 只拿第一条的结果
res = session.query(Users).filter(Users.id >= 2).first()
print(res.name)

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

# 5、改
session.query(Users).filter(Users.id == 4).update({Users.name: "沙雕"})
session.query(Users).filter(Users.id == 5).update({'name': "帅哥"})
# synchronize_session=False，如果不指定，默认会做数值的相加，导致报错
session.query(Users).filter(Users.id == 1).update({'name': Users.name+'_dsb'}, synchronize_session=False)
session.commit()

session.close()

2.2、SQLAlchemy常用的操作

all() 返回查询到的所有的结果。这个方法比较危险的地方是，如果数据量大且没有使用limit子句限制的话，所有的结果都会加载到内存中。它返回的是一个列表，如果查询不到任何结果，返回的是空列表。
first() 返回查询到的第一个结果，如果没有查询到结果，返回None。
.scalar() 这个方法与.one_or_none()的效果一样。 如果查询到很多结果，抛出sqlalchemy.orm.exc.MultipleResultsFound异常。如果只有一个结果，返回它，没有结果返回None。
one() 如果只能查询到一个结果，返回它，否则抛出异常。没有结果时抛sqlalchemy.orm.exc.NoResultFound，有超过一个结果时抛sqlalchemy.orm.exc.MultipleResultsFound。
one_or_none() 比起one()来，区别只是查询不到任何结果时不再抛出异常而是返回None。
get() 这是个比较特殊的方法。它用于根据主键来返回查询结果，因此它有个参数就是要查询的对象的主键。如果没有该主键的结果返回None，否则返回这个结果。

# 1、指定查询的列
res = session.query(Users.id, Users.name).all()
for i in res:
    print(i)  # (1, 'lzp_dsb')
    print(i[0], i.id)  # 1 1

# 2、默认条件是and
r = session.query(Users).filter(Users.id>1, Users.name=='ds').all()
print(r)

# 3、between……and，后面不加.all()，打印的结果就是执行的sql语句
r = session.query(Users).filter(Users.id.between(1,4)).all()
print(r)

# 4、id在列表[1,3,4]：in
r = session.query(Users).filter(Users.id.in_([1,3,4])).all()
for i in r:
    print(i.name)
# 5、not in：~
r = session.query(Users).filter(~Users.id.in_([1,3,4])).all()

# 6、子查询
r = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='帅哥'))).all()

# 7、and和or
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与or混用
        and_(Users.name == 'eric', Users.id > 3),
        Users.extra != ""
    )).all()

# 8、filter_by：内部传的是参数，不像filter传表达式
session.query(Users).filter_by(name='shj').all()

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

# 10、切片
ret = session.query(Users)[1:2]

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

# 12、分组：group_by
from sqlalchemy.sql import func

# 按部门id分组
ret = session.query(Users).group_by(Users.depart_id).all() #如果有多条数据，这么指定不能拿到我们想要的数据，要用func.max()或其他指定
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.depart_id).having(func.count(Users.id) >2).all()  # having，部门人数大于2的数据

# 13、组合
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()  # 不去重

# 14、连表
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()