文章版权由作者李晓晖及博客园共有,若转载请为大庭广众处于标明出处:http://www.cnblogs.com/naaoveGIS/

本篇对于Python操作MySQL主要行使简单栽办法:

1.    背景

        
准确说,该种之痕展示涉及到片个点,一个凡是轨道查询显示,一个凡轨道信息挖掘展示。随着轨道表数据的加,以及轨道信息挖掘涉及到之案卷表数据的增加,项目达到即出现了于显著的轨道显示性质问题。

      
这里,我尝试从代码流程逻辑、GPS采集优化、数据库常用优化(轨迹历史数据迁移和目录建立)、表结构重构,Redis缓存的援五只地方来进展优化。

1、原生模块pymsql。

2.    代码流程逻辑优化

2、ORM框架SQLAchemy。

2.1国有数据的合理复用

        
在头设计流程时,轨迹显示与轨道信息挖掘是简单个相对独立部分,一个负责地图端的轨迹显示、一个担提供于业务端(MIS和手机)来调用展示轨迹挖掘信息,所以造成个别独力量成为了独家独立的接口。

      
但是就历史轨迹表数据的增产,这种分离方式出现了举世瞩目的性弊端——即轨迹查询结果的复用。

      
轨迹信息挖掘流程的率先步,需要取到需要打的富有轨道点。而这些点当轨道显示时,就早已取得到了。以之前的一定量只事情单独的逻辑,将会造成轨迹获取之还查询。

       这里,我提议用流程稍作改,如下图所示:

   图片 1

    

pymsql

pymsql是Python中操作MySQL的模块,其以方式及MySQLdb几乎一样。

2.2轨道信息挖掘的询问瘦身

        
在头的轨迹信息和案卷关联的打通遭,我们最为开始询问四张表:案卷表(dlmis.to_rec)、核查核实表(dlmis.to_mi_patrol_task)、历史案卷表(dlhist.to_his_rec)、历史核查核实表(dlhist.to_his_mi_patrol_task)。但是,经过测试发现历史案卷表和历史核查表往往很挺,是查询的瓶颈所在。所以我们增加了一个参数(histrec)来开展说明底询问控制。

      
当histrec值为false时,查询案卷表和核对核实表就无异组;当也ture时,查询历史案卷表和历史核查核实表那无异组。

      
由于系统默认是亮当天轨道信息挖掘,所以histrec参数为true,从而避免了开头查询时对历史案卷那无异组的查询。

windows下载安装

  1. pip3 install pymysql

3.GPS征集优化

      
在GPS采集源头,通过对行为者的状态分析(停留、行走、跑步等),实现对GPS采集频率之控制。并且通过对GPS信号、位置精度等附加信分析,过滤掉无效GPS以及室内GPS数据。从而增加使得GPS的以,实现GPS采集存储量的减负。

运用操作

执行SQL

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4. import pymysql

  5.  

  6. # 创建连接

  7. conn =
    pymysql.connect(host=’192.168.31.134′,port=3306,user=’root’,passwd=’123456′,db=’information_schema’)

  1. # 创建游标

  2. cursor = conn.cursor()

  3. # 执行SQL,并赶回给影响行数

  4. effect_row = cursor.execute(“select * from tables”)

  5. print(effect_row)

  6. # 取出同长长的数据

  7. print(cursor.fetchone())

  8. # 取出前n条数据

  9. print(“*********************************************************”)

  1. print(cursor.fetchmany(5))

  2. # 取出所有数据

  3. print(“*********************************************************”)

  1. print(cursor.fetchall())

  2. # 提交,不然无法保存新建或修改的数

  1. conn.commit()

  2. # 关闭游标

  3. cursor.close()

  4. # 关闭连接

  5. conn.close()

想必报错:pymysql.err.InternalError: (1130, “Host ‘192.168.31.1’ is not
allowed to connect to this MySQL server”)

唯恐是为你的账号不容许打远程登录,只能当localhost登录。只待以localhost的那尊电脑登录mysql,更改对承诺数据库里的”user”表里的”host”项,把”localhost”改吗”%”。

解决办法:

