SqlAlchemy使用

Date:2016-03-27
Title:20.Python笔记之SqlAlchemy使用
Tags:python
Category:Python

作者：刘耀
博客：www.liuyao.me

一、SQLAlchemy

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

 

ORM方法论基于三个核心原则：

简单：以最基本的形式建模数据。

传达性：数据库结构被任何人都能理解的语言文档化。

精确性：基于数据模型创建正确标准化了的结构。

Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：

'数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'

MySQL-Python

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

pymysql

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

MySQL-Connector

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

cx_Oracle

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

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

注：

 
  1. 2.7版本使用mysqldb
  2. 3.5版本使用pymysql
  3. 请用pip或者源码包安装，确保环境可以正常使用。**
  4. 确保远程数据库服务器可以正常使用，并且拥有全新可以远程登录，
  5. 例如：
  6. 登录数据库：mysql -uroot -p
  7. 创建数据库：create database liuyao;
  8. 授权库：grant all on liuyao.* to liuyao@"%" identified by 'liuyao';
  9. 更新：flush privileges;
 

1.基本操作：

1）链接数据库：create_engine()

 
  1. engine = create_engine("mysql+mysqldb://liuyao:liuyao@121.42.195.15:3306/liuyao", max_overflow=5)
  2.  
  3. create_engine() 会返回一个数据库引擎，
  4. mysql+mysqldb”指定了使用 MySQL-Python 来连接，
  5. 使用用户名‘liuyao’和密码‘liuyao’来链接数据库
  6. 121.42.195.15是数据库链接地址可以是localhost，127.0.0.1
  7. ‘liuyao’是数据库名
  8. max_overflow是最大连接数
  9. 其他方法：
  10. “charset”指定了连接时使用的字符集（可省略）=utf8
  11. echo 参数为 True 时，会显示每条执行的 SQL 语句，生产环境下可关闭。
 

2）字段和数据类型及操作方法

在sqlalchemy.schema包里有数据库关系的描述，列举几个最常用的：

字段：Column

索引：Index

表：Table

数据类型在sqlalchemy.types包，列举几个最常用的:

二进制：BIGINT

布尔：BOOLEAN

字符：CHAR

可变字符：VARCHAR

日期：DATETIME

其他方法 execute,update,insert,select,delete,join等 自行补脑

3)创建表结构

使用 Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 进行数据库操作。Engine使用Schema Type创建一个特定的结构对象，之后通过SQL Expression Language将该对象转换成SQL语句，然后通过 ConnectionPooling 连接数据库，再然后通过 Dialect 执行SQL，并获取结果。

 
  1. from sqlalchemy import create_engine, Table, Column, Integer, /
  2. String, MetaData, ForeignKey
  3. import MySQLdb
  4. #创建数据库连接
  5. engine = create_engine("mysql+mysqldb://liuyao:liuyao@121.42.195.15:3306/liuyao", max_overflow=5)
  6. # 获取元数据
  7. metadata = MetaData()
  8. # 定义表
  9. user = Table('user', metadata,
  10. Column('id', Integer, primary_key=True),
  11. Column('name', String(20)),
  12. )
  13.  
  14. color = Table('color', metadata,
  15. Column('id', Integer, primary_key=True),
  16. Column('name', String(20)),
  17. )
  18. # 创建数据表，如果数据表存在，则忽视
  19. metadata.create_all(engine)
  20. 结果：
  21. mysql> show tables;
  22. +------------------+
  23. | Tables_in_liuyao |
  24. +------------------+
  25. | color |
  26. | user |
  27. +------------------+
  28. 2 rows in set (0.00 sec)
 

3）插入一条数据

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

 
  1. #!/usr/bin/env python3
  2. #coding:utf8
  3. from sqlalchemy import create_engine
  4.  
  5. engine = create_engine("mysql+mysqldb://liuyao:liuyao@121.42.195.15:3306/
  6. liuyao", max_overflow=5)
  7. engine.execute(
  8. "INSERT INTO liuyao.color(id, name) VALUES ('1', 'liuyao');"
  9. )
  10. result = engine.execute('select * from color')
  11. print(result.fetchall())
  12. 结果：
  13. [(1L, 'liuyao'), (2L, 'v1')]
 

4) 增删改查

