python mysql数据库操作grid控件_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 importcreate_engine

engine= create_engine("mysql+mysqldb://root:password@localhost:3306/test")

2，创建表结构

from sqlalchemy importcreate_enginefrom sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy importColumn,String,Integerimport 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>, <__main__.host object at>] #一共有两条数据

<__main__.host object at>localhost<__main__.host object at>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>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)

结果： localhost1res= 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 importand_,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 importcreate_enginefrom sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy importColumn,String,Integer,ForeignKeyfrom sqlalchemy.orm importsessionmakerimportpymysql

pymysql.install_as_MySQLdb()

engine= create_engine("mysql+mysqldb://root:password@localhost:3306/test",echo=True)

Base=declarative_base()classUser(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可以有多个userclassGroup(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 * fromuser_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 relationshipclassUser(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 lilyin group:develop

反向查询： relationship参数backref

通过groupname 得出当前组所有的 username

classUser(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 inres.xxx:print("group [%s] has user:[%s])"%(res.name,i.name))

结果：

group [develop] has user:[jack])

group [develop] has user:[lily])

join 连表查询

关联关系同上， 修改数据如下：> select * fromuser_info;+----+--------+----------+

| id | name | group_id |

+----+--------+----------+

| 1 | jack | 1 |

| 2 | lily | 1 |

| 3 | beibei | 2 |

| 4 | huahua | 1 |

| 5 | nini | NULL |[test]> select * fromuser_group;+-----+-----------+

| gid | name |

+-----+-----------+

| 1 | develop |

| 2 | operation |

| 3 | project |

+-----+-----------+res= session.query(User).join(Group).all() #默认inner join

for i inres:print(i,i.name)

结果：<__main__.user object at>jack<__main__.user object at>lily<__main__.user object at>beibei<__main__.user object at>huahua

res=session.query(Group).join(User).all()for i inres:print(i, i.name)

结果：<__main__.group object at>develop<__main__.group object at>operation

res=session.query(User.name,Group.name).join(Group).all()for i inres:print(i)

结果：

('jack', 'develop')

('lily', 'develop')

('beibei', 'operation')

('huahua', 'develop')

res= session.query(Group).outerjoin(User).all() #outer join

for i inres:print(i,i.name)

结果<__main__.group object at>develop<__main__.group object at>operation<__main__.group object at> project

orm多对多关联

学校有学生和老师两种角色，一个老师有多个学生，一个学生也可以有多个老师，如果通过建立外键来关联是无法满足需求的。这个时候需要通过建立第三张表-中间表来表示两者之间的关联关系：

步骤：

from sqlalchemy importcreate_engine,Tablefrom sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy importColumn,String,Integer,ForeignKeyfrom sqlalchemy.orm importsessionmaker,relationshipimport 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指定中间表classStudent(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>, <__main__.student object at>]

可以在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()