1、改表法

  1. [root@lnmp ~]# mysql -uroot -p123456
  1. mysql> use test;

  2. Database changed

  3. mysql> update user set host = ‘%’ where user = ‘root’;

  1. mysql> select host,user from user;
  1. mysql> flush rivileges;

2、授权法

允许用户user使用密码password从另外主机连接至mysql服务器。

  1. mysql> grant all privileges on *.*
    to ‘user’@’%’ identified by ‘password’ with grant option;

  2. mysql> flush privileges;

兴用户user从ip192.168.1.122之主机连接到mysql服务器,并使password作为密码。

  1. mysql> grant all privileges on *.*
    to ‘user’@’192.168.1.122’ identified by ‘password’ with grant
    option;

  2. mysql> flush privileges;

容用户user从ip192.168.1.122底主机连接到mysql服务器的test_db数据库,并利用password作为密码。

  1. mysql> grant all privileges on
    test_db.* to ‘user’@’192.168.1.122’ identified by ‘password’ with
    grant option;

  2. mysql> flush privileges;

栽数据

  1. # 插入数据

  2. effect_row = cursor.executemany(“inster into
    table_name(field_name1,field_name2)
    values(%s,%s)”,[(“value_1″,”value_2”),(“value_3″,”value_4”)])

4.数据库常用优化

SQLAlchemy ORM

4.1史轨迹迁移

      
从作业范围分析,得出针对一个月份前之轨道查询在工作达到来讲基本是任图的(一个月前之绩效已考核、工资曾发放)。甚至于拖欠项目现场,七天前之轨迹,也未是业主所关切的。所以这边以一个定时任务,在每日深夜(避免迁徙中针对正在上传的轨道造成影响)进行轨迹的搬,轨迹表中只保留最近七天之轨道,而各个一个当天之轨迹都易至历史轨迹表中。据目前初步统计,七上的轨道量大约在80W长条左右,数量得到那个好的决定。

ORM介绍

目标关联映射(Object Relational
Mapping,简称ORM),是平种植次技术,用于落实面向对象编程语言里不同门类系统的多寡里面的转移。从效益上说,它实质上是开创了一个只是当编程语言里应用的——”虚拟对象数据库”。

面向对象是从软件工程中心标准(如耦合、聚合、封装)的根底及发展起的,而关系数据库则是起数学理论发展而来之,两仿照理论在明显的区别。为了化解这不般配的场面,对象关系映射技术出现。

目标关联映射(Object Relational
Mapping)提供了概念性的、易于理解的模型化数据的方。ORM方法论基于三单核心标准。简单,以无比中心的样式建模数据。传达性,数据库结构给任何人都能够明白的语言文档化。精确性,基于数据模型创建是规范的组织。典型地,建模者通过征集来自那些熟悉应用程序但非纯数据建模者开发信息模型。建模者必须能够用非技术企业专家可以了解的术语在概念层次上及数据结构进行报道。建模者也亟须能盖略的单元分析信息,对样本数开展拍卖。ORM专门让规划为改进这种沟通。

图片 2

4.2轨道表上建立目录

       GIS端的轨道查询语句也:

select aa.coordinate_x as coordinatex,aa.coordinate_y as
coordinatey,to_char(aa.update_time, ‘YYYY-MM-DD HH24:MI:SS’) as
logtime  from dlmis.tr_log_patrol_pos aa where aa.update_time>=
?  and aa.update_time <? and aa.patrol_id = ? and
revised_coord_y<>-1 and aa.coordinate_x>0 and
aa.coordinate_y >0 order  by  aa.update_time

       以patrol_id和update_time分别树目录:

 图片 3

ORM的优点

1、隐藏了多少访问细节,”封闭”的通用数据库交互,ORM的核心。使得通用数据库的互变得简单易行,并且完全不用考虑SQL语句。

2、ORM使组织固化数据结构变得简单易行。

5.表结构重构

      
针对轨道迁徙方案,依然是隐患,即如果确实出现得对一个月前的史轨迹查询时,效率问题无法逃脱。这里以大表改小表的思路进行疏散,在小无考虑某些种类达或者导致的影响,提出少种表结构重构的方案。

ORM的缺点