先创建数据库

 
  1. #!/usr/bin/env python3
  2. #coding:utf8
  3. from sqlalchemy import create_engine, Table, Column, Integer, String,MetaData, ForeignKey
  4. metadata = MetaData()
  5. #创建数据库引擎
  6. engine = create_engine("mysql+mysqldb://liuyao:liuyao@121.42.195.15:3306/
  7. liuyao", max_overflow=5)
  8. conn = engine.connect()
  9. #创建一个表叫做user 在liuyao库里
  10. user = Table('user', metadata,
  11. Column('id', Integer, primary_key=True),
  12. Column('name', String(20)),
  13. )
  14. color = Table('color', metadata,
  15. Column('id', Integer, primary_key=True),
  16. Column('name', String(20)),
  17. )
  18. metadata.create_all(engine)
 

增加

 
  1. # 创建SQL语句，INSERT INTO "user" (id, name) VALUES (:id, :name)
  2. conn.execute(user.insert(),{'id':7,'name':'seven'})
  3. conn.close()
  4. # 或者按照下面的方式创建
  5. # sql = user.insert().values(id=123, name='wu')
  6. # conn.execute(sql)
  7. # conn.close()
  8. 结果：
  9. mysql> show tables;
  10. +------------------+
  11. | Tables_in_liuyao |
  12. +------------------+
  13. | color |
  14. | user |
  15. +------------------+
  16. 2 rows in set (0.00 sec)
  17.  
  18. mysql> select * from user;
  19. +----+-------+
  20. | id | name |
  21. +----+-------+
  22. | 7 | seven |
  23. +----+-------+
  24. 1 row in set (0.00 sec)
 

删除

 
  1. #删除一条user表里的 条件是id大于1的
  2. sql = user.delete().where(user.c.id > 1)
  3. #执行
  4. conn.execute(sql)
  5. #关闭链接
  6. conn.close()
  7. 结果：
  8. mysql> select * from user;
  9. Empty set (0.00 sec)
  10.  
  11. mysql>
  12. #因表里只有一条数据，删除之后，没有任何数据存在
 

修改/更新

 
  1. 先创建几条数据步骤略
  2. 显示如下：
  3. mysql> select * from user;
  4. +----+--------+
  5. | id | name |
  6. +----+--------+
  7. | 1 | liuyao |
  8. | 2 | liuyao |
  9. | 3 | yaoyao |
  10. | 4 | yao |
  11. +----+--------+
  12. 4 rows in set (0.00 sec)
  13. #更新
  14. #把名字为liuyao的修改为no1
  15. sql = user.update().where(user.c.name == 'liuyao').values(name='no1')
  16. conn.execute(sql)
  17. conn.close()
  18. 结果：
  19. mysql> select * from user;
  20. +----+--------+
  21. | id | name |
  22. +----+--------+
  23. | 1 | no1 |
  24. | 2 | no1 |
  25. | 3 | yaoyao |
  26. | 4 | yao |
  27. +----+--------+
  28. 4 rows in set (0.00 sec)
 

查询

注：请导入查询模块

 
  1. from sqlalchemy import select 其他模块同上
  2. #查询user表里的内容
  3. sql = select([user, ])
  4. res =conn.execute(sql)
  5. print res.fetchall()
  6. conn.close()
  7. 结果：
  8. [(1L, 'no1'), (2L, 'no1'), (3L, 'yaoyao'), (4L, 'yao')]
  9. #查询user表下的id
  10. sql = select([user.c.id, ])
  11. res =conn.execute(sql)
  12. print res.fetchall()
  13. conn.close()
  14. 结果:
  15. [(1L,), (2L,), (3L,), (4L,)]
  16. #查询user表和color表的name，条件是user表的id1=color的id1
  17. sql = select([user.c.name, color.c.name]).where(user.c.id==color.c.id)
  18. 结果：
  19. [('no1', 'liuyao'), ('no1', 'v1')]
  20. #查询user表的name，并按照条件排序
  21. #按照名字排序
  22. sql = select([user.c.name]).order_by(user.c.name)
  23. res =conn.execute(sql)
  24. print res.fetchall()
  25. conn.close()
  26. 结果：
  27. [('no1',), ('no1',), ('yao',), ('yaoyao',)]
  28. #按照id排序
  29. sql = select([user.c.name]).order_by(user.c.id)
  30. res =conn.execute(sql)
  31. print res.fetchall()
  32. conn.close()
  33. 结果：
  34. [('no1',), ('no1',), ('yaoyao',), ('yao',)]
  35. #查询user表的name，并按照条件分组
  36. sql = select([user]).group_by(user.c.name)
  37. res =conn.execute(sql)
  38. print res.fetchall()
  39. conn.close()
  40. 结果：
  41. [(1L, 'no1'), (4L, 'yao'), (3L, 'yaoyao')]
  42. #按照id
  43. 结果：
  44. [(1L, 'no1'), (2L, 'no1'), (3L, 'yaoyao'), (4L, 'yao')]
 

5）继承SqlORM类来操作数据库

 
  1. #!/usr/bin/env python3
  2. #coding:utf8
  3. from sqlalchemy import create_engine
  4. from sqlalchemy.ext.declarative import declarative_base
  5. from sqlalchemy import Column, Integer, String
  6. from sqlalchemy.orm import sessionmaker
  7. Base = declarative_base() #生成一个SqlORM 基类
  8. engine = create_engine("mysql+mysqldb://liuyao:liuyao@121.42.195.15:3306/liuyao",echo=True)
  9. #echo如果为True，那么当他执行整个代码的数据库的时候会显示过程
  10. #创建一个类继承Base基类
  11. class Host(Base):
  12. #表名为hosts
  13. __tablename__ = 'hosts'
  14. #表结构
  15. #primary_key等于主键
  16. #unique唯一
  17. #nullable非空
  18. id = Column(Integer,primary_key=True,autoincrement=True)
  19. hostname = Column(String(64),unique=True,nullable=False)
  20. ip_addr = Column(String(128),unique=True,nullable=False)
  21. port = Column(Integer,default=22)
  22.  
  23. Base.metadata.create_all(engine) #创建所有表结构
  24.  
  25. if __name__ == '__main__':
  26. SessionCls = sessionmaker(bind=engine)
  27. #bind绑定
  28. #创建与数据库的会话session class
  29. #注意,这里返回给session的是个class,不是实例
  30. session = SessionCls()
  31. #插入字段
  32. h1 = Host(hostname='qd115',ip_addr='115.29.51.8')
  33. h2 = Host(hostname='ubuntu',ip_addr='139.129.5.191',port=80)
  34. h3 = Host(hostname='mysql',ip_addr='121.42.195.15',port=3306)
  35. #添加一个
  36. #session.add(h3)
  37. #可以添加多个字段
  38. session.add_all( [h1,h2,h3])
  39. #修改字段名字,只要没提交,此时修改也没问题
  40. #h2.hostname = 'ubuntu_test'
  41. #支持数据回滚
  42. #session.rollback()
  43. #提交
  44. session.commit()
  45.  
  46. 结果：
  47. mysql> select * from hosts;
  48. +----+----------+---------------+------+
  49. | id | hostname | ip_addr | port |
  50. +----+----------+---------------+------+
  51. | 1 | qd115 | 115.29.51.8 | 22 |
  52. | 2 | ubuntu | 139.129.5.191 | 80 |
  53. | 4 | mysql | 121.42.195.15 | 3306 |
  54. +----+----------+---------------+------+
  55. 3 rows in set (0.00 sec)
 

注：SQLAlchemy无法修改表结构，如果需要可以使用SQLAlchemy开发者开源的另外一个软件Alembic来完成。

6.继承类式增删改查：

使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有组件对数据进行操作。根据类创建对象，对象转换成SQL，执行SQL。

Query对象可以返回可迭代的值(iterator value)，然后我们可以通过for in来查询。不过Query对象的all()、one()以及first()方法将返回非迭代值(non-iterator value)，比如说all()返回的是一个列表first()方法限制并仅作为标量返回结果集的第一条记录：

1)先创建相关数据库

 
  1. #!/usr/bin/env python
  2. # -*- coding:utf-8 -*-
  3. from sqlalchemy.ext.declarative import declarative_base
  4. from sqlalchemy import Column, Integer, String
  5. from sqlalchemy.orm import sessionmaker
  6. from sqlalchemy import create_engine
  7. #创建数据库
  8. engine = create_engine("mysql+mysqldb://liuyao:liuyao@121.42.195.15:3306/liuyao", max_overflow=5)
  9. #生成一个SqlORM 基类
  10. Base = declarative_base()
  11. #定义表结构
  12. class User(Base):
  13. #表名
  14. __tablename__ = 'users'
  15. #定义id,主键唯一,
  16. id = Column(Integer, primary_key=True)
  17. name = Column(String(50))
  18. #寻找Base的所有子类，按照子类的结构在数据库中生成对应的数据表信息
  19. Base.metadata.create_all(engine)
  20. #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
  21. Session = sessionmaker(bind=engine)
  22. session = Session()
  23. #获取session，然后把对象添加到session，
  24. #最后提交并关闭。Session对象可视为当前数据库连接。
 

