一、原生模块pymsql,1、原生模块pymsql

1、原生模块pymsql,1、原生模块pymsql

本篇对于Python操作MySQL首要运用三种方法:

本篇对于Python操作MySQL首要利用二种格局:

壹、原生模块pymsql。

1、原生模块pymsql。

2、ORM框架SQLAchemy。

2、ORM框架SQLAchemy。

pymsql

pymsql是Python中操作MySQL的模块,其利用方法和MySQLdb差不多相同。

pymsql

pymsql是Python中操作MySQL的模块,其应用办法和MySQLdb大致等同。

windows下载安装

  1. pip3 install pymysql

windows下载安装

  1. pip3 install pymysql

利用操作

执行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 ‘1玖二.16八.3壹.壹’ is not
allowed to connect to this MySQL server”)

想必是因为您的账号不容许从远程登录,只可以在localhost登录。只需求在localhost的那台计算机登录mysql,更改对应数据Curry的”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从ip1九二.16捌.①.12二的主机连接到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从ip1九二.16八.一.12二的主机连接到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”)])

行使操作

执行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 ‘1玖二.16八.3一.1’ is not
allowed to connect to this MySQL server”)

唯恐是因为您的账号区别意从远程登录,只幸亏localhost登录。只要求在localhost的那台电脑登录mysql,更改对应数据Curry的”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从ip1玖二.16捌.一.12贰的主机连接到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从ip19二.16八.一.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”)])

SQLAlchemy ORM

SQLAlchemy ORM

ORM介绍

指标关系映射(Object Relational
Mapping,简称O昂科拉M),是壹种程序技术,用于落到实处面向对象编制程序语言里差别门类系统的多少里面包车型客车转移。从功用上说,它事实上是成立了2个可在编制程序语言里应用的——”虚拟对象数据库”。

面向对象是从软件工程核心条件(如耦合、聚合、封装)的根基上前进起来的,而关周密据库则是从数学理论发展而来的,两套理论存在显然的界别。为了解决这几个不相称的场合,对象关系映射技术现身。

对象关联映射(Object Relational
Mapping)提供了概念性的、易于精晓的模型化数据的办法。O中华VM方法论基于多少个主导标准。简单,以最主旨的样式建立模型数据。传达性,数据库结构被任什么人都能知道的言语文书档案化。精确性,基于数据模型创设正确规范的构造。典型地,建立模型者通过采访来自那个熟谙应用程序但不纯熟数据建立模型者开发音讯模型。建立模型者必须能够用非技术公司专家能够清楚的术语在概念层次上与数据结构进行电视发表。建立模型者也不能够否以简练的单元分析消息,对样本数量进行拍卖。O福特ExplorerM专门被设计为革新那种关联。

图片 1

ORM介绍

目的关系映射(Object Relational
Mapping,简称O猎豹CS陆M),是1种程序技术,用于落到实处面向对象编制程序语言里不相同品种系统的多寡里面包车型客车变换。从功用上说,它实际是创制了三个可在编制程序语言里使用的——”虚拟对象数据库”。

面向对象是从软件工程基本尺度(如耦合、聚合、封装)的功底上升神采飞扬起的,而关周密据库则是从数学理论发展而来的,两套理论存在明显的界别。为了消除那一个不包容的情景,对象关联映射技术现身。

对象关联映射(Object Relational
Mapping)提供了概念性的、易于明白的模型化数据的格局。O安德拉M方法论基于多少个着力标准。简单,以最中央的花样建立模型数据。传达性,数据库结构被任什么人都能清楚的语言文书档案化。精确性,基于数据模型创立正确原则的构造。典型地,建立模型者通过募集来自那二个耳熟能详应用程序但不了然数据建立模型者开发消息模型。建立模型者必须能够用非技术集团专家能够知道的术语在概念层次上与数据结构举办报导。建立模型者也非得能以简练的单元分析新闻,对样本数量举办拍卖。O奇骏M专门被设计为革新那种联系。

图片 2