1、自动化意味着映射和涉嫌管理,代价是牺牲性能。现在各种ORM框架还在品尝以各种方式(如LazyLoad、Cache)来减轻性能的熏陶,效果比较显著。

2、面向对象的查询语言(OQL)作为一如既往栽数据库和对象期间的过渡,虽然隐藏了数码层面的事务抽象,但并无克全的荫掉数据库层的计划性,并且确实将增加学习成本。

3、对于复杂查询,ORM仍然无法。虽然足兑现,但是不值得。视图可以化解大部分calculated
column、case、group、having、order by、exists,但是查询条件a and b and
not c and (d or e)不能够非常好解决。

5.1分表方案(方案一)

        
假设,我们各隔一完美(以同等圆满吧条例)建立一个轨道表,表中仅存放该周的轨迹,那么每周轨迹表中的数据量将大大减少。表底命名为年_周,比如(2017_18,表示2017年18两全),在询问轨迹时,算有查询时所对应之轨迹表,进行查询即可。

常用的ORM框架

1、Hibernate全自动需要hql语句。

2、iBatis半自动自己写sql语句,可操作性强,小巧。

3、EclipseLink一个但扩大的支撑JPA的ORM框架,提供强有力的复苏存功能,缓存支持集群。

4、Apache OJB等等。

5、在Python中尽有名的ORM框架是SQLAlchemy。

5.2轨道摘要表的使用(方案二)

      
提出该方案的设想是,我们因为退数据冗余、减少磁盘读操作,在条分缕析事情的基础及展开设计。以下也即的轨迹表:

 图片 4

      
我们着眼历史轨迹表,可以窥见一个口一样上好出现一千条数。这些数据遭到,人员ID为再信息,人员之进度、角度、道路等等都是轨道查询中无需之字段。针对这种现象,我们提出一个轨道摘要表的定义,我以该表设计吧如下:

 

Id(流水号)

patrolID(监督员编号)

Date(日期,单位天)

报文(blob二进制)

      
其中最为中心的凡报文,报文中我们用坐(x,y,createtime)的格式,转换成二进制方式囤当天底具有中轨迹。

      
这样,轨迹摘要中针对有监督员的轨迹信息,将只是发生同漫长记下了。如果只要询问某天的轨道,我们唯有待date和partolid进行过滤即可获得报文,然后解析报文。同时询问的频率会大大提高,假要一个总人口前一样天发生1000长数,那么现在就发同漫漫数。之前3独月1000W修数据,那么现在以只是来1W久数据。

      
这里,涉及到报文内容的实时更新,如何能够行贯彻报文的多更新也?这里,引出我们的其他一个优化,Redis缓存的运用。

SQLAlchemy安装

SQLAlchemy的看法是,SQL数据库的量级和性重要于对象集合,而目标集合的抽象又着重和表和行。

图片 5

Dialect用于与数据API进行交流,根据配置文件之两样调用不同之数据库API,从而实现对数据库的操作。

  1. MySQL-Python

  2.     mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>

  1.  

  2. pymysql

  3.     mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]

  1.  

  2. MySQL-Connector

  3.     mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>

  1.  

  2. cx_Oracle

  3.     oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value…]

  1.  

  2. 重多详见:http://docs.sqlalchemy.org/en/latest/dialects/index.html

安装sqlalchemy

  1. pip3 install sqlalchemy

6.Redis的运用

      
轨迹的写入,无论是对轨道摘要表,还是针对本的轨迹表,均是一个相连发出出的操作。而且针对性轨道表,查询最高的应当是当天轨道查询。所以,针对当天频频反映的轨道,我们好以那都先存入redis中,在通过定时任务,于深夜同步写副到轨迹表中。

       同样,查询轨迹时,针对当天轨道的查询,也先由redis中获。

SQLAlchemy基本用

创一个表明。

SQL实现:

  1. CREATE TABLE USER(

  2.    ID INTEGER NOT NULL AUTO_INCREMENT,

  1.    NAME VARCHAR(32),

  2.    PASSWORD VARCHAR(64),

  3.    PRIMARY KET (ID)

  4. )