2.增加

 
  1. ########### 增 ##########
  2. #定义一个字段
  3. zengjia = User(id=2, name='sbliuyao')
  4. #添加字段
  5. session.add(zengjia)
  6. #添加多个字段
  7. session.add_all([
  8. User(id=3, name='sbyao'),
  9. User(id=4, name='liuyao')
  10. ])
  11. #提交以上操作
  12. session.commit()
  13.  
  14. 结果：
  15. mysql> select * from users;
  16. +----+----------+
  17. | id | name |
  18. +----+----------+
  19. | 2 | sbliuyao |
  20. | 3 | sbyao |
  21. | 4 | liuyao |
  22. +----+----------+
  23. 3 rows in set (0.00 sec)
 

3.删除

 
  1. # ########## 删除 ##########
  2. #删除user表，id大于2的字段
  3. session.query(User).filter(User.id > 2).delete()
  4. session.commit()
  5. 结果：
  6. mysql> select * from users;
  7. +----+----------+
  8. | id | name |
  9. +----+----------+
  10. | 2 | sbliuyao |
  11. +----+----------+
  12. 1 row in set (0.00 sec)
 

4.修改

 
  1. 因上次操作已经删除好多数据
  2. 请重新执行插入字段操作
  3. session.add_all([
  4. User(id=3, name='sbyao'),
  5. User(id=4, name='liuyao'),
  6. User(id=5, name='mayun')
  7. ])
  8. session.commit()
  9. 结果：
  10. mysql> select * from users;
  11. +----+----------+
  12. | id | name |
  13. +----+----------+
  14. | 2 | sbliuyao |
  15. | 3 | sbyao |
  16. | 4 | liuyao |
  17. | 5 | mayun |
  18. +----+----------+
  19. 4 rows in set (0.00 sec)
  20.  
  21. 在执行以下操作
  22. #user表里的id等于2的字段修改为id=6
  23. session.query(User).filter(User.id == 2).update({'id' : 6})
  24. session.commit()
  25. 结果：
  26. mysql> select * from users;
  27. +----+----------+
  28. | id | name |
  29. +----+----------+
  30. | 3 | sbyao |
  31. | 4 | liuyao |
  32. | 5 | mayun |
  33. | 6 | sbliuyao |
  34. +----+----------+
  35. 4 rows in set (0.00 sec)
  36. 其他方法：
  37. #把user表里id大于2的name全部换成woshiyaoge
  38. session.query(User).filter(User.id > 2).update({'name' :'woshiyaoge'})
  39. session.commit()
  40.  
  41. mysql> select * from users;
  42. +----+------------+
  43. | id | name |
  44. +----+------------+
  45. | 3 | woshiyaoge |
  46. | 4 | woshiyaoge |
  47. | 5 | woshiyaoge |
  48. | 6 | woshiyaoge |
  49. +----+------------+
  50. 4 rows in set (0.00 sec)
 

