python orm_python之ORM

最新推荐文章于 2023-11-19 22:52:12 发布

weixin_39601657

最新推荐文章于 2023-11-19 22:52:12 发布

阅读量197

点赞数

文章标签： python orm

pymysql

python操作数据库的基本步骤：

导入相应的python模块；

使用connect函数连接数据库，并返回一个connection对象；

通过connection对象的cursor方法，返回一个cursor对象；

通过cursor对象的execute方法执行SQL语句；

如果执行的是查询语句，通过cursor对象的fetchall语句获取返回结果；

调用cursor对象的close方法关闭cursor;

调用connection对象的close方法关闭数据库连接。

1 importpymysql2

3 conn = pymysql.Connect(host='127.0.0.1',user='admin',passwd='admin',db='test_py')4 cur =conn.cursor()5

6 reCount = cur.execute('select * from student')7 print(cur.fetchall()) #((1, 'gareth', 22, datetime.date(2011, 9, 1)),)

9 cur.close()10 conn.close()

connection类成员

begin: 开始事务

commit：提交事务

rollback: 回滚事务

cursor：返回一个cursor对象

autocommit：设置是否事务自动提交

set_character_set：设置字符集编码

get_server_info：获取数据库版本信息

注：一般不直接调用begin，commit和roolback函数，而是通过上下文管理器实现事务的提交与回滚操作。

cursor类成员对象：cursor对象表示数据库游标，用于执行SQL语句并获取SQL语句的执行结果。

execute：执行SQL语句

close：关闭游标

fetchall：获取SQL语句的所有记录

fetchmany：获取SQL语句的多条记录

fetchone：获取SQL语句的一条记录

owncount：常量，表示SQL语句的结果集中返回了多少条记录

arraysize：变量，保存了当前获取纪录的下标

lastrowid：获取最新自增ID

注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：

cursor.scroll(1,mode='relative') # 相对当前位置移动

cursor.scroll(2,mode='absolute') # 相对绝对位置移动

默认获取的数据是元祖类型，如果想要或者字典类型的数据，即：

1 conn = pymysql.Connect(host='127.0.0.1',user='admin',passwd='admin',db='test_py')2 cur = conn.cursor(cursor=pymysql.cursors.DictCursor)3

4 reCount = cur.execute('select * from student')5 print(cur.fetchall())6 """

7 [{'stu_id': 1, 'name': 'gareth', 'age': 22, 'register_data': datetime.date(2011, 9, 1)},8 {'stu_id': 3, 'name': 'Bob', 'age': 19, 'register_data': datetime.date(2012, 2, 3)},9 {'stu_id': 4, 'name': 'Bob', 'age': 19, 'register_data': datetime.date(2012, 2, 3)},10 {'stu_id': 5, 'name': 'Mary', 'age': 18, 'register_data': datetime.date(2013, 1, 2)}]11 """

13 cur.close()14 conn.close()

使用上下文管理管理数据库:

1 importpymysql2 importos3

5 def get_conn(**kwargs):6 if os.getenv('DB','MYSQL') == 'MYSQL':7 return pymysql.connect(host=kwargs.get('host','localhost'),8 user=kwargs.get('user'),9 passwd=kwargs.get('passwd'),10 port=kwargs.get('port',3306),11 db=kwargs.get('db'))12

13 defexecute_sql(conn, sql):14 with conn as cur:15 cur.execute(sql)16

17 definsert_data(conn,sname,sage,sregister):18 INSERT_FORMAT = """insert into student (name,age,register_data) values('{0}','{1}','{2}')"""

19 sql =INSERT_FORMAT.format(sname,sage,sregister)20 execute_sql(conn,sql)21

22 defmain():23 conn = get_conn(host='127.0.0.1',24 user='admin',25 passwd='admin',26 port=3306,27 db='test_py')28

29 try:30 insert_data(conn,'Bob',19,'2012-02-03')31 insert_data(conn,'Mary',18,'2013-01-02')32

33 with conn as cur:34 cur.execute('select * from student')35 rows =cur.fetchall()36 for row inrows:37 print(row)38 finally:39 ifconn:40 conn.close()41

42 if __name__ == '__main__':43 main()

View Code