ORM实现:

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4.  

  5. import sqlalchemy

  6.  

  7. from sqlalchemy import create_engine

  1. from sqlalchemy.ext.declarative import
    declarative_base

  2. from sqlalchemy import
    Column,Integer,String

  3.  

  4. # echo=True打印信息

  5. engine = create_engine(“mysql+pymysql://root:123456@192.168.31.134/test_db”,

  1.                        encoding=’utf-8′,echo=True)
  1.  

  2. # 生成orm基类

  3. Base = declarative_base()

  4.  

  5. class
    User(Base):

  6.     # 表名

  7.     __tablename__ = ‘USER’

  8.     # 定义字段ID,并设置也主键

  9.     ID =
    Column(Integer,primary_key=True)

  10.     NAME = Column(String(32))

  11.     PASSWORD = Column(String(64))

  1. # 创建表结构

  2. Base.metadata.create_all(engine)

除上面的创建表之外,还有同栽创建表的方法。

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4.  

  5. import sqlalchemy

  6.  

  7. from sqlalchemy import
    create_engine,Table,MetaData,Column,Integer,String,ForeignKey

  1. from sqlalchemy.orm import mapper
  1.  

  2. engine = create_engine(“mysql+pymysql://root:123456@192.168.31.134/test_db”,

  1.                        encoding=’utf-8′,echo=True)
  1. metadata = MetaData()

  2.  

  3. user = Table(‘USER_TABLE’,metadata,

  1.              Column(‘ID’,Integer,primary_key=True),
  1.              Column(‘NAME’,String(50)),
  1.              Column(‘FULLNAME’,String(50)),
  1.              Column(‘PASSWORD’,String(12))
  1.              )

  2.  

  3.  

  4. class
    User(object):

  5.     def
    __init__(self,name,fullname,password):

  6.         self.name = name

  7.         self.fullname = fullname

  1.         self.password = password
  1.  

  2. mapper(User,user)

  3.  

  4. # 创建表结构

  5. metadata.create_all(engine)

先是栽方式创造的表是基于第二种植方法的重复封装。

下ORM创建同长数据。

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4.  

  5. import sqlalchemy

  6. from sqlalchemy import
    create_engine,Column,Integer,String

  7. from sqlalchemy.orm import sessionmaker

  1. from sqlalchemy.ext.declarative import
    declarative_base

  2.  

  3. engine = create_engine(“mysql+pymysql://root:123456@192.168.31.134/test_db”,

  1.                        encoding=’utf-8′,echo=True)
  1. Base = declarative_base()

  2.  

  3. class
    User(Base):

  4.     __tablename__ = ‘USER’

  5.     ID =
    Column(Integer,primary_key=True)

  6.     NAME = Column(String(32))

  7.     PASSWORD = Column(String(64))

  1.  

  2. Base.metadata.create_all(engine)

  1. # 创建同数据库的会话session class,注意这里归给session的是一个class,不是实例
  1. Session_class =
    sessionmaker(bind=engine)

  2. # 生成session实例

  3. Session = Session_class()

  4. # 生成要创建的数对象

  5. user_obj = User(NAME=”Golden”,PASSWORD=”123456″)

  6. # 此时还从来不创建对象

  7. print(user_obj.NAME,user_obj.ID)

  1. # 把需要创造的多少对象上加到session
  1. Session.add(user_obj)

  2. # 此时还无创建对象

  3. print(user_obj.NAME,user_obj.ID)

  1. # 创建数量,统一提交

  2. Session.commit()

7.总结

       该类型的轨道优化方案,建议以三步走战略来拓展:

       a.先完成代码逻辑、数据库常用优化,然后观察效果。

       b.如果效果一般,再展开Redis运用的改造。

      
c.最后,代价不过深的申结构重构,个人觉得效果会生好,但是最有或对现有工作等致影响。

 

                        
 —–欢迎转载,但保留版权,请为大庭广众处于标明出处:http://www.cnblogs.com/naaoveGIS/

                                                                           
如果您看本文确实帮了公,可以微信扫一扫,进行小额的打赏和鞭策,谢谢
^_^

                                     
  图片 6

 