5.查询

 
  1. 数据库如下：
  2. mysql> select * from users;
  3. +----+------------+
  4. | id | name |
  5. +----+------------+
  6. | 3 | woshiyaoge |
  7. | 4 | woshiyaoge |
  8. | 5 | woshiyaoge |
  9. | 6 | woshiyaoge |
  10. | 7 | sbyao |
  11. | 8 | liuyao |
  12. | 9 | mayun |
  13. +----+------------+
  14. 7 rows in set (0.00 sec)
  15.  
  16. 方式1：
  17.  
  18. #查询user表下面name=liuyao的字段
  19. ret = session.query(User).filter_by(name='liuyao').all()
  20. #打印实例
  21. print ret
  22. for i in ret:
  23. #打印结果
  24. print(i.id,i.name,)
  25. 结果：
  26. [<__main__.User object at 0x0000000002F55860>]
  27. (8L, 'liuyao')
  28. 这种查询方法可以返回一个User对象以及它的name属性字段的值。
  29.  
  30. 方式2：
  31. #查询user表里字段是name=liuyao的第一条数据
  32. ret = session.query(User).filter_by(name='liuyao').first()
  33. print ret.name
  34. print ret.id
  35. 结果：
  36. liuyao
  37. 8
  38.  
  39. 方式3：
  40. #查询user表里字段是name是liuyao或者mayun的信息打印出来
  41. ret = session.query(User).filter(User.name.in_(['liuyao','mayun'])).all()
  42. print ret
  43. for i in ret:
  44. print(i.name,i.id)
  45. 结果：
  46. [<__main__.User object at 0x00000000030F1E48>, <__main__.User object at 0x000000000311D8D0>]
  47. ('liuyao', 8L)
  48. ('mayun', 9L)
  49.  
  50. 方式4：
  51. #可以给返回的结果起一个别名，或者叫标签：可有可无
  52. ret = session.query(User.name.label('')).all()
  53. print ret,type(ret)
  54. 这里的关键是label方法，它的意思是把User的name字段改个名字叫name_label，
  55. 其相当于执行以下的SQL语句：
  56. SELECT users.name AS name_label
  57. FROM users
  58. 结果：
  59. [('woshiyaoge',), ('woshiyaoge',), ('woshiyaoge',), ('woshiyaoge',), ('sbyao',), ('liuyao',), ('mayun',)] <type 'list'>
  60.  
  61. 方式5：
  62. #查询User表根据id排序
  63. ret = session.query(User).order_by(User.id).all()
  64. print ret
  65. for i in ret:
  66. print(i.name)
  67. 结果：
  68. [<__main__.User object at 0x00000000031978D0>, <__main__.User object at 0x0000000003197978>, <__main__.User object at 0x00000000031979E8>, <__main__.User object at 0x0000000003197A58>, <__main__.User object at 0x000000000316BE10>, <__main__.User object at 0x000000000316BE48>, <__main__.User object at 0x0000000003197940>]
  69. woshiyaoge
  70. woshiyaoge
  71. woshiyaoge
  72. woshiyaoge
  73. sbyao
  74. liuyao
  75. mayun
  76.  
  77. 方式6：
  78. #查询user表里根据id排序输入0到3的字段
  79. ret = session.query(User).order_by(User.id)[0:3]
  80. print ret
  81. for i in ret:
  82. print(i.name)
  83. 结果：
  84. [<__main__.User object at 0x00000000030F59E8>, <__main__.User object at 0x00000000030C9E80>, <__main__.User object at 0x00000000030C9C88>]
  85. woshiyaoge
  86. woshiyaoge
  87. woshiyaoge
  88. 方式7：
  89. # 创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行:
  90. user = session.query(User).filter(User.id=='5').one()
  91. #打印类型和对象的name属性:
  92. print 'type:', type(user)
  93. print 'name:', user.name
 