上面例子中如果values('{0}','{1}','{2}')的引号去掉，则会报错：pymysql.err.InternalError: (1054, "Unknown column 'jack' in 'field list'")

1 cur.execute("insert into student (name,age,register_data) values('jack',12,'2012-02-03')")2

3 INSERT_FORMAT = """insert into student (name,age,register_data) values('{0}','{1}','{2}')"""

批量插入：

1 cur =conn.cursor()2

3 cur.executemany("insert into student (name,age,register_data) values(%s,%s,%s)",4 [('jack',12,'2012-02-03'),('Bob',12,'2012-02-03')] )

ORM

orm英文全称object relational mapping，即对象映射关系程序，简单来说我们类似python这种面向对象的程序来说一切皆对象，但是我们使用的数据库却都是关系型的，为了保证一致的使用习惯，通过orm将编程语言的对象模型和数据库的关系模型建立映射关系，这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了，而不用直接使用sql语言。

优点：

隐藏了数据访问细节，“封闭”的通用数据库交互，ORM的核心。他使得我们的通用数据库交互变得简单易行，并且完全不用考虑该死的SQL语句。快速开发，由此而来。

ORM使我们构造固化数据结构变得简单易行。

缺点：

自动化意味着映射和关联管理，代价是牺牲性能

sqlalchemy

SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。

SQLAlchemy本身无法操作数据库，其必须依赖pymsql等第三方插件，Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：

1 MySQL-Python2 mysql+mysqldb://:@[:]/

4 pymysql5 mysql+pymysql://:@/[?]6

7 MySQL-Connector8 mysql+mysqlconnector://:@[:]/

10 cx_Oracle11 oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]12

13 更多详见：http://docs.sqlalchemy.org/en/latest/dialects/index.html

dialect

使用 Engine/ConnectionPooling/Dialect 进行数据库操作，Engine使用ConnectionPooling连接数据库，然后再通过Dialect执行SQL语句。

1 from sqlalchemy importcreate_engine2

3 engine = create_engine('mysql+pymysql://user_account:password@127.0.0.1/test', max_overflow=5)4

5 cur = engine.execute("insert into student(name,age,register_date) values('Jack',20,'2018-01-02')")6 print(cur.lastrowid)7 #1

9 cur = engine.execute("insert into student(name,age,register_date) values(%s,%s,%s)",10 [('Gareth',18,'2018-07-02'),('Mar',17,'2017-12-02')])11

12 cur = engine.execute("insert into student(name,age,register_date) values(%(name)s,%(age)s,%(register_date)s)",13 name = 'Ker',age = 21,register_date='2016-09-01')14 cur = engine.execute('select * from student')15 print(cur.fetchone())16 #(1, 'Jack', 20, datetime.date(2018, 1, 2))

17 print(cur.fetchmany(2))18 #[(2, 'Gareth', 18, datetime.date(2018, 7, 2)), (3, 'Mar', 17, datetime.date(2017, 12, 2))]

19 print(cur.fetchall())20 #[(4, 'Ker', 21, datetime.date(2016, 9, 1))]

ORM使用

使用 Engine/ConnectionPooling/Dialect 进行数据库操作，Engine使用ConnectionPooling连接数据库，然后再通过Dialect执行SQL语句。

创建表：

1 from sqlalchemy.ext.declarative importdeclarative_base2 from sqlalchemy importColumn,Integer,String,ForeignKey, UniqueConstraint,Index3 from sqlalchemy.orm importsessionmaker, relationship4 from sqlalchemy importcreate_engine5

7 engine = create_engine("mysql+pymysql://user_account:password@127.0.0.1:3306/test", max_overflow = 5)8 Base =declarative_base() # 生成一个基类9

10 classClasses(Base):11 __tablename__='classes'

12 id = Column(Integer, primary_key=True)13 name = Column(String(32))14

15 Base.metadata.create_all(engine) #创建表结构

创建与数据库的会话session:

1 session_class = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例

2 session = session_class() #生成session实例

4 cla_obj = Classes(name='python099') #生成你要创建的数据对象

6 session.add(cla_obj) #把要创建的数据对象添加到这个session里，一会统一创建

8 session.commit() #现此才统一提交，创建数据

查询

sqlalchemy把返回的数据映射成一个对象，调用每个字段可以像调用对象属性一样。