查询、修改

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4.  

  5. import sqlalchemy

  6. from sqlalchemy import
    create_engine,Column,Integer,String

  7. from sqlalchemy.ext.declarative import
    declarative_base

  8. from sqlalchemy.orm import sessionmaker

  1. engine = create_engine(“mysql+pymysql://root:123456@192.168.31.134/test_db”,
  1.                        encoding=’utf-8′,echo=False)
  1. Base = declarative_base()

  2. class
    User(Base):

  3.     __tablename__ = ‘USER’

  4.     ID =
    Column(Integer,primary_key=True)

  5.     NAME = Column(String(32))

  6.     PASSWORD = Column(String(64))

  1.  

  2.     def __repr__(self):

  3.         return “<NAME : %s | PASSWORD : %s>”
    %(self.NAME,self.PASSWORD)

  4.  

  5. Session_class =
    sessionmaker(bind=engine)

  6. Session = Session_class()

  7. # first 查出NAME=’Golden’的首先个

  1. my_user =
    Session.query(User).filter_by(NAME=’Golden’).first()

  2. print(my_user.NAME,my_user.PASSWORD)

  1. # NAME=’Golden’

  2. data =
    Session.query(User).filter_by(NAME=’Golden’).all()

  3. print(data[0].PASSWORD)

  4. #all 查出有

  5. data_2 =
    Session.query(User).filter_by().all()

  6. print(data_2)

  7. data_3 =
    Session.query(User).filter(User.ID == 1).all()

  8. print(data_3)

  9. data_4 =
    Session.query(User).filter_by(ID=1).all()

  10. print(data_4)

  11. # 多标准化查询

  12. data_5 =
    Session.query(User).filter(User.ID > 1).filter(User.ID <
    4).all()

  13. print(data_5)

  14. data_6 =
    Session.query(User).filter().first()

  15. print(data_6)

  16. # 修改

  17. data_6.NAME = ‘zhangsan’

  18. data_6.PASSWORD = ‘110’

  19. # 提交

  20. Session.commit()

回滚

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4.  

  5. import sqlalchemy

  6. from sqlalchemy import
    create_engine,Column,Integer,String

  7. from sqlalchemy.ext.declarative import
    declarative_base

  8. from sqlalchemy.orm import sessionmaker

  1.  

  2. engine = create_engine(“mysql+pymysql://root:123456@192.168.31.134/test_db”,

  1.                        encoding=’utf-8′,echo=False)
  1. Base = declarative_base()

  2.  

  3. class
    User(Base):

  4.     __tablename__ = ‘USER’

  5.     ID =
    Column(Integer,primary_key=True)

  6.     NAME = Column(String(32))

  7.     PASSWORD = Column(String(64))

  1.  

  2.     def __repr__(self):

  3.         return “<ID : %s | NAME : %s | PASSWORD :
    %s>” %(self.ID,self.NAME,self.PASSWORD)

  4.  

  5. Session_class =
    sessionmaker(bind=engine)

  6. Session = Session_class()

  7.  

  8. my_user =
    Session.query(User).filter_by(ID=1).first()

  9. my_user.NAME = ‘Mark’

  10.  

  11. fake_user =
    User(NAME=’Merry’,PASSWORD=’999999′)

  12. Session.add(fake_user)

  13. # 查看刚刚添加和修改的数据

  14. print(Session.query(User).filter(User.NAME.in_([‘Merry’,’Mark’])).all())

  1. # rollback

  2. Session.rollback()

  3. # 再次查询

  4. print(Session.query(User).filter(User.NAME.in_([‘Merry’,’Mark’])).all())

统计以及分组

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4.  

  5. import sqlalchemy

  6. from sqlalchemy import
    create_engine,Column,Integer,String,func

  7. from sqlalchemy.ext.declarative import
    declarative_base

  8. from sqlalchemy.orm import sessionmaker

  1.  

  2. engine = create_engine(“mysql+pymysql://root:123456@192.168.31.134/test_db”,

  1.                        encoding=’utf-8′,echo=False)
  1.  

  2. Base = declarative_base()

  3.  

  4.  

  5. class
    User(Base):

  6.     __tablename__ = ‘USER’

  7.     ID =
    Column(Integer,primary_key=True)

  8.     NAME = Column(String(32))

  9.     PASSWORD = Column(String(64))

  1.  

  2.     def __repr__(self):

  3.         return “<ID : %s | NAME : %s | PASSWORD :
    %s>” %(self.ID,self.NAME,self.PASSWORD)

  4.  

  5. Session_class =
    sessionmaker(bind=engine)

  6. Session = Session_class()

  7. # 统计ha开头的NAME个数

  8. print(Session.query(User).filter(User.NAME.like(“ha%”)).count())

  9. # 分组

  10. print(Session.query(User.NAME,func.count(User.NAME)).group_by(User.NAME).all())