7.外键关联

由于关系数据库的多个表还可以用外键实现一对多、多对多等关联，相应地，ORM框架也可以提供两个对象之间的一对多、多对多等功能。

1）一对多(一个User可以有多个Address)

 
  1. 外键引用relationship()
  2.  
  3. 例:
  4. #!/usr/bin/env python3
  5. #coding:utf8
  6. #导入所需模块
  7. from sqlalchemy import create_engine,func
  8. from sqlalchemy.ext.declarative import declarative_base
  9. from sqlalchemy import Column, Integer, String,ForeignKey
  10. from sqlalchemy.orm import sessionmaker,relationship
  11. #生成sqlorm基类
  12. Base = declarative_base()
  13. #创建数据库连接
  14. engine = create_engine("mysql+mysqldb://liuyao:liuyao@121.42.195.15:3306/liuyao", max_overflow=5)
  15. #目的是一个人可以拥有多本书，那么在数据库里的一对多关系
  16. class User(Base):
  17. #表名
  18. __tablename__ = 'user'
  19. #id字段
  20. id = Column(String(20), primary_key=True)
  21. #名字字段
  22. name = Column(String(20))
  23. # 一对多:#内容不是表名而是定义的表结构名字
  24. books = relationship('Book')
  25. class Book(Base):
  26. #表面
  27. __tablename__ = 'book'
  28. #id字段
  29. id = Column(String(20), primary_key=True)
  30. #名字字段
  31. name = Column(String(20))
  32. # “多”的一方的book表是通过外键关联到user表的:
  33. #ForeignKey是外键 关联user表的id字段
  34. user_id = Column(String(20), ForeignKey('user.id'))
  35. #创建所需表
  36. Base.metadata.create_all(engine)
  37.  
  38. if __name__ == '__main__':
  39. #绑定,生成回话
  40. SessionCls = sessionmaker(bind=engine)
  41. session = SessionCls()
  42. #创建用户
  43. liuyao = User(id='1',name='liuyao')
  44. ali=User(id='2',name='ali')
  45. #添加字段
  46. session.add_all([liuyao,ali])
  47. #提交
  48. session.commit()
  49. #创建白鹿原这本书，指定谁是拥有者
  50. Whitedeer = Book(id='1',name='White_deer',user_id = '1')
  51. #创建三体这本书，指定谁是拥有者
  52. Threebody = Book(id='2',name='Three_body',user_id = '2')
  53. #添加字段
  54. session.add_all([Whitedeer,Threebody])
  55. #提交
  56. session.commit()
 

