python中mysql数据库的操作-sqlalchemy

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()