连表

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4.  

  5. import sqlalchemy

  6. from sqlalchemy import
    create_engine,Column,Integer,String,DATE

  7. from sqlalchemy.orm import sessionmaker

  1. from sqlalchemy.ext.declarative import
    declarative_base

  2.  

  3. engine = create_engine(“mysql+pymysql://root:123456@192.168.31.134/test_db”,

  1.                        encoding=’utf-8′,echo=True)
  1. Base = declarative_base()

  2.  

  3. class
    User(Base):

  4.     __tablename__ = ‘USER’

  5.     ID =
    Column(Integer,primary_key=True)

  6.     NAME = Column(String(32))

  7.     PASSWORD = Column(String(64))

  1.  

  2.     def __repr__(self):

  3.         return “<ID :%s | NAME : %s | PASSWORD :
    %s>” %(self.ID,self.NAME,self.PASSWORD)

  4.  

  5.  

  6. class
    Student(Base):

  7.     __tablename__ = ‘STUDENT’

  1.     ID =
    Column(Integer,primary_key=True)

  2.     NAME =
    Column(String(32),nullable=False)

  3.     REGISTER_DATE =
    Column(DATE,nullable=False)

  4.     GENDER =
    Column(String(32),nullable=False)

  5.  

  6.     def __repr__(self):

  7.         return “<NAME : %s | PASSWORD : %s>”
    %(self.NAME,self.REGISTER_DATE)

  8.  

  9. Base.metadata.create_all(engine)

  1. Session_class =
    sessionmaker(bind=engine)

  2. Session = Session_class()

  3.  

  4. # 外联

  5. print(Session.query(User,Student).filter(User.ID
    == Student.ID).all())

  6. # 两只说明要有外键关联才会应用 Can’t
    find any foreign key relationships between ‘A’ and ‘B’

  7. print(Session.query(User).join(Student).all())

  1. print(Session.query(User).join(Student,isouter=True).all())
  1. Session.commit()

贯彻两单说明的外键关联

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4. import sqlalchemy

  5. from sqlalchemy import
    create_engine,Column,Integer,String,DATE,ForeignKey

  6. from sqlalchemy.orm import
    sessionmaker,relationship

  7. from sqlalchemy.ext.declarative import
    declarative_base

  8.  

  9. engine = create_engine(“mysql+pymysql://root:123456@192.168.31.134/test_db”,

  1.                        encoding=’utf-8′,echo=False)
  1. Base = declarative_base()

  2.  

  3. class
    Student(Base):

  4.     __tablename__ = ‘student’

  1.     id =
    Column(Integer,primary_key=True)

  2.     name =
    Column(String(32),nullable=False)

  3.     register_date =
    Column(DATE,nullable=False)

  4.  

  5.     def __repr__(self):

  6.         return “<name : %s | register_date :
    %s>”%(self.name,self.register_date)

  7.  

  8.  

  9. class
    StudyRecord(Base):

  10.     __tablename__ = “study_record”

  11.     id =
    Column(Integer,primary_key=True)

  12.     day =
    Column(Integer,nullable=False)

  13.     status =
    Column(String(32),nullable=False)

  14.     # 创建外键

  15.     stu_id =
    Column(Integer,ForeignKey(“student.id”))

  16.     #

  17.     student = relationship(“Student”,backref=”my_study_record”)

  18.  

  19.     def __repr__(self):

  20.        return “<%s day :%s | status :
    %s>”%(self.student.name,self.day,self.status)

  21. # 创建表结构

  22. Base.metadata.create_all(engine)

  1. # 创建及数据库的会话session

  2. Session_class =
    sessionmaker(bind=engine)

  3. # 生成session实例

  4. session = Session_class()

  5.  

  6. “””添加数据

  7. s1 = Student(name=”Golden”,register_date=”2017-12-24″)

  8. s2 = Student(name=”Jack”,register_date=”2017-11-13″)

  9. s3 = Student(name=”Rain”,register_date=”2017-10-11″)

  10. s4 = Student(name=”Eric”,register_date=”2017-07-07″)

  11.  

  12. study_obj1 =
    StudyRecord(day=1,status=”Yes”,stu_id=1)

  13. study_obj2 =
    StudyRecord(day=2,status=”No”,stu_id=1)

  14. study_obj3 =
    StudyRecord(day=3,status=”Yes”,stu_id=1)

  15. study_obj4 =
    StudyRecord(day=1,status=”Yes”,stu_id=2)

  16.  

  17. # 一次性全部开立

  18. session.add_all([s1,s2,s3,s4,study_obj1,study_obj2,study_obj3,study_obj4])

  1. “””

  2. stu_obj =
    session.query(Student).filter(Student.name == “Golden”).first()

  3. print(stu_obj)

  4. print(stu_obj.my_study_record)

  1. # 提交

  2. session.commit()