结果：

 
  1. 表：
  2. mysql> show tables;
  3. +------------------+
  4. | Tables_in_liuyao |
  5. +------------------+
  6. | book |
  7. | user |
  8. +------------------+
  9. rows in set (0.00 sec)
  10.  
  11. user表：
  12. mysql> select * from user;
  13. +----+--------+
  14. | id | name |
  15. +----+--------+
  16. | 1 | liuyao |
  17. | 2 | ali |
  18. +----+--------+
  19. 2 rows in set (0.00 sec)
  20.  
  21. book表#已经显示关联哪个user表id
  22. mysql> select * from book;
  23. +----+------------+---------+
  24. | id | name | user_id |
  25. +----+------------+---------+
  26. | 1 | White_deer | 1 |
  27. | 2 | Three_body | 2 |
  28. +----+------------+---------+
  29. 2 rows in set (0.00 sec)
 

2）多对多

建立一个双向一对多关系,“反向”是一个许多人,指定一个额外的relationship()函数
并连接两个使用relationship.back_populates参数
简单来说, relationship函数是sqlalchemy对关系之间提供的一种便利的调用方式, backref参数则对关系提供反向引用的声明。在最新版本的sqlalchemy中对relationship引进了back_populates参数。

先创建数据库：

 
  1. #!/usr/bin/env python3
  2. #coding:utf8
  3. from sqlalchemy import Column, Sequence, String, Integer, ForeignKey
  4. from sqlalchemy import create_engine # 导入创建连接驱动
  5. from sqlalchemy.ext.declarative import declarative_base
  6. from sqlalchemy.orm import sessionmaker
  7. from sqlalchemy.orm import relationship, backref
  8. # 这个url可以用urlparse解析, 其中echo=True表示执行时显示sql语句
  9. engine = create_engine("mysql+mysqldb://liuyao:liuyao@121.42.195.15:3306/liuyao", max_overflow=5)
  10. #生成了declarative基类, 以后的model继承此类
  11. Base = declarative_base()
  12. class Parent(Base):
  13. __tablename__ = 'parent'
  14. id = Column(Integer, primary_key=True)
  15. name = Column(String(64),unique=True,nullable=False)
  16. children = relationship("Child", back_populates="parent")
  17. class Child(Base):
  18. __tablename__ = 'child'
  19. id = Column(Integer, primary_key=True)
  20. name = Column(String(64),unique=True,nullable=False)
  21. parent_id = Column(Integer, ForeignKey('parent.id'))
  22. parent = relationship("Parent", back_populates="children")
  23. Base.metadata.create_all(engine) #创建所有表结构
  24. if __name__ == '__main__':
  25. SessionCls = sessionmaker(bind=engine)
  26. #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
  27. session = SessionCls()
  28. mama = Parent(id='1',name='mamaxx')
  29. baba = Parent(id='2',name='babaoo')
  30. session.add_all([mama,baba])
  31. # onesb = Child(id='1',name='onesb',parent_id='2')
  32. # twosb = Child(id='2',name='twosb',parent_id='2')
  33. #session.add_all([onesb,twosb])
  34. session.commit()
 