1 session_class = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例

2 session = session_class() #生成session实例

5 my_classes =session.query(Classes)6 print(my_classes)7 #"SELECT classes.id AS classes_id, classes.name AS classes_name FROM classes"

9 my_classes =session.query(Classes).first()10 print(my_classes) #返回内存对象地址

11 #<__main__.Classes object at 0x000001832CE1A4E0>

13 my_classes =session.query(Classes).first()14 print('id:',my_classes.id,'name:',my_classes.name)15 #id: 1 name: python011

17 my_classes =session.query(Classes)18 for classes inmy_classes:19 #print('id: ',classes.id,'name:',classes.name)

20 #id: 1 name: python011

21 #id: 2 name: python016

22 #id: 3 name: linux

把内存对象地址转化为可读性数据，除了调用字段还可以使用__repr__(self) 函数。

1 def __repr__(self):2 return "Classes(id=%s,name=%s)"%(self.id, self.name)3

4 my_classes =session.query(Classes)5 print(my_classes) #SELECT classes.id AS classes_id, classes.name AS classes_name FROM classes

7 print(my_classes[1]) #Classes(id=2,name=python016)

9 for classes inmy_classes:10 print(classes)11 #Classes(id=1,name=python011)

12 #Classes(id=2,name=python016)

13 #Classes(id=3,name=linux)

过滤

1 print(session.query(Classes).filter(Classes.name.in_(['mysql','python_sqlalchemy'])).all())2

3 session.query(Classes).filter_by(name='python011').first()

多条件查询：相当于id > 1 and id <4

1 obj = session.query(Classes).filter(Classes.id>1).filter(Classes.id<4).all()2 print(obj)3 #[Classes(id=2,name=python016), Classes(id=3,name=linux)]

获取所有数据

1 print(session.query(Classes).all())2 #[Classes(id=1,name=python_mysql), Classes(id=2,name=python016), Classes(id=3,name=linux)]

3 print(session.query(Classes.id,Classes.name).all())4 #[(1, 'python_mysql'), (2, 'python016'), (3, 'linux')]

查询语法

1 Common Filter Operators2

3 Here’s a rundown of some of the most common operators used infilter():4

5 equals:6

7 query.filter(User.name == 'ed')8 notequals:9

10 query.filter(User.name != 'ed')11 LIKE:12

13 query.filter(User.name.like('%ed%'))14

15 IN:16

17 NOT IN:18 query.filter(~User.name.in_(['ed', 'wendy', 'jack']))19

20 IS NULL:21

22 IS NOT NULL:23

24 AND:25 2.1. ObjectRelationalTutorial 17

27 query.filter(User.name.in_(['ed', 'wendy', 'jack']))28 #works with query objects too:

29 query.filter(User.name.in_( session.query(User.name).filter(User.name.like('%ed%'))30

31 ))32

33 query.filter(User.name ==None)34 #alternatively, if pep8/linters are a concern

35 query.filter(User.name.is_(None))36 query.filter(User.name !=None)37 #alternatively, if pep8/linters are a concern

38 query.filter(User.name.isnot(None))39 SQLAlchemy Documentation, Release 1.1.0b140

41 #use and_()

43 from sqlalchemy importand_44 query.filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))45

46 #or send multiple expressions to .filter()

47 query.filter(User.name == 'ed', User.fullname == 'Ed Jones')48 #or chain multiple filter()/filter_by() calls

49 query.filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')50 Note: Makesureyouuseand_()andnotthePythonandoperator! • OR:51

52 Note: Makesureyouuseor_()andnotthePythonoroperator! • MATCH:53

54 query.filter(User.name.match('wendy'))55 Note: match() uses a database-specific MATCH or CONTAINS f

View Code

分组和统计

1 print(session.query(Classes).filter(Classes.name.like('p%')).count())2 #2

3 print(session.query(Classes).count())4 #2

6 from sqlalchemy importfunc7 print(session.query(Classes).all())8 #[Classes(id=1,name=python_mysql), Classes(id=2,name=python016), Classes(id=3,name=linux), Classes(id=5,name=linux)]

10 print(session.query(func.count(Classes.name),Classes.name).group_by(Classes.name).all())11 #[(1, 'python_mysql'), (1, 'python016'), (2, 'linux')]