ORM的优点

一、隐藏了数额访问细节,”封闭”的通用数据库交互,ORM的中坚。使得通用数据库的交互变得简单易行,并且完全不用思虑SQL语句。

二、OXC60M使协会固化数据结构变得简单易行。

ORM的优点

一、隐藏了数据访问细节,”封闭”的通用数据库交互,OPRADOM的着力。使得通用数据库的相互变得简单易行,并且完全不用思考SQL语句。

二、O猎豹CS陆M使协会固化数据结构变得简单易行。

ORM的缺点

一、自动化意味着映射和关系管理,代价是就义品质。未来种种O昂CoraM框架都在尝试接纳各样方法(如LazyLoad、Cache)来减轻品质的震慑,效果比较明显。

贰、面向对象的询问语言(OQL)作为1种数据库与目的之间的交接,尽管隐藏了数额层面包车型客车事体抽象,但并不能够完全的屏蔽掉数据库层的布置性,并且确实将追加学习成本。

三、对于复杂查询,O奇骏M还是不或许。就算能够兑现,可是不值得。视图能够缓解大多数calculated
column、case、group、having、order by、exists,但是查询条件a and b and
not c and (d or e)不可能很好消除。

ORM的缺点

一、自动化意味着映射和关系管理,代价是捐躯质量。未来各类O福睿斯M框架都在品味使用各样艺术(如LazyLoad、Cache)来减轻质量的震慑,效果相比鲜明。

二、面向对象的询问语言(OQL)作为壹种数据库与对象时期的接入,固然隐藏了数据层面包车型地铁政工抽象,但并无法完全的屏蔽掉数据库层的筹划,并且确实将追加学习开支。

叁、对于复杂查询,O卡宴M依然不能够。纵然能够实现,但是不值得。视图能够缓解超过1/③calculated
column、case、group、having、order by、exists,可是查询条件a and b and
not c and (d or e)不可能很好消除。

常用的ORM框架

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

二、iBatis半自动本人写sql语句,可操作性强,小巧。

3、EclipseLink二个可扩张的帮忙JPA的OLacrosseM框架,提供强有力的缓存功用,缓存支持集群。

4、Apache OJB等等。

⑤、在Python中最显赫的O汉兰达M框架是SQLAlchemy。

常用的ORM框架

一、Hibernate全自动需求hql语句。

贰、iBatis半自动自个儿写sql语句,可操作性强,小巧。

叁、EclipseLink2个可扩张的支撑JPA的OCR-VM框架,提供有力的缓存成效,缓存辅助集群。

4、Apache OJB等等。

伍、在Python中最知名的O昂科拉M框架是SQLAlchemy。

SQLAlchemy安装

SQLAlchemy的视角是,SQL数据库的量级和天性首要于对象集合,而目的集合的抽象又重视与表和行。

图片 3

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

SQLAlchemy安装

SQLAlchemy的看法是,SQL数据库的量级和总体性首要于对象集合,而指标集合的虚幻又主要与表和行。

图片 4

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

SQLAlchemy基本采取

创立1个表。

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种创建表的主意。

  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)

第一种艺术开创的表是基于第二种方式的再封装。

利用O猎豹CS六M创造一条数据。

  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的是1个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. # 创设数量,统1提交

  2. Session.commit()

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)

率先种办法开创的表是基于第2种艺术的再封装。

使用OEscortM创设一条数据。

  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. # 成立数量,统1提交

  2. 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. 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=’高尔德en’的第1个

  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. 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=’戈尔德en’的率先个

  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

  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,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.  

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

 

多对多涉及

创造多对多关联表

  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设置编码字符集一定要在数据库访问的U逍客L上平添charset=utf8,不然数据库的总是就不是utf八的编码格式。

  1. # 扶助汉语

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

支撑中文

sqlalchemy设置编码字符集一定要在数据库访问的U福特ExplorerL上加码charset=utf捌,不然数据库的连接就不是utf八的编码格式。

  1. # 协助中文

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