差不多外键关联

创办包含多外键的阐发结构。

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4.  

  5. from sqlalchemy.orm import relationship

  1. from sqlalchemy import
    create_engine,Column,Integer,String,DATE,ForeignKey

  2. from sqlalchemy.ext.declarative import
    declarative_base

  3.  

  4. Base = declarative_base()

  5.  

  6. class
    Customer(Base):

  7.     __tablename__ = ‘customer’

  1.     id =
    Column(Integer,primary_key=True)

  2.     name = Column(String(64))

  3.  

  4.     billing_address_id =
    Column(Integer,ForeignKey(“address.id”))

  5.     shipping_address_id =
    Column(Integer,ForeignKey(“address.id”))

  6.  

  7.     billing_address =
    relationship(“Address”,foreign_keys=[billing_address_id])

  1.     shipping_address =
    relationship(“Address”,foreign_keys=[shipping_address_id])
  1.  

  2. class
    Address(Base):

  3.     __tablename__ = ‘address’

  1.     id =
    Column(Integer,primary_key=True)

  2.     street = Column(String(64))

  3.     city = Column(String(64))

  4.     state = Column(String(64))

  5.  

  6.     def __repr__(self):

  7.         return self.street

  8.  

  9. engine = create_engine(“mysql+pymysql://root:123456@192.168.31.134/test_db”,

  1.                        encoding=’utf-8′,echo=False)
  1. # 创建表结构

  2. # Base.metadata.create_all(engine)

栽数据及查询。

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4.  

  5. import os,sys

  6. path =
    os.path.dirname(os.path.dirname(os.path.abspath(__file__)))

  1. sys.path.append(path)

  2. from day11 import orm_many_fk

  3. from sqlalchemy.orm import sessionmaker

  1.  

  2. Session_class =
    sessionmaker(bind=orm_many_fk.engine)

  3. session = Session_class()

  4.  

  5. # 创建数据

  6. # addr1 =
    orm_many_fk.Address(street=”zhongshanroad”,city=”qiaokou”,state=’hankou’)

  7. # addr2 =
    orm_many_fk.Address(street=”dongyiroad”,city=”hongshan”,state=”wuchang”)

  8. # addr3 =
    orm_many_fk.Address(street=”guangshanroad”,city=”gaoxin”,state=”guanggu”)

  9. #

  10. #
    session.add_all([addr1,addr2,addr3])

  11. # c1 =
    orm_many_fk.Customer(name=”Golden”,billing_address=addr1,shipping_address=addr3)

  1. # c2 =
    orm_many_fk.Customer(name=”Jack”,billing_address=addr2,shipping_address=addr2)
  1. #

  2. # session.add_all([c1,c2])

  3.  

  4. # 查询数据

  5. obj =
    session.query(orm_many_fk.Customer).filter(orm_many_fk.Customer.name==”Golden”).first()

  6. print(obj.name,obj.billing_address,obj.shipping_address)

  1.  

  2. session.commit()