3.）多对多之三表外键关联
 

 
  1. #!/usr/bin/env python3
  2. #coding:utf8
  3. from sqlalchemy import create_engine,func,Table
  4. from sqlalchemy.ext.declarative import declarative_base
  5. from sqlalchemy import Column, Integer, String,ForeignKey
  6. from sqlalchemy.orm import sessionmaker,relationship
  7. Base = declarative_base()
  8. #关系表
  9. Host2Group = Table('host_2_group',Base.metadata,
  10. Column('host_id',ForeignKey('hosts.id'),primary_key=True),
  11. Column('group_id',ForeignKey('group.id'),primary_key=True),
  12. )
  13. engine = create_engine("mysql+mysqldb://liuyao:liuyao@121.42.195.15:3306/liuyao", max_overflow=5)
  14. class Host(Base):
  15. __tablename__ = 'hosts'
  16. id = Column(Integer,primary_key=True,autoincrement=True)
  17. hostname = Column(String(64),unique=True,nullable=False)
  18. ip_addr = Column(String(128),unique=True,nullable=False)
  19. port = Column(Integer,default=22)
  20. groups = relationship('Group',
  21. secondary= Host2Group,
  22. backref = 'host_list')
  23. class Group(Base):
  24. __tablename__ = 'group'
  25. id = Column(Integer,primary_key=True)
  26. name = Column(String(64),unique=True,nullable=False)
  27.  
  28. if __name__ == '__main__':
  29. SessionCls = sessionmaker(bind=engine)
  30. session = SessionCls()
  31. g1 = Group(name='g1')
  32. g2 = Group(name='g2')
  33. g3 = Group(name='g3')
  34. g4 = Group(name='g4')
  35. session.add_all([g1,g2,g3,g4])
  36. session.commit()
 
第01篇 SQLAlchemy简介以及基本使用

SQLAlchemy库简单介绍

SQLAlchemy是一个基于Python实现的ORM对象关系映射框架。
该框架建立在DB API之上，使用关系对象映射进行数据库操作，
将类和对象转换成SQL，然后使用数据API执行SQL并获取执行结果。

SQLAlchemy的下载安装