修改

使用修改，可以加个判断，如果没有查询到需要修改的信息则跳过，否则容易出现异常 AttributeError 。

1 my_classes = session.query(Classes).filter_by(name='python011').first()2

3 my_classes.name = 'python_mysql022'

4 session.commit()5 my_classes =session.query(Classes)6 for classes inmy_classes:7 print(classes)8

9 #Classes(id=1,name=python_mysql)

10 #Classes(id=2,name=python016)

11 #Classes(id=3,name=linux)

异常：

1 Traceback (most recent call last):2 File "C:/D/personal_data/workspace/eleven/mysql_study/class_study/orm_01.py", line 64, in

3 my_classes.name = 'python_mysql022'

4 AttributeError: 'NoneType' object has no attribute 'name'

回滚

1 my_classes = session.query(Classes).filter_by(id=2).first()2 my_classes.name = 'python_sqlalchemy'

4 fake_classes = Classes(name='mysql')5 session.add(fake_classes)6

7 print(session.query(Classes).filter(Classes.name.in_(['mysql','python_sqlalchemy'])).all())8 #[Classes(id=2,name=python_sqlalchemy), Classes(id=4,name=mysql)]

9 session.rollback()10

11 print(session.query(Classes).filter(Classes.name.in_(['mysql','python_sqlalchemy'])).all())12 #[]

程序：

7 engine = create_engine("mysql+pymysql://jiawenyx:intel@3117@127.0.0.1:3306/test", max_overflow = 5)8 Base =declarative_base()9

10 classClasses(Base):11 __tablename__='classes'

12 id = Column(Integer, primary_key=True)13 name = Column(String(32))14

15 def __repr__(self):16 return "Classes(id=%s,name=%s)"%(self.id, self.name)17

18 Base.metadata.create_all(engine) #创建表结构

20 session_class = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例

21 session = session_class() #生成session实例

22 #23 #cla_obj = Classes(name='python016') #生成你要创建的数据对象

24 #cla_obj2 = Classes(name='linux')

25 #26 #session.add(cla_obj) # 把要创建的数据对象添加到这个session里，一会统一创建

27 #session.add(cla_obj2)

28 #session.commit() # 现此才统一提交，创建数据

31 #******************************** 查询 ***************************************

33 my_classes =session.query(Classes)34 #print(my_classes)

35 #"SELECT classes.id AS classes_id, classes.name AS classes_name FROM classes"

36 my_classes =session.query(Classes).first()37 #print(my_classes)

38 #<__main__.Classes object at 0x000001832CE1A4E0>

39 my_classes =session.query(Classes).first()40 #print('id: ',my_classes.id,'name:',my_classes.name)

41 #id: 1 name: python011

43 my_classes =session.query(Classes)44 #for classes in my_classes:

45 #print('id: ',classes.id,'name:',classes.name)

46 #id: 1 name: python011

47 #id: 2 name: python016

48 #id: 3 name: linux

51 #my_classes = session.query(Classes)

52 #print(my_classes) # SELECT classes.id AS classes_id, classes.name AS classes_name FROM classes

53 #print(my_classes[1]) # Classes(id=2,name=python016)

54 #for classes in my_classes:

55 #print(classes)

56 ## Classes(id=1,name=python011)

57 ## Classes(id=2,name=python016)

58 ## Classes(id=3,name=linux)

60 #****************************************** 修改 ********************************************

62 my_classes = session.query(Classes).filter_by(name='python011').first()63

64 #my_classes.name = 'python_mysql022'

65 #session.commit()

66 #my_classes = session.query(Classes)

67 #for classes in my_classes:

68 #print(classes)

70 #Classes(id=1,name=python_mysql)

71 #Classes(id=2,name=python016)

72 #Classes(id=3,name=linux)

74 #******************************* 回滚 ********************************

76 my_classes = session.query(Classes).filter_by(id=2).first()77 my_classes.name = 'python_sqlalchemy'

79 fake_classes = Classes(name='mysql')80 session.add(fake_classes)81

82 #print(session.query(Classes).filter(Classes.name.in_(['mysql','python_sqlalchemy'])).all())

83 #[Classes(id=2,name=python_sqlalchemy), Classes(id=4,name=mysql)]