大多针对大多涉及

开创多对准几近关联表

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4.  

  5. from sqlalchemy import
    Table,Column,Integer,String,DATE,ForeignKey,create_engine

  6. from sqlalchemy.orm import
    relationship,sessionmaker

  7. from sqlalchemy.ext.declarative import
    declarative_base

  8.  

  9. engine = create_engine(“mysql+pymysql://root:123456@192.168.31.134/test_db”,

  1.                        encoding=’utf-8′,echo=False)
  1.  

  2. Base = declarative_base()

  3.  

  4. book_m2m_author =
    Table(‘book_m2m_author’,Base.metadata,

  5.                         Column(‘book_id’,Integer,ForeignKey(‘books.id’)),

  1.                         Column(‘author_id’,Integer,ForeignKey(‘authors.id’)))
  1.  

  2. class
    Book(Base):

  3.     __tablename__ = ‘books’

  4.     id =
    Column(Integer,primary_key=True)

  5.     name = Column(String(64))

  6.     pub_date = Column(DATE)

  7.     authors =
    relationship(‘Author’,secondary=book_m2m_author,backref=’books’)

  1.  

  2.     def __repr__(self):

  3.         return self.name

  4.  

  5.  

  6. class
    Author(Base):

  7.     __tablename__ = ‘authors’

  1.     id =
    Column(Integer,primary_key=True)

  2.     name = Column(String(32))

  3.  

  4.     def __repr__(self):

  5.         return self.name

  6.  

  7. Base.metadata.create_all(engine)

  1. Session_class =
    sessionmaker(bind=engine)

  2. session = Session_class()

 

插入数据以及询问

  1. __author__ = ‘Golden’

  2. #!/usr/bin/env python3

  3. # -*- coding:utf-8 -*-

  4.  

  5. import os,sys

  6. path =
    os.path.dirname(os.path.dirname(os.path.abspath(__file__)))

  1. sys.path.append(path)

  2. from day11 import orm_m2m

  3. from sqlalchemy.orm import sessionmaker

  1.  

  2. Session_class =
    sessionmaker(bind=orm_m2m.engine)

  3. session = Session_class()

  4.  

  5. # 创建数据

  6. # b1 = orm_m2m.Book(name=”Python”)

  7. # b2 = orm_m2m.Book(name=”JAVA”)

  8. # b3 = orm_m2m.Book(name=”C++”)

  9. # b4 = orm_m2m.Book(name=”c#”)

  10. #

  11. # a1 = orm_m2m.Author(name=”Golden”)

  12. # a2 = orm_m2m.Author(name=”Jack”)

  13. # a3 = orm_m2m.Author(name=”Rain”)

  14. #

  15. # b1.authors = [a1,a2]

  16. # b2.authors = [a1,a2,a3]

  17. #

  18. #
    session.add_all([b1,b2,b3,b4,a1,a2,a3])

  19. # session.commit()

  20.  

  21. print(“通过书表查询关联的作者”.center(50,”*”))

  22. book_obj =
    session.query(orm_m2m.Book).filter_by(name=”JAVA”).first()

  23. print(book_obj.name,book_obj.authors)

  1. print(“通过作者表查询关联的题”.center(50,”*”))

  2. author_obj =
    session.query(orm_m2m.Author).filter_by(name=”Golden”).first()

  3. print(author_obj.name,author_obj.books)

  1.  

  2. #
    多对几近去,删除数据经常不用管book_m2m_author,sqlalchemy会自动删除相应之多寡

  1. # 通过开删除作者

  2. book_obj.authors.remove(author_obj)

  1.  

  2. # 直接删除作者

  3. author_obj2 =
    session.query(orm_m2m.Author).filter_by(name=”Jack”).first()

  4. session.delete(author_obj2)

  5. session.commit()

 

支撑中文

sqlalchemy设置编码字符集一定要以数据库访问的URL上加charset=utf8,否则数据库的连日就无是utf8的编码格式。

  1. # 支持中文

  2. engine = create_engine(“mysql+pymysql://root:123456@192.168.31.134/test_db?charset=utf8”,echo=True)