// 默认从官网下载安装PyMySQL库
pip3 install sqlalchemy
// 从豆瓣源下载安装PyMySQL库
pip3 install sqlalchemy -i https://pypi.douban.com/simple
// 从清华源下载安装PyMySQL库
pip3 install sqlalchemy -i https://pypi.tuna.tsinghua.edu.cn/simple

SQLAlchemy的组件构成

SQLAlchemy ORM组成部分如下：
Object Relation Mapping(ORM)：对象关系映射
SQLAlchemy Core组成部分如下：
Engine：框架的引擎
Connection Pooling：数据库连接池
Dialect：选择连接数据库的DB API种类
Schema/Types：架构和类型
SQL Exprression Language：SQL表达式语言
SQLAlchemy本身无法操作数据库，其必须以来PYMYSQL等第三方插件驱动，
Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，
从而实现对数据库的操
MySQL-Python
    mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
    
pymysql
    mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
    
MySQL-Connector
    mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
    
cx_Oracle
    oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
    
更多：http://docs.sqlalchemy.org/en/latest/dialects/index.html

配置数据库连接串

uri: dialect[+driver]://user:password@host/dbname[?key=value..]
- dialect：数据库，如：sqlite、mysql、oracle、postgresql等
- driver：数据库驱动，用于连接数据库，比如pymysql、mysqldb等
- username：数据库用户
- password：数据库密码
- host：数据库服务IP地址
- port：数据库服务端口
- database：数据库名
# 实例：MySQL + PyMySQL
# MySQL服务端配置信息
DB_INFO = dict(
    host="127.0.0.1",
    port=6379,
    user="admin",
    password="123456",
    database="test",
    charset="utf8"
)
# 数据库连接URL格式化
DB_URI = 'mysql+pymysql://{user}:{password}@{host}:{port}/{database}?charset={charset}'.format(**DB_INFO)

创建引擎并连接数据库

engine = create_engine("mysql+pymysql://root:123456@localhost:3306/db4?charset=utf8",
                        max_overflow=0,  # 超过连接池大小外最多创建的连接
                        pool_size=5,  # 连接池大小
                        pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
                        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
                        echo = True    # echo参数为True时，会显示每条执行的SQL语句，可以关闭 ",max_overflow = 5)
from sqlalchemy import create_engine

# 连接地址
DB_URI = 'mysql+pymysql://{user}:{password}@{host}:{port}/{database}?charset={charset}'
# 创建引擎
engine = create_engine(DB_URI)
# 打开连接
conn = engine.connect() 
# 执行查询
result = conn.execute('select * from user limit %s offset %s', 10, 2)
# 获取单条数据
data_line = result.fetchone()
# 获取多条数据
data_list = result.fetchmany(2)
# 获取全部数据
data_list = result.fetchall()
# 插入数据操作，获取最后行ID
last_row_id = result.lastrowid
# 关闭连接
conn.close()

数据库对象映射模型

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
# 数据库连接地址
DB_URI = 'mysql+pymysql://{user}:{password}@{host}:{port}/{database}?charset={charset}'
# 创建数据库引擎
engine = create_engine(DB_URI)
# 模型基类
Base = declarative_base(engine)
session = sessionmaker(engine)()


class Student(Base):
    """功能：学生映射模型类"""
    __tablename__ = 'Student'
    id = Column(Integer, primary_key=True, autoincrement=True, comment="主键ID")
    name = Column(String(50), index=True, nullable=True, comment="学生名称")
    age = Column(Integer, comment="学生年龄")
    sex = Column(String(10), comment="学生性别")

# 创建全部表，默认自动跳过已存在表
Base.metadata.create_all()
# 创建指定表，默认自动跳过已存在表
Base.metadata.create_all(tables=[Student.__table__])

# 删除全部表，默认自动跳过不存在的表
Base.metadata.drop_all()
# 删除指定表，默认自动跳过不存在的表
Base.metadata.drop_all(tables=[Student.__table__])
posted @ 2024-03-26 11:06  CharyGao  阅读(31)  评论(0)  编辑  收藏  举报