84 session.rollback()85

86 #print(session.query(Classes).filter(Classes.name.in_(['mysql','python_sqlalchemy'])).all())

89 #**********************获取所有数据*****************************

91 #print(session.query(Classes).all())

92 ## [Classes(id=1,name=python_mysql), Classes(id=2,name=python016), Classes(id=3,name=linux)]

93 #print(session.query(Classes.id,Classes.name).all())

94 ## [(1, 'python_mysql'), (2, 'python016'), (3, 'linux')]

95 #96 ## ********************** 多条件查询 ****************************

97 #98 #obj = session.query(Classes).filter(Classes.id>1).filter(Classes.id<4).all()

99 #print(obj)

100 ## [Classes(id=2,name=python016), Classes(id=3,name=linux)]

101 #102 ## ********************** 统计和分组 ****************************

103 #104 print(session.query(Classes).filter(Classes.name.like('p%')).count())105 #2

106 print(session.query(Classes).count())107 #2

108

109 from sqlalchemy importfunc110 print(session.query(Classes).all())111 #[Classes(id=1,name=python_mysql), Classes(id=2,name=python016), Classes(id=3,name=linux), Classes(id=5,name=linux)]

112 print(session.query(func.count(Classes.name),Classes.name).group_by(Classes.name).all())113 #[(1, 'python_mysql'), (1, 'python016'), (2, 'linux')]

程序

外键

1 from sqlalchemy importColumn,Integer,String2 from sqlalchemy importForeignKey3 from sqlalchemy.orm importrelationship4 from sqlalchemy.ext.declarative importdeclarative_base5 from sqlalchemy importcreate_engine6 from sqlalchemy.orm importsessionmaker7

8 engine = create_engine("mysql+pymysql://user:password@127.0.0.1:3306/test", max_overflow = 5)9

10 Base =declarative_base()11

12 classCustomer(Base):13 __tablename__='customer'

19 address = relationship('Address', backref='customer')20 #shipping_address = relationship('Address')

21 def __repr__(self):22 return "id:%s,name:%s,billing_address_id:%s,shipping_address_id:%s"\23 %(self.id,self.name,self.billing_address_id,self.shipping_address_id)24

25 classAddress(Base):26 __tablename__ = 'address'

27 id = Column(Integer,primary_key=True)28 stress = Column(String(64))29 city = Column(String(64))30 state = Column(String(64))31

32 def __repr__(self):33 return 'id:%s,stress:%s,city:%s,state:%s' %(self.id,self.stress,self.city,self.state)34

35 Base.metadata.create_all(engine) #创建表结构

37 session_class =sessionmaker(engine)38 session =session_class()39

40 #cus1 = Customer(name='gareth',billing_address_id=1, shipping_address_id = 2)

41 #cus2 = Customer(name = 'Jack',billing_address_id=2, shipping_address_id = 3)

42 #cus3 = Customer(name ='Mar',billing_address_id=3, shipping_address_id = 3)

43 #44 #add_obj = Address(stress = 'shuangxing', city = 'shunyi', state= 'Beijing')

45 #add_obj1 = Address(stress = 'tiantongyuan', city = 'changping', state = 'Beijing')

46 #add_obj2 = Address(stress = 'bayiqiao', city = 'nanchang', state = 'jiangxi')

47 #session.add_all([add_obj,add_obj1,add_obj2,cus1,cus2,cus3])

48 #49 #session.commit()

51 obj =session.query(Address).first()52 #print(obj) # id:1,stress:shuangxing,city:shunyi,state:Beijing

53 #print(obj.customer) # [id:1,name:gareth,billing_address_id:1,shipping_address_id:2]

54 #for i in obj.customer:

55 #print(i) # id:1,name:gareth,billing_address_id:1,shipping_address_id:2

57 obj =session.query(Customer).first()58 #print(obj) # id:1,name:gareth,billing_address_id:1,shipping_address_id:2

59 #print(obj.address) # id:1,stress:shuangxing,city:shunyi,state:Beijing

外键

注：

定义外键： billing_address_id = Column(Integer,ForeignKey('address.id'))

relationship：address = relationship('Address', backref='customer')，通过Address表可以反查customer信息，可以在Customer表中通过address字段，查找Adress表中信息；从而两张表互查。

