python的学习第十二天 mysql sqlalchemy ORM
mysql orm
数据库
数据库(Database)是按照数据结构来组织、存储和管理数据的仓库，它产生于距今六十多年前，随着信息技术和市场的发展，特别是二十世纪九十年代以后，数据管理不再仅仅是存储和管理数据，而转变成用户所需要的各种数据管理的方式。数据库有很多种类型，从最简单的存储有各种数据的表格到能够进行海量数据存储的大型数据库系统都在各个方面得到了广泛的应用。
关系型数据库通过外键关联来建立表与表之间的关系，非关系型数据库通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定
关系型数据库：oracle,Sybase,DB2,SQL server ,MySQL
非关系型数据库 ：NoSQL MongoDB
直接管理数据库 Mysql
管理MySQL的命令
以下列出了使用Mysql数据库过程中常用的命令：
-
USE 数据库名 :选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库。
-
SHOW DATABASES: 列出 MySQL 数据库管理系统的数据库列表。
-
SHOW TABLES: #显示指定数据库的所有表，使用该命令前需要使用 use命令来选择要操作的数据库。
-
SHOW COLUMNS FROM 数据表: #显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。
-
create database testdb charset "utf8"; #创建一个叫testdb的数据库，且让其支持中文
-
drop database testdb; #删除数据库
-
SHOW INDEX FROM 数据表:显示数据表的详细索引信息，包括PRIMARY KEY（主键）。
Mysql的类型可以看老师的博客，太详细了。
我记录上课的一些关键操作
mysql -u root -p 密码 登录数据 执行show databases; 查看数据库
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.07 sec)
先设置字符编码数据库 utf8支持中文
1 mysql> set character_set_client=utf8; 2 mysql> set character_set_connection=utf8; 3 mysql> set character_set_database=utf8; 4 mysql> set character_set_results=utf8; 5 mysql> set character_set_server=utf8; 6 mysql> set character_set_system=utf8; 7 mysql> set collation_connection=utf8; 8 mysql> set collation_database=utf8; 9 mysql> set collation_server=utf8;
或者在my.conf下配置：
[mysqld] character-set-server=utf8 [client] default-character-set=utf8 [mysql] default-character-set=utf8
查看mysql编码
MariaDB [(none)]> show variables like 'character_set_%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+
创建一个库 create database jack character set utf8;
use jack； 进入数据库
show tables；显示表
desc user\G 查看一个表的结构；
MariaDB [mysql]> desc user; +------------------------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-----------------------------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Reload_priv | enum('N','Y') | NO | | N | | | Shutdown_priv | enum('N','Y') | NO | | N | | | Process_priv | enum('N','Y') | NO | | N | | | File_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Show_db_priv | enum('N','Y') | NO | | N | | | Super_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Repl_slave_priv | enum('N','Y') | NO | | N | | | Repl_client_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Create_user_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | | Create_tablespace_priv | enum('N','Y') | NO | | N | | | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | | ssl_cipher | blob | NO | | NULL | | | x509_issuer | blob | NO | | NULL | | | x509_subject | blob | NO | | NULL | | | max_questions | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_user_connections | int(11) | NO | | 0 | | | plugin | char(64) | NO | | | | | authentication_string | text | NO | | NULL | | +------------------------+-----------------------------------+------+-----+---------+-------+
查看一个用户的信息：
MariaDB [mysql]> SELECT host, user, password FROM user WHERE user = 'tom'; +-------------+------+-------------------------------------------+ | host | user | password | +-------------+------+-------------------------------------------+ | 192.168.%.% | tom | *128977E278358FF80A246B5046F51043A2B1FCED | +-------------+------+-------------------------------------------+ 1 row in set (0.00 sec)
授权一个用户访问：
MariaDB [mysql]> grant all on jack.* to 'jack'@'192.168.%.%' identified by 'jack123'; Query OK, 0 rows affected (0.00 sec)
mysql 数据类型
MySQL中定义数据字段的类型对你数据库的优化是非常重要的。
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| TINYINT | 1 字节 | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 字节 | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 字节 | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 字节 | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 字节 | (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 字节 | (-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度 浮点数值 |
| DOUBLE | 8 字节 | (1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性，将在后面描述；
| 类型 | 大小 (字节) | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2037 年某时 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255字节 | 定长字符串 |
| VARCHAR | 0-65535 字节 | 变长字符串 |
| TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255字节 | 短文本字符串 |
| BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
| TEXT | 0-65 535字节 | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
mysql 基本操作
创建一个表：
create table student( stu_id INT NOT NULL AUTO_INCREMENT, #不为空 自动增加id name CHAR(32) NOT NULL, age INT NOT NULL, register_date DATE, PRIMARY KEY ( stu_id ) #设置为主键唯一 );
插入3个数据：
MariaDB [jack]> insert into st (name,age,date) values('aa',22,20161025) -> ; Query OK, 1 row affected (0.04 sec) MariaDB [jack]> select * from st; +----+------+-----+------------+ | id | name | age | date | +----+------+-----+------------+ | 1 | aa | 22 | 0000-00-00 | | 2 | aa | 22 | 0000-00-00 | | 3 | aa | 22 | 2016-10-25 | +----+------+-----+------------+ 3 rows in set (0.00 sec)
查看指定的2行内容
MariaDB [jack]> select * from st; +----+------+-----+------------+ | id | name | age | date | +----+------+-----+------------+ | 1 | aa | 22 | 0000-00-00 | | 2 | aa | 22 | 0000-00-00 | | 3 | aa | 22 | 2016-10-25 | | 4 | bb | 22 | 2016-10-15 | | 5 | cc | 99 | 2016-11-15 | | 6 | cc | 99 | 2016-11-15 | | 7 | cc | 99 | 2016-11-15 | +----+------+-----+------------+ 7 rows in set (0.00 sec) MariaDB [jack]> select * from st limit 2; +----+------+-----+------------+ | id | name | age | date | +----+------+-----+------------+ | 1 | aa | 22 | 0000-00-00 | | 2 | aa | 22 | 0000-00-00 | +----+------+-----+------------+
MariaDB [jack]> select * from st limit 3 offset 2; +----+------+-----+------------+ | id | name | age | date | +----+------+-----+------------+ | 3 | aa | 22 | 2016-10-25 | | 4 | bb | 22 | 2016-10-15 | | 5 | cc | 99 | 2016-11-15 | +----+------+-----+------------+
where 过滤
MariaDB [jack]> select * from st where id > 3; +----+------+-----+------------+ | id | name | age | date | +----+------+-----+------------+ | 4 | bb | 22 | 2016-10-15 | | 5 | cc | 99 | 2016-11-15 | | 6 | cc | 99 | 2016-11-15 | | 7 | cc | 99 | 2016-11-15 | +----+------+-----+------------+
MariaDB [jack]> select * from st where id > 3 and age > 44; +----+------+-----+------------+ | id | name | age | date | +----+------+-----+------------+ | 5 | cc | 99 | 2016-11-15 | | 6 | cc | 99 | 2016-11-15 | | 7 | cc | 99 | 2016-11-15 | +----+------+-----+------------+ 3 rows in set (0.00 sec)
MariaDB [jack]> select * from st where date like '2016-10-%' -> ; +----+------+-----+------------+ | id | name | age | date | +----+------+-----+------------+ | 3 | aa | 22 | 2016-10-25 | | 4 | bb | 22 | 2016-10-15 | +----+------+-----+------------+
| 操作符 | 描述 | 实例 |
|---|---|---|
| = | 等号，检测两个值是否相等，如果相等返回true | (A = B) 返回false。 |
| <>, != | 不等于，检测两个值是否相等，如果不相等返回true | (A != B) 返回 true。 |
| > | 大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true | (A > B) 返回false。 |
| < | 小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true | (A < B) 返回 true。 |
| >= | 大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true | (A >= B) 返回false。 |
| <= | 小于等于号，检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true | (A <= B) 返回 true。 |
MariaDB [jack]> select * from st; +----+------+-----+------------+ | id | name | age | date | +----+------+-----+------------+ | 1 | aa | 22 | 0000-00-00 | | 2 | aa | 22 | 0000-00-00 | | 3 | aa | 22 | 2016-10-25 | | 4 | bb | 22 | 2016-10-15 | | 5 | cc | 99 | 2016-11-15 | | 6 | cc | 99 | 2016-11-15 | | 7 | cc | 99 | 2016-11-15 | +----+------+-----+------------+ 7 rows in set (0.00 sec) MariaDB [jack]> update st set name='ff',age=77 where id=2; #更新数据 Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [jack]> select * from st; +----+------+-----+------------+ | id | name | age | date | +----+------+-----+------------+ | 1 | aa | 22 | 0000-00-00 | | 2 | ff | 77 | 0000-00-00 | | 3 | aa | 22 | 2016-10-25 | | 4 | bb | 22 | 2016-10-15 | | 5 | cc | 99 | 2016-11-15 | | 6 | cc | 99 | 2016-11-15 | | 7 | cc | 99 | 2016-11-15 | +----+------+-----+------------+
排序 默认 asc升序 desc 降序
MariaDB [jack]> select * from st order by id desc; +----+------+-----+------------+ | id | name | age | date | +----+------+-----+------------+ | 7 | cc | 99 | 2016-11-15 | | 6 | cc | 99 | 2016-11-15 | | 5 | cc | 99 | 2016-11-15 | | 4 | bb | 22 | 2016-10-15 | | 3 | aa | 22 | 2016-10-25 | | 2 | ff | 77 | 0000-00-00 | | 1 | aa | 22 | 0000-00-00 | +----+------+-----+------------+
统计重复的name有多少
MariaDB [jack]> select name,count(*) from st group by name; +------+----------+ | name | count(*) | +------+----------+ | aa | 2 | | bb | 1 | | cc | 3 | | ff | 1 | +------+----------+ 4 rows in set (0.00 sec)
MariaDB [jack]> alter table st add sex int; 增加一列
外间 建立另外一个表mokey class_id 和 id建立外键关系 （引用的这个表不能先删除原始的，如果原始的在被引用是无法删除的，如果自己新增加一个在引用的表里的原来表里面没有的也会增加失败）
MariaDB [jack]> CREATE TABLE `monkey` ( `id` int(11) NOT NULL, `name` char(16) NOT NULL, `class_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `fk` (`class_id`), CONSTRAINT `fk` FOREIGN KEY (`class_id`) REFERENCES `st` (`id`) ); Query OK, 0 rows affected (0.06 sec)
Mysql 连接（left join, right join, inner join ,full join）
我们已经学会了如果在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据。
本章节我们将向大家介绍如何使用 MySQL 的 JOIN 在两个或多个表中查询数据。
你可以在SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。
JOIN 按照功能大致分为如下三类：
- INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
- LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
- RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
- 在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
- 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行
- 事务用来管理insert,update,delete语句
一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）
- 1、事务的原子性：一组事务，要么成功；要么撤回。
- 2、稳定性 ： 有非法数据（外键约束之类），事务撤回。
- 3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
- 4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里
MariaDB [jack]> select * from st; +----+------+-----+------------+------+ | id | name | age | date | sex | +----+------+-----+------------+------+ | 1 | aa | 22 | 0000-00-00 | NULL | | 2 | ff | 77 | 0000-00-00 | NULL | | 4 | bb | 22 | 2016-10-15 | NULL | | 5 | cc | 99 | 2016-11-15 | NULL | +----+------+-----+------------+------+ 4 rows in set (0.00 sec) MariaDB [jack]> begin; Query OK, 0 rows affected (0.00 sec) MariaDB [jack]> insert into st(name,age,date) values('gg',22,20141111); Query OK, 1 row affected (0.00 sec) MariaDB [jack]> select * from st; +----+------+-----+------------+------+ | id | name | age | date | sex | +----+------+-----+------------+------+ | 1 | aa | 22 | 0000-00-00 | NULL | | 2 | ff | 77 | 0000-00-00 | NULL | | 4 | bb | 22 | 2016-10-15 | NULL | | 5 | cc | 99 | 2016-11-15 | NULL | | 9 | gg | 22 | 2014-11-11 | NULL | +----+------+-----+------------+------+ 5 rows in set (0.00 sec) MariaDB [jack]> rollback; Query OK, 0 rows affected (0.05 sec) MariaDB [jack]> select * from st; +----+------+-----+------------+------+ | id | name | age | date | sex | +----+------+-----+------------+------+ | 1 | aa | 22 | 0000-00-00 | NULL | | 2 | ff | 77 | 0000-00-00 | NULL | | 4 | bb | 22 | 2016-10-15 | NULL | | 5 | cc | 99 | 2016-11-15 | NULL | +----+------+-----+------------+------+ 4 rows in set (0.00 sec)
当然如果上面的数据没问题，就输入commit提交命令就行；
pymysql模块操作mysql
#!/usr/bin/env python # -*- coding:utf-8 -*- import pymysql # 创建连接 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1') # 创建游标 cursor = conn.cursor() # 执行SQL，并返回收影响行数 effect_row = cursor.execute("update hosts set host = '1.1.1.2'") # 执行SQL，并返回受影响行数 #effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,)) # 执行SQL，并返回受影响行数 #effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)]) # 提交，不然无法保存新建或者修改的数据 conn.commit() # 关闭游标 cursor.close() # 关闭连接 conn.close()
例子：
#!/usr/bin/env python # -*- coding:utf-8 -*- __author__ = "tao" import pymysql # 创建连接 conn = pymysql.connect(host='192.168.142.129', port=3306, user='centos', passwd='centos', db='jack') # 创建游标 cursor = conn.cursor() # 执行SQL，并返回收影响行数 effect_row = cursor.execute("select * from st") print(effect_row) #有多少条数据 print(cursor.fetchone()) #取一条数据 print(cursor.fetchone())
print(curson.fetchmany(2)) #获取2条数据 print(cursor.fetchall()) #取全部的 # print(cursor.fetchall()[1]) # 提交，不然无法保存新建或者修改的数据 conn.commit() # 关闭游标 cursor.close() # 关闭连接 conn.close() # 4 # (1, 'aa', 22, None, None) # (2, 'ff', 77, None, None) # ((4, 'bb', 22, datetime.date(2016, 10, 15), None), (5, 'cc', 99, datetime.date(2016, 11, 15), None))
sqlalchemy orm
例子：用sqlalchemy创建一个user1表
#!/usr/bin/env python
# -*- coding:utf-8 -*-
__author__ = "tao"
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
engine = create_engine("mysql+pymysql://centos:centos@192.168.142.129/jack",
encoding='utf-8', echo=True) #显示操作
Base = declarative_base() # 生成orm基类
class User(Base):
__tablename__ = 'user1' # 表名
id = Column(Integer, primary_key=True)
name = Column(String(32))
password = Column(String(64))
Base.metadata.create_all(engine) # 创建表结构
查询
my_user = Session.query(User).filter_by(name="alex").first() print(my_user) my_user = Session.query(User).filter(User.name=="alex").first() print(my_user)
my_user = Session.query(User).filter_by(name="alex").first()
print(my_user)最详细的地址：http://www.cnblogs.com/alex3714/articles/5978329.html
外键关联例子：
#!/usr/bin/env python # -*- coding:utf-8 -*- __author__ = "tao" import sqlalchemy from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Enum,DATE,Integer, String,ForeignKey from sqlalchemy.orm import sessionmaker,relationship engine = create_engine("mysql+pymysql://centos:centos@192.168.142.129/jack", encoding='utf-8') Base = declarative_base() # 生成orm基类 class Student(Base): __tablename__ = "student" id = Column(Integer, primary_key=True) name = Column(String(32),nullable=False) register_date = Column(DATE,nullable=False) def __repr__(self): return "<%s name:%s>" % (self.id, self.name) class StudyRecord(Base): __tablename__ = "study_record" id = Column(Integer, primary_key=True) day = Column(Integer,nullable=False) status = Column(String(32),nullable=False) stu_id = Column(Integer,ForeignKey("student.id")) #study_obj = query(id=1) #student = query(Student).filter(Student.id == stu_obj.stu_id).first() student = relationship("Student", backref="my_study_record") #这个nb，允许你在user表里通过backref字段反向查出所有它在addresses表里的关联项 def __repr__(self): return "<%s day:%s status:%s>" % (self.student.name, self.day,self.status) Base.metadata.create_all(engine) # 创建表结构 Session_class = sessionmaker(bind=engine) # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例 session = Session_class() # 生成session实例 #cursor
多个表关联一个表
创建表结构：
#!/usr/bin/env python # -*- coding:utf-8 -*- __author__ = "tao" from sqlalchemy import Integer, ForeignKey, String, Column from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import relationship from sqlalchemy import create_engine Base = declarative_base() class Customer(Base): __tablename__ = 'customer' id = Column(Integer, primary_key=True) name = Column(String(64)) billing_address_id = Column(Integer, ForeignKey("address.id")) shipping_address_id = Column(Integer, ForeignKey("address.id")) billing_address = relationship("Address") shipping_address = relationship("Address") class Address(Base): __tablename__ = 'address' id = Column(Integer, primary_key=True) street = Column(String(64)) city = Column(String(64)) state = Column(String(64)) engine = create_engine("mysql+pymysql://centos:centos@192.168.142.129/jack", encoding='utf-8') Base.metadata.create_all(engine) #创建表结构
------------------------------------------------------------------------------+ | customer | CREATE TABLE `customer` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(64) DEFAULT NULL, `billing_address_id` int(11) DEFAULT NULL, `shipping_address_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `billing_address_id` (`billing_address_id`), KEY `shipping_address_id` (`shipping_address_id`), CONSTRAINT `customer_ibfk_1` FOREIGN KEY (`billing_address_id`) REFERENCES `address` (`id`), CONSTRAINT `customer_ibfk_2` FOREIGN KEY (`shipping_address_id`) REFERENCES `address` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
插入数据： 一般是把上面的表机构放到单独的文件，from导入当模块调用
我起名字叫：orm_marny_fk
#!/usr/bin/env python # -*- coding:utf-8 -*- __author__ = "tao" from sqlalchemy import Integer, ForeignKey, String, Column from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import relationship from sqlalchemy import create_engine Base = declarative_base() class Customer(Base): __tablename__ = 'customer' id = Column(Integer, primary_key=True) name = Column(String(64)) billing_address_id = Column(Integer, ForeignKey("address.id")) shipping_address_id = Column(Integer, ForeignKey("address.id")) billing_address = relationship("Address",foreign_keys=[billing_address_id]) #分不清楚两个谁是谁 加foreign_keys shipping_address = relationship("Address",foreign_keys=[shipping_address_id]) class Address(Base): __tablename__ = 'address' id = Column(Integer, primary_key=True) street = Column(String(64)) city = Column(String(64)) state = Column(String(64)) engine = create_engine("mysql+pymysql://centos:centos@192.168.142.129/jack", encoding='utf-8') Base.metadata.create_all(engine) #创建表结构
创建的代码：
#!/usr/bin/env python # -*- coding:utf-8 -*- __author__ = "tao" from day12.boke import orm_marny_fk from sqlalchemy.orm import sessionmaker Session_clase = sessionmaker(bind=orm_marny_fk.engine) session = Session_clase() #生成实例 addr1 = orm_marny_fk.Address(street="tianty",city="changpin",state="bj") addr2 = orm_marny_fk.Address(street="wdk",city="haidiang",state="bj") addr3 = orm_marny_fk.Address(street="wanda",city="tognzhou",state="bj") session.add_all([addr1,addr2,addr3]) c1 = orm_marny_fk.Customer(name="tom",billing_address=addr1,shipping_address=addr2) c2 = orm_marny_fk.Customer(name="jack",billing_address=addr3,shipping_address=addr3) session.add_all([c1,c2]) session.commit() #sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Customer.billing_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 #billing_address = relationship("Address",foreign_keys=[billing_address_id]) 用foreign_keys解决
查询的代码：boke5文件
#!/usr/bin/env python # -*- coding:utf-8 -*- __author__ = "tao" from day12.boke import orm_marny_fk from sqlalchemy.orm import sessionmaker Session_clase = sessionmaker(bind=orm_marny_fk.engine) session = Session_clase() #生成实例 # addr1 = orm_marny_fk.Address(street="tianty",city="changpin",state="bj") # addr2 = orm_marny_fk.Address(street="wdk",city="haidiang",state="bj") # addr3 = orm_marny_fk.Address(street="wanda",city="tognzhou",state="bj") # session.add_all([addr1,addr2,addr3]) # c1 = orm_marny_fk.Customer(name="tom",billing_address=addr1,shipping_address=addr2) # c2 = orm_marny_fk.Customer(name="jack",billing_address=addr3,shipping_address=addr3) # session.add_all([c1,c2]) obj = session.query(orm_marny_fk.Customer).filter(orm_marny_fk.Customer.name=="tom").first() print(obj.name,obj.billing_address,obj.shipping_address) session.commit() #sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Customer.billing_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 #billing_address = relationship("Address",foreign_keys=[billing_address_id]) 用foreign_keys解决 #tom <day12.boke.orm_marny_fk.Address object at 0x0000000003EE60F0> <day12.boke.orm_marny_fk.Address object at 0x0000000003EE6320> #tom tianty wdk 加人def __repr__(self): 后结果 #return self.street
上面的导入变化的代码
#!/usr/bin/env python # -*- coding:utf-8 -*- __author__ = "tao" from sqlalchemy import Integer, ForeignKey, String, Column from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import relationship from sqlalchemy import create_engine Base = declarative_base() class Customer(Base): __tablename__ = 'customer' id = Column(Integer, primary_key=True) name = Column(String(64)) billing_address_id = Column(Integer, ForeignKey("address.id")) shipping_address_id = Column(Integer, ForeignKey("address.id")) billing_address = relationship("Address",foreign_keys=[billing_address_id]) #分不清楚两个谁是谁 加foreign_keys shipping_address = relationship("Address",foreign_keys=[shipping_address_id]) class Address(Base): __tablename__ = 'address' id = Column(Integer, primary_key=True) street = Column(String(64)) city = Column(String(64)) state = Column(String(64)) def __repr__(self): return self.street engine = create_engine("mysql+pymysql://centos:centos@192.168.142.129/jack", encoding='utf-8') Base.metadata.create_all(engine) #创建表结构
多对多
先创建表结构：
#!/usr/bin/env python # -*- coding:utf-8 -*- __author__ = "tao" #一本书可以有多个作者，一个作者又可以出版多本书 from sqlalchemy import Table, Column, Integer,String,DATE, ForeignKey from sqlalchemy.orm import relationship from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker Base = declarative_base() book_m2m_author = Table('book_m2m_author', Base.metadata, Column('book_id',Integer,ForeignKey('books.id')), Column('author_id',Integer,ForeignKey('authors.id')), ) #用这种方法创建第3张表是因为不用自己管理这个表 这个只是记录另外2个表的关系 mysql自己管理的 class Book(Base): __tablename__ = 'books' id = Column(Integer,primary_key=True) name = Column(String(64)) pub_date = Column(DATE) authors = relationship('Author',secondary=book_m2m_author,backref='books') def __repr__(self): return self.name class Author(Base): __tablename__ = 'authors' id = Column(Integer, primary_key=True) name = Column(String(32)) def __repr__(self): return self.name engine = create_engine("mysql+pymysql://centos:centos@192.168.142.129/jack", encoding='utf-8') Base.metadata.create_all(engine) # 创建表结构
MariaDB [jack]> show tables; +-----------------+ | Tables_in_jack | +-----------------+ | address | | authors | | book_m2m_author | | books | | customer | | monkey | | st | | user1 | +-----------------+ 8 rows in set (0.00 sec) MariaDB [jack]> desc book_m2m_author; +-----------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+-------+ | book_id | int(11) | YES | MUL | NULL | | | author_id | int(11) | YES | MUL | NULL | | +-----------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
插入数据：
#!/usr/bin/env python # -*- coding:utf-8 -*- __author__ = "tao" from day12.boke import orm_m2m_fk from sqlalchemy.orm import sessionmaker Session_class = sessionmaker(bind=orm_m2m_fk.engine) #mysql连接 session = Session_class() #生成session 实例 b1 = orm_m2m_fk.Book(name="learn python",pub_date="20160112") b2 = orm_m2m_fk.Book(name="learn linux",pub_date="20150112") b3 = orm_m2m_fk.Book(name="learn windows",pub_date="20140112") a1 = orm_m2m_fk.Author(name="tom") a2 = orm_m2m_fk.Author(name="jack") a3 = orm_m2m_fk.Author(name="niubi") b1.authors = [a1,a2] b2.authors =[a2,a3] b3.authors = [a3] session.add_all([b1,b2,b3,a1,a2,a3]) session.commit()
查询;
#!/usr/bin/env python # -*- coding:utf-8 -*- __author__ = "tao" from day12.boke import orm_m2m_fk from sqlalchemy.orm import sessionmaker Session_class = sessionmaker(bind=orm_m2m_fk.engine) #mysql连接 session = Session_class() #生成session 实例 # b1 = orm_m2m_fk.Book(name="learn python",pub_date="20160112") # b2 = orm_m2m_fk.Book(name="learn linux",pub_date="20150112") # b3 = orm_m2m_fk.Book(name="learn windows",pub_date="20140112") # a1 = orm_m2m_fk.Author(name="tom") # a2 = orm_m2m_fk.Author(name="jack") # a3 = orm_m2m_fk.Author(name="niubi") # b1.authors = [a1,a2] # b2.authors =[a2,a3] # b3.authors = [a3] # session.add_all([b1,b2,b3,a1,a2,a3]) authoer_obj = session.query(orm_m2m_fk.Author).filter(orm_m2m_fk.Author.name=="tom").first() print(authoer_obj.books) # print(authoer_obj.books[1].pub_date) book_obj = session.query(orm_m2m_fk.Book).filter(orm_m2m_fk.Book.id==3).first() print(book_obj.authors) session.commit()
删除一个关系
#!/usr/bin/env python # -*- coding:utf-8 -*- __author__ = "tao" from day12.boke import orm_m2m_fk from sqlalchemy.orm import sessionmaker Session_class = sessionmaker(bind=orm_m2m_fk.engine) #mysql连接 session = Session_class() #生成session 实例 # b1 = orm_m2m_fk.Book(name="learn python",pub_date="20160112") # b2 = orm_m2m_fk.Book(name="learn linux",pub_date="20150112") # b3 = orm_m2m_fk.Book(name="learn windows",pub_date="20140112") # a1 = orm_m2m_fk.Author(name="tom") # a2 = orm_m2m_fk.Author(name="jack") # a3 = orm_m2m_fk.Author(name="niubi") # b1.authors = [a1,a2] # b2.authors =[a2,a3] # b3.authors = [a3] # session.add_all([b1,b2,b3,a1,a2,a3]) authoer_obj = session.query(orm_m2m_fk.Author).filter(orm_m2m_fk.Author.name=="jack").first() # print(authoer_obj.books) # print(authoer_obj) # print(authoer_obj.books[1].pub_date) # print(authoer_obj.books) book_obj = session.query(orm_m2m_fk.Book).filter(orm_m2m_fk.Book.id==2).first() print(book_obj.authors.remove(authoer_obj)) #删除一个 session.commit() # MariaDB [jack]> select * from book_m2m_author; # +---------+-----------+ # | book_id | author_id | # +---------+-----------+ # | 2 | 2 | # | 2 | 3 | # | 3 | 3 | # | 1 | 1 | # | 1 | 2 | # +---------+-----------+ # MariaDB [jack]> select * from book_m2m_author; # +---------+-----------+ # | book_id | author_id | # +---------+-----------+ # | 2 | 3 | # | 3 | 3 | # | 1 | 1 | # | 1 | 2 | # +---------+-----------+
支持中文：？charset=utf8
engine = create_engine("mysql+pymysql://centos:centos@192.168.142.129/jack?charset=utf8",encoding='utf-8')
MariaDB [jack]> select * from books; +----+---------------+------------+ | id | name | pub_date | +----+---------------+------------+ | 1 | learn python | 2016-01-12 | | 2 | learn linux | 2015-01-12 | | 3 | learn windows | 2014-01-12 | | 4 | learn python | 2016-01-12 | | 5 | learn linux | 2015-01-12 | | 6 | learn windows | 2014-01-12 | | 7 | learn python | 2016-01-12 | | 8 | learn linux | 2015-01-12 | | 9 | learn windows | 2014-01-12 | | 10 | 支持中文 | 2014-01-12 | +----+---------------+------------+
浙公网安备 33010602011771号