MySQLdb支持python2.*，不支持3.* ，python3里面使用PyMySQL模块代替
python3里面如果有报错 django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module.
可以在需要的项目中，在__init__.py中添加两行：
import pymysql pymysql.install_as_MySQLdb()
就可以用 import MySQLdb了。其他的方法与MySQLdb一样
sqlalchemy
是python中的一款orm框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作
orm框架 （object relational mapping)对象关系映射
把关系数据库的表结构映射到对象上，就是把数据库的表（table)，映射为编程语言里面的类（class）
1, 连接数据库
from sqlalchemy import create_engine engine = create_engine("mysql+mysqldb://root:password@localhost:3306/test")
2，创建表结构
from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,String,Integer import pymysql #python3里面需要加上这两行 pymysql.install_as_MySQLdb() engine = create_engine("mysql+mysqldb://root:password@localhost:3306/test?charset=utf8",max_overflow=5) Base = declarative_base() #生成orm基类 class Host(Base): #创建表单 __tablename__ = 'hosts' #表名 以下为表结构属性 id = Column(Integer,primary_key=True,autoincrement=True) hostname = Column(String(64),nullable=False) ip_addr = Column(String(64),nullable=False) port = Column(Integer,default=22) Base.metadata.create_all(engine) #创建表结构 #寻找Base的所有子类，按照子类的结构在数据库中生成对应的数据表信息 #Base.metadata.drop_all(engine) 删除
查看一下，表结构已经创建好了。
- 增加数据
if __name__ == "__main__": DBSession = sessionmaker(bind=engine) session = DBSession() h1 = Host(hostname='localhost',ip_addr='127.0.0.1') #创建host对象，数据库记录 h2 = Host(hostname='centos',ip_addr='192.168.1.10',port=2333) h3 = Host(hostname='ubuntu',ip_addr='172.16.10.23',port=888) session.add(h1) #session.add增加单条数据到session session.add_all([h2,h3,]) #session.add_all增加多条，列表形式 session.commit() #提交保存到数据库
- 删除数据
session.query(Host).filter(Host.hostname=='ubuntu').delete() #删除hostname=ubuntu的那条数据 session.commit() #先查询，对符合条件的结果delete（）
- 修改数据
session.query(Host).filter(Host.id==2).update({'port':8855}) session.query(Host).filter(Host.id==2).update({Host.hostname:Host.hostname+'_01'},synchronize_session=False) # update({},synchronize_session=False 字符串拼接
session.query(Host).filter(Host.id==2).update({Host.port:Host.port+10}) session.commit() # 先查询，然后对查询结果 update（{字段：值}） 原来的数据是 +----+-----------+--------------+------+ | id | hostname | ip_addr | port | +----+-----------+--------------+------+ | 1 | localhost | 127.0.0.1 | 22 | | 2 | centos | 192.168.1.10 | 2333 修改之后：| 2 | centos_01 | 192.168.1.10 | 8865
- 查询
res = session.query(Host).all() #返回一个列表，里面是数据的对象形式，有几条数据就有几个对象 print("res:",res) for i in res: # 可以使用for循环遍历列表取出里面的对象 print(i,i.hostname) 结果： res: [<__main__.Host object at 0x7f4574423a20>, <__main__.Host object at 0x7f4574423a90>] #一共有两条数据 <__main__.Host object at 0x7f4574423a20> localhost <__main__.Host object at 0x7f4574423a90> centos_01 ret = session.query(Host).filter(Host.id>2).all() # 返回符合条件的对象列表，如果为空则为[] 结果：[] res = session.query(Host).filter(Host.id>1).first() # first()取第一条数据 ，等同于 res = session.query(Host).filter(Host.id>1).all()[0] print(res,res.hostname) 返回： <__main__.Host object at 0x7f03079fcf60> centos_01 res = session.query(Host.hostname,Host.id).all() #返回 hostname，id列表 print(res) 结果：[('localhost', 1), ('centos_01', 2)] ret = session.query(Host.hostname).filter(Host.id > 1).all() #返回id>2的hostname列表 print(ret) 结果：[('centos_01',)] # filter_by（字段名=？） ret = session.query(Host).filter_by(hostname='localhost').first() # 返回对象 print(ret.hostname, ret.id) 结果： localhost 1 res = session.query(Host.hostname).filter_by(id=2).all() print(res) 结果：[('centos_01',)] # 还有更多查询（between，in，and，or，like，order_by，group_by...） ret = session.query(User.username).filter(User.id.between(1,3)).all() ret = session.query(User.username).filter(User.id.in_([1,2])).all() from sqlalchemy import and_,or_ ret = session.query(User.id,User.username).filter(and_(User.id<4,User.username=='alex')).all() print(ret)
orm一对多关联
sqlalchemy不支持直接修改表结构，把原来的表删除了重新创建以下：
一个group可以对应多个user，一个user只能对应一个group
from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,String,Integer,ForeignKey from sqlalchemy.orm import sessionmaker import pymysql pymysql.install_as_MySQLdb() engine = create_engine("mysql+mysqldb://root:password@localhost:3306/test",echo=True) Base = declarative_base() class User(Base): __tablename__ = 'user_info' id = Column(Integer,primary_key=True,autoincrement=True) name = Column(String(64),nullable=False) group_id = Column(Integer,ForeignKey('user_group.gid')) #生成外键。 一个user只能有一个group，一个group可以有多个user class Group(Base): __tablename__ = 'user_group' gid = Column(Integer,primary_key=True,autoincrement=True) name = Column(String(64),nullable=False) Base.metadata.create_all(engine) # 结果： [test]> desc user_info; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | NO | | NULL | | | group_id | int(11) | YES | MUL | NULL | | +----------+-------------+------+-----+---------+----------------+ [test]> desc user_group; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | gid | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | NO | | NULL | |
添加数据：
第一步:先添加user_group表的数据。因为user_info外键关联group gid。需要先创建group否则会报错
if __name__ == "__main__": DBSession = sessionmaker(bind=engine) session = DBSession() g1 = Group(name='develop') g2 = Group(name='operation') g3 = Group(name= 'project') session.add_all([g1,g2,g3]} session.commit()
结果：
+-----+-----------+
| gid | name |
+-----+-----------+
| 1 | develop |
| 2 | operation |
| 3 | project |
第二步：添加user_info 表的数据。可以根据group的查询结果插入group_id
u1 = User(name='jack', group_id=g1.gid) # 错误示例 obj_g2 = session.query(Group).filter(Group.name == 'develop').first() u2 = User(name='lily', group_id=obj_g2.gid) obj_g3 = session.query(Group).filter(Group.gid == 2).first() u3 = User(name='beibei',group_id=obj_g3.gid) u4 = User(name='huahua',group_id=3) session.add_all([u1, u2, u3, u4]) session.commit() 得到结果： MariaDB [test]> select * from user_info; +----+--------+----------+ | id | name | group_id | +----+--------+----------+ | 1 | jack | NULL | | 2 | lily | 1 | | 3 | beibei | 2 | | 4 | huahua | 3 | +----+--------+----------+ u1的group_id为空，可见直接使用g1.gid并没有关联起来 修改一下： obj_g1 = session.query(Group).filter(Group.gid==1).first() session.query(User).filter(User.id==1).update({"group_id":obj_g1.gid}) session.commit() 结果： +----+--------+----------+ | id | name | group_id | +----+--------+----------+ | 1 | jack | 1 | | 2 | lily | 1 | | 3 | beibei | 2 | | 4 | huahua | 3 |
删除表时，需要先删除user表再删group表
relationship 通过外键关联进行查询
- 通过 username 得出所在group的 groupname
在上例中 User表里面添加一行relationship
from sqlalchemy.orm import relationship class User(Base): __tablename__ = 'user_info' id = Column(Integer,primary_key=True,autoincrement=True) name = Column(String(64),nullable=False) group_id = Column(Integer,ForeignKey('user_group.gid')) group = relationship("Group") #添加这一行，relationship与生成表结构无关，仅用于查询方便 查询： res = session.query(User).filter(User.name=='lily').first() print('user %s in group:%s ' % (res.name, res.group.name)) 结果： user lily in group:develop
反向查询： relationship参数backref
- 通过groupname 得出当前组所有的 username
class User(Base): __tablename__ = 'user_info' id = Column(Integer,primary_key=True,autoincrement=True) name = Column(String(64),nullable=False) group_id = Column(Integer,ForeignKey('user_group.gid')) group = relationship("Group",backref='xxx') 查询： res = session.query(Group).filter(Group.name=='develop').first() for i in res.xxx: print("group [%s] has user:[%s])"%(res.name,i.name)) 结果： group [develop] has user:[jack]) group [develop] has user:[lily])
join 连表查询
关联关系同上， 修改数据如下： > select * from user_info; +----+--------+----------+ | id | name | group_id | +----+--------+----------+ | 1 | jack | 1 | | 2 | lily | 1 | | 3 | beibei | 2 | | 4 | huahua | 1 | | 5 | nini | NULL | [test]> select * from user_group; +-----+-----------+ | gid | name | +-----+-----------+ | 1 | develop | | 2 | operation | | 3 | project | +-----+-----------+ res = session.query(User).join(Group).all() #默认inner join for i in res: print(i,i.name) 结果： <__main__.User object at 0x7fa7cd881208> jack <__main__.User object at 0x7fa7cd881278> lily <__main__.User object at 0x7fa7cd8812e8> beibei <__main__.User object at 0x7fa7cd881358> huahua res = session.query(Group).join(User).all() for i in res: print(i, i.name) 结果： <__main__.Group object at 0x7fd85ba63320> develop <__main__.Group object at 0x7fd85ba730f0> operation res = session.query(User.name,Group.name).join(Group).all() for i in res: print(i) 结果： ('jack', 'develop') ('lily', 'develop') ('beibei', 'operation') ('huahua', 'develop') res = session.query(Group).outerjoin(User).all() #outer join for i in res: print(i,i.name) 结果 <__main__.Group object at 0x7f7bf4e9a358> develop <__main__.Group object at 0x7f7bf4eaa128> operation <__main__.Group object at 0x7f7bf4eaa978> project
orm多对多关联
学校有学生和老师两种角色，一个老师有多个学生，一个学生也可以有多个老师，如果通过建立外键来关联是无法满足需求的。这个时候需要通过建立第三张表-中间表来表示两者之间的关联关系：
步骤：
from sqlalchemy import create_engine,Table from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,String,Integer,ForeignKey from sqlalchemy.orm import sessionmaker,relationship import pymysql #python3里面需要加上这两行 pymysql.install_as_MySQLdb() engine = create_engine("mysql+mysqldb://root:password@localhost:3306/test?charset=utf8",max_overflow=5,echo=True) Base = declarative_base() #生成orm基类
StuToTea=Table('stu_to_tea',Base.metadata, #创建一个中间表，关联teacher和student表 Column('students_id',ForeignKey('students.id'),primary_key=True), Column('teachers_id', ForeignKey('teachers.id'), primary_key=True), ) class Teacher(Base): #创建表单 __tablename__ = 'teachers' #表名 以下为表结构属性 id = Column(Integer,primary_key=True,autoincrement=True) name = Column(String(64),nullable=False) rela_student = relationship('Student',secondary=StuToTea,backref = 'rela_teacher') #secondary指定中间表 class Student(Base): __tablename__ = 'students' #表名 以下为表结构属性 id = Column(Integer,primary_key=True,autoincrement=True) name = Column(String(64),nullable=False) Base.metadata.create_all(engine)
插入一些数据：
if __name__ == '__main__': DBsession = sessionmaker(bind=engine) session = DBsession() s1 = Student(name='小王') s2 = Student(name='小李') s3 = Student(name='小杨') s4 = Student(name='小红') s5 = Student(name='小明') t1 = Teacher(name='老丁') t2 = Teacher(name='老唐') t3 = Teacher(name='老陈') t1.rela_student = [s1,s2] #通过关联关系，创建关联的数据到stu_to_tea表中。 t2.rela_student = [s2,] t3.rela_student = [s3,s4,s5] #也可以先创建tea，stu两张表，后面通过查询语句，例如： # obj_t1 = session.query(Teacher).filter(Teacher.id==1).first() # s_all = session.query(Student).all() # obj_t1.rela_student = s_all #通过关联关系来创建 session.add_all([s1,s2,s3,s4,s5,t1,t2,t3]) session.commit()
查找：
t1 = session.query(Teacher).filter(Teacher.id==1).first() print(t1.rela_student) #查找id=1的老师对应的学生 结果： [<__main__.Student object at 0x7f8096aa2cc0>, <__main__.Student object at 0x7f8096aa2d30>] 可以在class Student(Base):下面加上 def __repr__(self): return 'id:%s, name:%s'%(self.id,self.name) 结果返回： [id:1, name:小王, id:2, name:小李]
也可以反向查询：
s2 = session.query(Student).filter(Student.id==2).first() print(s2.rela_teacher) 结果： [id:1, name:老丁, id:2, name:老唐]
删除关联关系：
删除某一个关联关系。例如删除老丁下的小王： t = session.query(Teacher).filter(Teacher.name=='老丁').first() s = session.query(Student).filter(Student.name=='小王').first() t.rela_student.remove(s) session.commit() 假如小李转学了，删除小李，会自动删除所有与之相关的关联。 obj_s = session.query(Student).filter(Student.name=='小李').first() session.delete(obj_s) session.commit()