1 obj =session.query(Customer).first()2 print(obj) #id:1,name:gareth,billing_address_id:1,shipping_address_id:2

3 print(obj.address) #id:1,stress:shuangxing,city:shunyi,state:Beijing

1 obj =session.query(Address).first()2 print(obj) #id:1,stress:shuangxing,city:shunyi,state:Beijing

3 print(obj.customer) #[id:1,name:gareth,billing_address_id:1,shipping_address_id:2]

4 for i inobj.customer:5 print(i) #id:1,name:gareth,billing_address_id:1,shipping_address_id:2

多外键关联

8 engine = create_engine("mysql+pymysql://user:password@127.0.0.1:3306/test", max_overflow = 5)9

10 Base =declarative_base()11

12 classCustomer(Base):13 __tablename__='customer'

14 id = Column(Integer,primary_key=True)15 name = Column(String(32))16 billing_address_id = Column(Integer,ForeignKey('address.id'))17 shipping_address_id = Column(Integer,ForeignKey('address.id'))18 address = relationship('Address')19 shipping_address = relationship('Address')20 def __repr__(self):21 return "id:%s,name:%s,billing_address_id:%s,shipping_address_id:%s"\22 %(self.id,self.name,self.billing_address_id,self.shipping_address_id)23

24 classAddress(Base):25 __tablename__ = 'address'

26 id = Column(Integer,primary_key=True)27 stress = Column(String(64))28 city = Column(String(64))29 state = Column(String(64))30

31 def __repr__(self):32 return 'id:%s,stress:%s,city:%s,state:%s' %(self.id,self.stress,self.city,self.state)33

34 Base.metadata.create_all(engine) #创建表结构

36 session_class =sessionmaker(engine)37 session = session_class()

创建表结构OK，但Address表中插入数据时会报下面的错

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Customer.address -there are multiple foreign key paths linking the tables. Specify the'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

修改方法：

1 classCustomer(Base):2 __tablename__='customer'

3 id = Column(Integer,primary_key=True)4 name = Column(String(32))5 billing_address_id = Column(Integer,ForeignKey('address.id'))6 shipping_address_id = Column(Integer,ForeignKey('address.id'))7 billing_address = relationship('Address',foreign_keys=[billing_address_id])8 shipping_address = relationship('Address',foreign_keys=[shipping_address_id])

处理中文

sqlalchemy设置编码字符集一定要在数据库访问的URL上增加charset=utf8，否则数据库的连接就不是utf8的编码格式

1 eng = create_engine('mysql://root:root@localhost:3306/test2?charset=utf8',echo=True)

多对多关系

ORM_API

1 from sqlalchemy importColumn,Integer,String,Date,Table2 from sqlalchemy importForeignKey3 from sqlalchemy.orm importrelationship4 from sqlalchemy.ext.declarative importdeclarative_base5 from sqlalchemy importcreate_engine6 from sqlalchemy.orm importsessionmaker7

8 engine = create_engine("mysql+pymysql://admin:admin@localhost:3306/test_py?charset=utf8", max_overflow = 5)9

10 Base =declarative_base()11

12 book_m2m_author = Table('book_m2m_author', Base.metadata,13 Column('book_id',Integer,ForeignKey('books.id')),14 Column('author_id',Integer,ForeignKey('authors.id')),15 )16 classBook(Base):17 __tablename__='books'

18 id = Column(Integer,primary_key=True)19 name = Column(String(64))20 pub_data =Column(Date)21 authors = relationship('Author',secondary=book_m2m_author, backref='books')22

23 def __repr__(self):24 returnself.name25

26 classAuthor(Base):27 __tablename__='authors'

28 id = Column(Integer,primary_key=True)29 name = Column(String(32))30

31 def __repr__(self):32 returnself.name33

34 Base.metadata.create_all(engine) #创建表结构

session:

1 from sqlalchemy.orm importsessionmaker2 importbook_orm3

5 Session =sessionmaker(book_orm.engine)6 session =Session()7

8 b1 = book_orm.Book(name='三国演义')9 b2 = book_orm.Book(name='水浒传')10 b3 = book_orm.Book(name='红楼梦')11 b4 = book_orm.Book(name='西游记')12

13 a1 = book_orm.Author(name='罗贯中')14 a2= book_orm.Author(name='曹雪芹')15 a3= book_orm.Author(name='吴承恩')16 a4= book_orm.Author(name='施耐庵')17 a5= book_orm.Author(name='Gareth')18 a6= book_orm.Author(name='Yu')19 a7= book_orm.Author(name='caven')20

21 b1.authors =[a1,a6]22 b2.authors =[a4,a5,a7]23 b3.authors =[a2]24 b4.authors =[a3]25

26 session.add_all([a1,a2,a3,a4,a5,a6,a7,b1,b2,b3,b4])27 session.commit()

table:

1 mysql>show tables;2 +-------------------+

3 | Tables_in_test_py |

4 +-------------------+

5 | authors |

6 | book_m2m_author |

7 | books |

8 | classes |

9 | student |

10 | user |

11 +-------------------+

12 6 rows in set (0.03sec)13

15 mysql> select * fromauthors;16 +----+--------+

17 | id | name |

18 +----+--------+

19 | 1 | 罗贯中 |

20 | 2 | Yu |

21 | 3 | 曹雪芹 |

22 | 4 | 吴承恩 |

23 | 5 | 施耐庵 |

24 | 6 | Gareth |

25 | 7 | caven |

26 +----+--------+

27 7 rows in set (0.03sec)28

30 mysql> select * fromauthors;31 +----+--------+

32 | id | name |

33 +----+--------+

34 | 1 | 罗贯中 |

35 | 2 | Yu |

36 | 3 | 曹雪芹 |

37 | 4 | 吴承恩 |

38 | 5 | 施耐庵 |

39 | 6 | Gareth |

40 | 7 | caven |

41 +----+--------+

42 7 rows in set (0.03sec)43

45 mysql> select * frombook_m2m_author;46 +---------+-----------+

47 | book_id | author_id |

48 +---------+-----------+

49 | 4 | 5 |

50 | 4 | 6 |

51 | 1 | 1 |

52 | 1 | 2 |

53 | 2 | 3 |

54 | 4 | 7 |

55 | 3 | 4 |

56 +---------+-----------+

57 7 rows in set (0.00 sec)

tables

查询：

1 print("通过book table查询关联的作者")2 book_obj = session.query(book_orm.Book).filter_by(name="三国演义").all()3 print(book_obj) #[三国演义]

4 book_obj = session.query(book_orm.Book).filter_by(name="三国演义").first()5 print(book_obj.name, book_obj.authors)6 #三国演义 [罗贯中, 曹雪芹]

7 print("通过author table查询关联的书")8 author_obj = session.query(book_orm.Author).filter_by(name="曹雪芹").first()9 print(author_obj.name, author_obj.books)

直接删除作者：

1 author_obj = session.query(book_orm.Author).filter(book_orm.Author.id>7).all()2 print(author_obj)3

4 for i inauthor_obj:5 session.delete(i)6 session.commit()7 print(session.query(book_orm.Author).all())8

9 #[罗贯中, 曹雪芹, 施耐庵, Yu, 罗贯中, 曹雪芹, 施耐庵, Yu, 罗贯中, 曹雪芹, 施耐庵, Yu, 罗贯中, 曹雪芹, 施耐庵, Yu, 罗贯中, 罗贯中, 曹雪芹, 罗贯中, 曹雪芹]

10 #[罗贯中, Yu, 曹雪芹, 吴承恩, 施耐庵, Gareth, caven]

通过书删除作者：

1 author_obj = session.query(book_orm.Author).filter_by(name="曹雪芹").first()2 book_obj = session.query(book_orm.Book).filter_by(name="红楼梦").first()3 book_obj.authors.remove(author_obj)4 session.commit()

删除数据时不用管boo_m2m_authors　， sqlalchemy会自动帮你把对应的数据删除。

weixin_39601657

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
python orm_python之ORM

pymysqlpython操作数据库的基本步骤：导入相应的python模块；使用connect函数连接数据库，并返回一个connection对象；通过connection对象的cursor方法，返回一个cursor对象；通过cursor对象的execute方法执行SQL语句；如果执行的是查询语句，通过cursor对象的fetchall语句获取返回结果；调用cursor对象的close方法关闭cur...
复制链接

扫一扫