澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

第十一篇,python与数据库之mysql

2019-06-16 作者:www.8455.com   |   浏览(57)

相关内容:

  • 使用pymysql直接操作mysql

    • 创建表
    • 查看表
    • 修改表
    • 删除表
    • 插入数据
    • 查看数据
    • 修改数据
    • 删除数据

     

  • 使用sqlmary操作mysql

    • 创建表
    • 查看表
    • 修改表
    • 删除表
    • 插入数据
    • 查看数据
    • 修改数据
    • 删除数据

 

 

首发时间:2018-02-24 23:59

修改:

  • 2018-06-15,发现自己关于pymysql写了对于数据的操作示例,但没有写表结构的示例,于是添加上

 


14.python与数据库之mysql:pymysql、sqlalchemy,14.pythonpymysql

Python(九)【分分钟精通】 Python 操作 MySQL 之 pysql 与 ORM,pysqlorm

本文针对 Python 操作 MySQL 主要使用的两种方式讲解:

  • 原生模块 pymsql
  • ORM框架 SQLAchemy

本章内容: 

  • pymsql 执行 sql 增删改查 语句
  • pymsql 获取查询内容、获取自增 ID
  • pymsql 游标
  • pymsql 更改 fetch 数据类型
  • pymsql 利用 with 简化操作
  • ORM 下载安装
  • ORM 史上最全操作

 

本篇对于Python操作MySQL主要使用两种方式:

本篇对于Python操作MySQL主要使用两种方式:

直接操作mysql--pymysql:

直接操作mysql意思是利用python实现类似命令行模式下的mysql交互。

 

相关内容:

  • 使用pymysql直接操作mysql

    • 创建表
    • 查看表
    • 修改表
    • 删除表
    • 插入数据
    • 查看数据
    • 修改数据
    • 删除数据

     

  • 使用sqlmary操作mysql

    • 创建表
    • 查看表
    • 修改表
    • 删除表
    • 插入数据
    • 查看数据
    • 修改数据
    • 删除数据

 

 

首发时间:2018-02-24 23:59

【第一次写那么长的博文,若有错误,请不吝与我说明】


一、pymsql

pymsql 是 Python 中操作 MySQL 的原生模块,其使用方法和 MySQL 的SQL语句几乎相同

1、原生模块pymsql。

1、原生模块pymsql。

 

直接操作mysql:

1、下载安装

pip3 install pymysql

2、ORM框架SQLAchemy。

2、ORM框架SQLAchemy。

前提:

  • 首先需要安装python与mysql交互的库【PyMySQL 是在 Python3 版本中用于连接 MySQL 服务器的一个库】:

    • 安装模块:pymysql:

      pip3 install pymysql
      

      或者在Pycharm中安装

前提:

  • 首先需要安装python与mysql交互的库【PyMySQL 是在 Python3 版本中用于连接 MySQL 服务器的一个库】:

    • 安装模块:pymysql:

      pip3 install pymysql
      

      或者在Pycharm中安装

2、执行SQL

执行 SQL 语句的基本语法:

需要注意的是:创建链接后,都由游标来进行与数据库的操作,当然,拿到数据也靠游标

#!/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()

pymsql

pymsql是Python中操作MySQL的模块,其使用方法和MySQLdb几乎相同。

pymsql

pymsql是Python中操作MySQL的模块,其使用方法和MySQLdb几乎相同。

使用:

  • 首先导入模块:import pymysql
  • 连接数据库 :数据库连接对象 = pymysql.connect("host="localhost",port=3306,user='root',passwd='123456',db='python_test') 【如果需要支持中文,则加上charset=”utf8”】澳门新萄京官方网站 1
  • 创建游标【游标用于执行sql语句和管理查询到的结果】 :游标对象 = 数据库连接对象.cursor()
  • 执行sql语句 :游标对象.execute(SQL语句) ,返回值是受影响行数  【execute可以执行所有的sql语句,不论是表相关的,还是数据相关的。】
    • 由于默认开始事务,如果涉及修改、插入,则需要提交:连接对象.commit() ;以及撤销修改、插入的回滚:连接对象.rollback()
    • executemany是同时执行多条sql语句【以多组参数的格式,executemany(self,query,args)】:
    • 澳门新萄京官方网站 2
  • 获取结果:

    • 获取一条结果:data = 游标对象.fetchone()

    • 获取全部结果:data=游标对象.fetchall()

    • 获取指定数量结果:data=游标对象.fetmany(x)

    • 获取结果后,就会将对应的结果删掉,比如fetchone是获取一条,那么这一条就会从原来的结果中删除

    • 游标对象.rowcount()可以获得执行sql语句后受影响的行数

    • 澳门新萄京官方网站 3 

  • 关闭游标: 游标对象.close()

  • 关闭数据库连接:数据库连接对象.close()

 

使用:

  • 首先导入模块:import pymysql
  • 连接数据库 :数据库连接对象 = pymysql.connect("host="localhost",port=3306,user='root',passwd='123456',db='python_test') 【如果需要支持中文,则加上charset=”utf8”】
  • 创建游标【游标用于执行sql语句和管理查询到的结果】 :游标对象 = 数据库连接对象.cursor()
  • 执行sql语句 :游标对象.execute(SQL语句) ,返回值是受影响行数

    • 由于默认开始事务,如果涉及修改、插入,则需要提交:连接对象.commit() ;以及撤销修改、插入的回滚:连接对象.rollback()
    • executemany是同时执行多条sql语句【以多组参数的格式,executemany(self,query,args)】:
  • 获取结果:

    • 获取一条结果:data = 游标对象.fetchone()

    • 获取全部结果:data=游标对象.fetchall()

    • 获取指定数量结果:data=游标对象.fetmany(x)

    • 获取结果后,就会将对应的结果删掉,比如fetchone是获取一条,那么这一条就会从原来的结果中删除

    • 游标对象.rowcount()可以获得执行sql语句后受影响的行数

    •  

  • 关闭游标: 游标对象.close()

  • 关闭数据库连接:数据库连接对象.close()

 

3、获取新创建数据自增ID

可以获取到最新自增的ID,也就是最后插入的一条数据ID

#!/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()
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()

# 获取最新自增ID
new_id = cursor.lastrowid

windows下载安装

  1. pip3 install pymysql

windows下载安装

  1. pip3 install pymysql

示例:

示例:

4、获取查询数据

获取查询数据的三种方式:

#!/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()
cursor.execute("select * from hosts")

# 获取第一行数据
row_1 = cursor.fetchone()

# 获取前n行数据
# row_2 = cursor.fetchmany(3)

# 获取所有数据
# row_3 = cursor.fetchall()

conn.commit()
cursor.close()
conn.close()

使用操作

执行SQL

  1. __author__ = 'Golden'

  2. #!/usr/bin/env python3

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

  4. import pymysql

  5.  

  6. # 创建连接

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

  1. # 创建游标

  2. cursor = conn.cursor()

  3. # 执行SQL,并返回受影响行数

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

  5. print(effect_row)

  6. # 取出一条数据

  7. print(cursor.fetchone())

  8. # 取出前n条数据

  9. print("*********************************************************")

  1. print(cursor.fetchmany(5))

  2. # 取出所有数据

  3. print("*********************************************************")

  1. print(cursor.fetchall())

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

  1. conn.commit()

  2. # 关闭游标

  3. cursor.close()

  4. # 关闭连接

  5. conn.close()

可能报错:pymysql.err.InternalError: (1130, "Host '192.168.31.1' is not allowed to connect to this MySQL server")

可能是因为你的账号不允许从远程登录,只能在localhost登录。只需要在localhost的那台电脑登录mysql,更改对应数据库里的"user"表里的"host"项,把"localhost"改为"%"。

解决办法:

1、改表法

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

  2. Database changed

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

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

2、授权法

允许用户user使用密码password从任何主机连接到mysql服务器。

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

  2. mysql> flush privileges;

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

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

  2. mysql> flush privileges;

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

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

  2. mysql> flush privileges;

插入数据

  1. # 插入数据

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

使用操作

执行SQL

  1. __author__ = 'Golden'

  2. #!/usr/bin/env python3

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

  4. import pymysql

  5.  

  6. # 创建连接

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

  1. # 创建游标

  2. cursor = conn.cursor()

  3. # 执行SQL,并返回受影响行数

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

  5. print(effect_row)

  6. # 取出一条数据

  7. print(cursor.fetchone())

  8. # 取出前n条数据

  9. print("*********************************************************")

  1. print(cursor.fetchmany(5))

  2. # 取出所有数据

  3. print("*********************************************************")

  1. print(cursor.fetchall())

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

  1. conn.commit()

  2. # 关闭游标

  3. cursor.close()

  4. # 关闭连接

  5. conn.close()

可能报错:pymysql.err.InternalError: (1130, "Host '192.168.31.1' is not allowed to connect to this MySQL server")

可能是因为你的账号不允许从远程登录,只能在localhost登录。只需要在localhost的那台电脑登录mysql,更改对应数据库里的"user"表里的"host"项,把"localhost"改为"%"。

解决办法:

1、改表法

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

  2. Database changed

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

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

2、授权法

允许用户user使用密码password从任何主机连接到mysql服务器。

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

  2. mysql> flush privileges;

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

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

  2. mysql> flush privileges;

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

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

  2. mysql> flush privileges;

插入数据

  1. # 插入数据

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

1.创建连接:

import pymysql

#创建连接
conn=pymysql.connect(host="localhost",port=3306,user="root",passwd="123456",db="python_test")
#创建游标
cursor=conn.cursor()
#..............操作过程
#关闭游标
cursor.close()
#关闭连接
conn.close()

1.创建连接:

import pymysql

#创建连接
conn=pymysql.connect(host="localhost",port=3306,user="root",passwd="123456",db="python_test")
#创建游标
cursor=conn.cursor()
#..............操作过程
#关闭游标
cursor.close()
#关闭连接
conn.close()

5、移动游标

操作都是靠游标,那对游标的控制也是必须的

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

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

SQLAlchemy ORM

SQLAlchemy ORM

2.执行创建表:

import pymysql

conn=pymysql.connect(host="localhost",port=3306,user="root",password="123456",db="it",charset="utf8")

cursor=conn.cursor()

sql="""
create table user(
id int PRIMARY KEY auto_increment,
username VARCHAR(20),
password VARCHAR(20),
address VARCHAR(35) 
)
"""
cursor.execute(sql)

conn.commit()
cursor.close()
conn.close()

2.执行查询:

import pymysql

#创建连接
conn=pymysql.connect(host="localhost",port=3306,user="root",passwd="123456",db="python_test",charset="utf8")
#创建游标
cursor=conn.cursor()

cursor.execute("select * from student;")
print(cursor.fetchone())#获取一条
print(cursor.fetchmany(2))#获取两条
print(cursor.fetchall())#获取结果集合中的全部

#关闭游标
cursor.close()
#关闭连接
conn.close()

6、fetch数据类型

默认拿到的数据是小括号,元祖类型,如果是字典的话会更方便操作,那方法来了:

# 关于默认获取的数据是元祖类型,如果想要或者字典类型的数据,即:

#!/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(cursor=pymysql.cursors.DictCursor)
r = cursor.execute("call p1()")

result = cursor.fetchone()

conn.commit()
cursor.close()
conn.close()

ORM介绍

对象关系映射(Object Relational Mapping,简称ORM),是一种程序技术,用于实现面向对象编程语言里不同类型系统的数据之间的转换。从效果上说,它其实是创建了一个可在编程语言里使用的——"虚拟对象数据库"。

面向对象是从软件工程基本原则(如耦合、聚合、封装)的基础上发展起来的,而关系数据库则是从数学理论发展而来的,两套理论存在显著的区别。为了解决这个不匹配的现象,对象关系映射技术应运而生。

对象关系映射(Object Relational Mapping)提供了概念性的、易于理解的模型化数据的方法。ORM方法论基于三个核心原则。简单,以最基本的形式建模数据。传达性,数据库结构被任何人都能理解的语言文档化。精确性,基于数据模型创建正确标准化的结构。典型地,建模者通过收集来自那些熟悉应用程序但不熟练数据建模者开发信息模型。建模者必须能够用非技术企业专家可以理解的术语在概念层次上与数据结构进行通讯。建模者也必须能以简单的单元分析信息,对样本数据进行处理。ORM专门被设计为改进这种联系。

澳门新萄京官方网站 4

ORM介绍

对象关系映射(Object Relational Mapping,简称ORM),是一种程序技术,用于实现面向对象编程语言里不同类型系统的数据之间的转换。从效果上说,它其实是创建了一个可在编程语言里使用的——"虚拟对象数据库"。

面向对象是从软件工程基本原则(如耦合、聚合、封装)的基础上发展起来的,而关系数据库则是从数学理论发展而来的,两套理论存在显著的区别。为了解决这个不匹配的现象,对象关系映射技术应运而生。

对象关系映射(Object Relational Mapping)提供了概念性的、易于理解的模型化数据的方法。ORM方法论基于三个核心原则。简单,以最基本的形式建模数据。传达性,数据库结构被任何人都能理解的语言文档化。精确性,基于数据模型创建正确标准化的结构。典型地,建模者通过收集来自那些熟悉应用程序但不熟练数据建模者开发信息模型。建模者必须能够用非技术企业专家可以理解的术语在概念层次上与数据结构进行通讯。建模者也必须能以简单的单元分析信息,对样本数据进行处理。ORM专门被设计为改进这种联系。

澳门新萄京官方网站 5

3.执行查询:

import pymysql

#创建连接
conn=pymysql.connect(host="localhost",port=3306,user="root",passwd="123456",db="python_test",charset="utf8")
#创建游标
cursor=conn.cursor()

cursor.execute("select * from student;")
print(cursor.fetchone())#获取一条
print(cursor.fetchmany(2))#获取两条
print(cursor.fetchall())#获取结果集合中的全部

#关闭游标
cursor.close()
#关闭连接
conn.close()

3.执行插入、修改、删除:

import pymysql

#创建连接
conn=pymysql.connect(host="localhost",port=3306,user="root",password="123456",db="python_test",charset="utf8")
#创建游标
cursor=conn.cursor()

print("-----------插入----------------")
cursor.execute("insert into student values ('nazha',2000,'男');")
cursor.execute("select * from student;")
print(cursor.fetchall())
print("-----------插入----------------")
#cursor.executemany(self,query,args)
cursor.executemany("insert into student value(%s,%s,%s);",[('zhangsan',18,'男'),('lisi',18,'男')])
cursor.execute("select * from student;")
print(cursor.fetchall())
print("-----------修改----------------")
cursor.execute("update student set name = 'zhangsan1' where name = 'zhangsan';")
cursor.execute("select * from student;")
print(cursor.fetchall())
print("----------删除-----------------")
cursor.execute("delete from student where name = 'lisi';")
cursor.execute("select * from student;")
print(cursor.fetchall())
print("---------------------------")

#需要提交才能插入、成功修改、删除
conn.commit()
#关闭游标
cursor.close()
#关闭连接
conn.close()

结果:

(('lilei', 18, '男'), ('hanmeimei', 18, '女'), ('huluwa', 18, '男'), ('sunwukong', 18, '男'), ('baigujing', 3000, '女'), ('nazha', 2000, '男'))
---------------------------
(('lilei', 18, '男'), ('hanmeimei', 18, '女'), ('huluwa', 18, '男'), ('sunwukong', 18, '男'), ('baigujing', 3000, '女'), ('nazha', 2000, '男'), ('zhangsan', 18, '男'), ('lisi', 18, '男'))
---------------------------
(('lilei', 18, '男'), ('hanmeimei', 18, '女'), ('huluwa', 18, '男'), ('sunwukong', 18, '男'), ('baigujing', 3000, '女'), ('nazha', 2000, '男'), ('zhangsan1', 18, '男'), ('lisi', 18, '男'))
---------------------------
(('lilei', 18, '男'), ('hanmeimei', 18, '女'), ('huluwa', 18, '男'), ('sunwukong', 18, '男'), ('baigujing', 3000, '女'), ('nazha', 2000, '男'), ('zhangsan1', 18, '男'))
---------------------------

7、利用 with 自动关闭

每次连接数据库都需要连接和关闭,啊,好多代码,那么方法又来了:

是不是很屌啊?

# 利用with定义函数

    @contextlib.contextmanager
    def mysql(self, host='127.0.0.1', port=3306, user='nick', passwd='', db='db1', charset='utf8'):
        self.conn = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db, charset=charset)
        self.cuersor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)

        try:
            yield self.cuersor
        finally:
            self.conn.commit()
            self.cuersor.close()
            self.conn.close()

# 执行
with mysql() as cuersor:
   print(cuersor)
   # 操作MySQL代码块

 

ORM的优点

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

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

ORM的优点

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

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

4.执行插入、修改、删除:

import pymysql

#创建连接
conn=pymysql.connect(host="localhost",port=3306,user="root",password="123456",db="python_test",charset="utf8")
#创建游标
cursor=conn.cursor()

print("-----------插入----------------")
cursor.execute("insert into student values ('nazha',2000,'男');")
cursor.execute("select * from student;")
print(cursor.fetchall())
print("-----------插入----------------")
#cursor.executemany(self,query,args)
cursor.executemany("insert into student value(%s,%s,%s);",[('zhangsan',18,'男'),('lisi',18,'男')])
cursor.execute("select * from student;")
print(cursor.fetchall())
print("-----------修改----------------")
cursor.execute("update student set name = 'zhangsan1' where name = 'zhangsan';")
cursor.execute("select * from student;")
print(cursor.fetchall())
print("----------删除-----------------")
cursor.execute("delete from student where name = 'lisi';")
cursor.execute("select * from student;")
print(cursor.fetchall())
print("---------------------------")

#需要提交才能插入、成功修改、删除
conn.commit()
#关闭游标
cursor.close()
#关闭连接
conn.close()

结果:

(('lilei', 18, '男'), ('hanmeimei', 18, '女'), ('huluwa', 18, '男'), ('sunwukong', 18, '男'), ('baigujing', 3000, '女'), ('nazha', 2000, '男'))
---------------------------
(('lilei', 18, '男'), ('hanmeimei', 18, '女'), ('huluwa', 18, '男'), ('sunwukong', 18, '男'), ('baigujing', 3000, '女'), ('nazha', 2000, '男'), ('zhangsan', 18, '男'), ('lisi', 18, '男'))
---------------------------
(('lilei', 18, '男'), ('hanmeimei', 18, '女'), ('huluwa', 18, '男'), ('sunwukong', 18, '男'), ('baigujing', 3000, '女'), ('nazha', 2000, '男'), ('zhangsan1', 18, '男'), ('lisi', 18, '男'))
---------------------------
(('lilei', 18, '男'), ('hanmeimei', 18, '女'), ('huluwa', 18, '男'), ('sunwukong', 18, '男'), ('baigujing', 3000, '女'), ('nazha', 2000, '男'), ('zhangsan1', 18, '男'))
---------------------------

4.设置支持中文【添加charset=”utf8”】:

import pymysql

#创建连接
# conn=pymysql.connect(host="localhost",port=3306,user='root',passwd='123456',db='python_test')
conn=pymysql.connect(host="localhost",port=3306,user='root',passwd='123456',db='python_test',charset="utf8")

#创建游标
cursor = conn.cursor()

effect_row= cursor.execute("select * from student;")


print("执行成功,受影响行数:",effect_row)
print(cursor.fetchall())

conn.commit()

cursor.close()
conn.close()

添加前:

添加后:

 

 

 

 


二、SQLAlchemy

SQLAlchemy 简称 ORM 框架,该框架建立在数据库的 API 之上,使用关系对象映射来进行数据库操作;

简言之便是:将类对象转换成 SQL 语句,然后使用数据 API 执行 SQL 语句并获取执行结果。

ORM的缺点

1、自动化意味着映射和关联管理,代价是牺牲性能。现在各种ORM框架都在尝试使用各种方法(如LazyLoad、Cache)来减轻性能的影响,效果比较显著。

2、面向对象的查询语言(OQL)作为一种数据库与对象之间的过渡,虽然隐藏了数据层面的业务抽象,但并不能完全的屏蔽掉数据库层的设计,并且无疑将增加学习成本。

3、对于复杂查询,ORM仍然力不从心。虽然可以实现,但是不值得。视图可以解决大部分calculated column、case、group、having、order by、exists,但是查询条件a and b and not c and (d or e)不能很好解决。

ORM的缺点

1、自动化意味着映射和关联管理,代价是牺牲性能。现在各种ORM框架都在尝试使用各种方法(如LazyLoad、Cache)来减轻性能的影响,效果比较显著。

2、面向对象的查询语言(OQL)作为一种数据库与对象之间的过渡,虽然隐藏了数据层面的业务抽象,但并不能完全的屏蔽掉数据库层的设计,并且无疑将增加学习成本。

3、对于复杂查询,ORM仍然力不从心。虽然可以实现,但是不值得。视图可以解决大部分calculated column、case、group、having、order by、exists,但是查询条件a and b and not c and (d or e)不能很好解决。

5.设置支持中文【创建连接时添加charset=”utf8”】:

import pymysql

#创建连接
# conn=pymysql.connect(host="localhost",port=3306,user='root',passwd='123456',db='python_test')
conn=pymysql.connect(host="localhost",port=3306,user='root',passwd='123456',db='python_test',charset="utf8")

#创建游标
cursor = conn.cursor()

effect_row= cursor.execute("select * from student;")


print("执行成功,受影响行数:",effect_row)
print(cursor.fetchall())

conn.commit()

cursor.close()
conn.close()

添加前:

澳门新萄京官方网站 6

添加后:

澳门新萄京官方网站 7

 

 

 

 


使用sqlalchemy操作mysql:

 

1、下载安装

pip3 install SQLAlchemy

澳门新萄京官方网站 8

需要注意了:SQLAlchemy 自己无法操作数据库,必须结合 pymsql 等第三方插件,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

常用的ORM框架

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

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

3、EclipseLink一个可扩展的支持JPA的ORM框架,提供强大的缓存功能,缓存支持集群。

4、Apache OJB等等。

5、在Python中最著名的ORM框架是SQLAlchemy。

常用的ORM框架

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

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

3、EclipseLink一个可扩展的支持JPA的ORM框架,提供强大的缓存功能,缓存支持集群。

4、Apache OJB等等。

5、在Python中最著名的ORM框架是SQLAlchemy。

使用sqlalchemy操作mysql:

 

介绍:

  • ORM 将数据库中的表与面向对象语言中的类建立了一种对应关系,【ORM可以说是参照映射来处理数据的模型,比如说:需要创建一个表,可以定义一个类,而这个类存在与表相映射的属性,那么可以通过操作这个类来创建一个表】
  • sqlmary是一个mysql的ORM

 2、内部处理

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

澳门新萄京官方网站 9#!/usr/bin/env python # -*- coding:utf-8 -*- from sqlalchemy import create_engine engine = create_engine("mysql pymysql://root:[email protected]:3306/t1", max_overflow=5) # 执行SQL # cur = engine.execute( # "INSERT INTO hosts (host, color_id) VALUES ('1.1.1.22', 3)" # ) # 新插入行自增ID # cur.lastrowid # 执行SQL # cur = engine.execute( # "INSERT INTO hosts (host, color_id) VALUES(%s, %s)",[('1.1.1.22', 3),('1.1.1.221', 3),] # ) # 执行SQL # cur = engine.execute( # "INSERT INTO hosts (host, color_id) VALUES (%(host)s, %(color_id)s)", # host='1.1.1.99', color_id=3 # ) # 执行SQL # cur = engine.execute('select * from hosts') # 获取第一行数据 # cur.fetchone() # 获取第n行数据 # cur.fetchmany(3) # 获取所有数据 # cur.fetchall() View Code

SQLAlchemy安装

SQLAlchemy的理念是,SQL数据库的量级和性能重要于对象集合,而对象集合的抽象又重要与表和行。

澳门新萄京官方网站 10

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. 更多详见:

安装sqlalchemy

  1. pip3 install sqlalchemy

SQLAlchemy安装

SQLAlchemy的理念是,SQL数据库的量级和性能重要于对象集合,而对象集合的抽象又重要与表和行。

澳门新萄京官方网站 11

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. 更多详见:

安装sqlalchemy

  1. pip3 install sqlalchemy

介绍:

  • ORM 将数据库中的表与面向对象语言中的类建立了一种对应关系,【ORM可以说是参照映射来处理数据的模型,比如说:需要创建一个表,可以定义一个类,而这个类存在与表相映射的属性,那么可以通过操作这个类来创建一个表】
  • sqlmary是一个mysql的ORM

前提:

  • 安装模块:pip3 install sqlalchemy

3、ORM功能使用

使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有组件对数据进行操作。

根据类创建对象,对象转换成SQL,执行SQL。

SQLAlchemy基本使用

创建一个表。

SQL实现:

  1. CREATE TABLE USER(

  2.    ID INTEGER NOT NULL AUTO_INCREMENT,

  1.    NAME VARCHAR(32),

  2.    PASSWORD VARCHAR(64),

  3.    PRIMARY KET (ID)

  4. )

ORM实现:

  1. __author__ = 'Golden'

  2. #!/usr/bin/env python3

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

  4.  

  5. import sqlalchemy

  6.  

  7. from sqlalchemy import create_engine

  1. from sqlalchemy.ext.declarative import declarative_base

  2. from sqlalchemy import Column,Integer,String

  3.  

  4. # echo=True打印信息

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

  1.                        encoding='utf-8',echo=True)
  1.  

  2. # 生成orm基类

  3. Base = declarative_base()

  4.  

  5. class User(Base):

  6.     # 表名

  7.     __tablename__ = 'USER'

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

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

  10.     NAME = Column(String(32))

  11.     PASSWORD = Column(String(64))

  1. # 创建表结构

  2. Base.metadata.create_all(engine)

除了上面的创建表之外,还有一种创建表的方式。

  1. __author__ = 'Golden'

  2. #!/usr/bin/env python3

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

  4.  

  5. import sqlalchemy

  6.  

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

  1. from sqlalchemy.orm import mapper
  1.  

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

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

  2.  

  3. user = Table('USER_TABLE',metadata,

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

  2.  

  3.  

  4. class User(object):

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

  6.         self.name = name

  7.         self.fullname = fullname

  1.         self.password = password
  1.  

  2. mapper(User,user)

  3.  

  4. # 创建表结构

  5. metadata.create_all(engine)

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

使用ORM创建一条数据。

  1. __author__ = 'Golden'

  2. #!/usr/bin/env python3

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

  4.  

  5. import sqlalchemy

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

  7. from sqlalchemy.orm import sessionmaker

  1. from sqlalchemy.ext.declarative import declarative_base

  2.  

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

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

  2.  

  3. class User(Base):

  4.     __tablename__ = 'USER'

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

  6.     NAME = Column(String(32))

  7.     PASSWORD = Column(String(64))

  1.  

  2. Base.metadata.create_all(engine)

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

  2. # 生成session实例

  3. Session = Session_class()

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

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

  6. # 此时还没有创建对象

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

  1. # 把需要创建的数据对象添加到session
  1. Session.add(user_obj)

  2. # 此时还没有创建对象

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

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

  2. Session.commit()

SQLAlchemy基本使用

创建一个表。

SQL实现:

  1. CREATE TABLE USER(

  2.    ID INTEGER NOT NULL AUTO_INCREMENT,

  1.    NAME VARCHAR(32),

  2.    PASSWORD VARCHAR(64),

  3.    PRIMARY KET (ID)

  4. )

ORM实现:

  1. __author__ = 'Golden'

  2. #!/usr/bin/env python3

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

  4.  

  5. import sqlalchemy

  6.  

  7. from sqlalchemy import create_engine

  1. from sqlalchemy.ext.declarative import declarative_base

  2. from sqlalchemy import Column,Integer,String

  3.  

  4. # echo=True打印信息

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

  1.                        encoding='utf-8',echo=True)
  1.  

  2. # 生成orm基类

  3. Base = declarative_base()

  4.  

  5. class User(Base):

  6.     # 表名

  7.     __tablename__ = 'USER'

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

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

  10.     NAME = Column(String(32))

  11.     PASSWORD = Column(String(64))

  1. # 创建表结构

  2. Base.metadata.create_all(engine)

除了上面的创建表之外,还有一种创建表的方式。

  1. __author__ = 'Golden'

  2. #!/usr/bin/env python3

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

  4.  

  5. import sqlalchemy

  6.  

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

  1. from sqlalchemy.orm import mapper
  1.  

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

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

  2.  

  3. user = Table('USER_TABLE',metadata,

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

  2.  

  3.  

  4. class User(object):

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

  6.         self.name = name

  7.         self.fullname = fullname

  1.         self.password = password
  1.  

  2. mapper(User,user)

  3.  

  4. # 创建表结构

  5. metadata.create_all(engine)

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

使用ORM创建一条数据。

  1. __author__ = 'Golden'

  2. #!/usr/bin/env python3

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

  4.  

  5. import sqlalchemy

  6. 澳门新萄京官方网站,from sqlalchemy import create_engine,Column,Integer,String

  7. from sqlalchemy.orm import sessionmaker

  1. from sqlalchemy.ext.declarative import declarative_base

  2.  

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

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

  2.  

  3. class User(Base):

  4.     __tablename__ = 'USER'

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

  6.     NAME = Column(String(32))

  7.     PASSWORD = Column(String(64))

  1.  

  2. Base.metadata.create_all(engine)

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

  2. # 生成session实例

  3. Session = Session_class()

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

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

  6. # 此时还没有创建对象

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

  1. # 把需要创建的数据对象添加到session
  1. Session.add(user_obj)

  2. # 此时还没有创建对象

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

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

  2. Session.commit()

前提:

  • 安装模块:pip3 install sqlalchemy

使用:

  • 导入模块:
    • 导入连接数据库模块:from sqlalchemy import create_engine
    • 如果需要创建新表,则需要导入表结构定义模块:from sqlalchemy.ext.declarative import declarative_base
    • 导入其他相关模块,主要是映射的类,如字段映射为Column,如数据类型int映射为Integer,如索引映射为Index,需要什么导入什么:from sqlalchemy import Column,Integer,String
    • 映射关系:
      数据库中 映射 模块【如果可以从多个模块处导入,用 | 分隔】【方式太多,可能有漏,但不影响导入】
      Table from sqlalchemy import Table
      int Integer from sqlalchemy.types import Integer
      索引 Index from sqlalchemy import Index
           
      字段、列 Column from sqlalchemy import Column
      varchar VARCHAR、String from sqlalchemy.types import String    |   from sqlalchemy import String
      外键 ForeignKey
      from sqlalchemy import ForeignKey
           
           
  • 连接数据库:连接对象=create_engine('数据库类型 数据库驱动名称://用户名:口令@机器地址:端口号/数据库名',编码格式,echo)
    • sqlalchemy支持多种API操作模式,可以使用不同的模式来连接操作数据库:'数据库类型 数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'
      • 比如pymsql【py3常用】:mysql pymysql://<username>:<password>@<host>/<dbname>[?<options>
    • 其他参数:
      • echo是否显示ORM转成实际sql语句的过程,echo=True为显
      • encoding为连接时使用的字符集

 

a、创建表

澳门新萄京官方网站 12#!/usr/bin/env python # -*- coding:utf-8 -*- from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy import create_engine # 指定字符集、最大连接池数 engine = create_engine("mysql pymysql://root:[email protected]:3306/suoning4?charset=utf8", max_overflow=5) Base = declarative_base() # 创建单表 class Users(Base): # 表名 __tablename__ = 'users' # 表字段 id = Column(Integer, primary_key=True) # 主键、默认自增 name = Column(String(32)) extra = Column(String(16)) __table_args__ = ( UniqueConstraint('id', 'name', name='uix_id_name'), # 唯一索引 Index('ix_id_name', 'name', 'extra'), # 普通索引 ) def __repr__(self): # 查是输出的内容格式,本质还是对象 return "%s-%s" %(self.id, self.name) # 一对多 class Favor(Base): __tablename__ = 'favor' nid = Column(Integer, primary_key=True) caption = Column(String(50), default='red', unique=True) # 默认值、唯一索引 def __repr__(self): return "%s-%s" %(self.nid, self.caption) class Person(Base): __tablename__ = 'person' nid = Column(Integer, primary_key=True) name = Column(String(32), index=True, nullable=True) favor_id = Column(Integer, ForeignKey("favor.nid")) # 与生成表结构无关,仅用于查询方便 favor = relationship("Favor", backref='pers') # 多对多 class ServerToGroup(Base): # 关系表要放对应表上面,否则找不到 __tablename__ = 'servertogroup' nid = Column(Integer, primary_key=True, autoincrement=True) server_id = Column(Integer, ForeignKey('server.id')) # 外键 group_id = Column(Integer, ForeignKey('group.id')) group = relationship("Group", backref='s2g') server = relationship("Server", backref='s2g') class Group(Base): __tablename__ = 'group' id = Column(Integer, primary_key=True) name = Column(String(64), unique=True, nullable=False) # 不能为空 port = Column(Integer, default=22) # group = relationship('Group',secondary=ServerToGroup,backref='host_list') class Server(Base): __tablename__ = 'server' id = Column(Integer, primary_key=True, autoincrement=True) # 自增 hostname = Column(String(64), unique=True, nullable=False) def init_db(): # 创建表 Base.metadata.create_all(engine) def drop_db(): # 删除表 Base.metadata.drop_all(engine) 创建表

注:设置外检的另一种方式 ForeignKeyConstraint(['other_id'], ['othertable.other_id'])

查询、修改

  1. __author__ = 'Golden'

  2. #!/usr/bin/env python3

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

  4.  

  5. import sqlalchemy

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

  7. from sqlalchemy.ext.declarative import declarative_base

  8. from sqlalchemy.orm import sessionmaker

  1. engine = create_engine("mysql pymysql://root:123456@192.168.31.134/test_db",
  1.                        encoding='utf-8',echo=False)
  1. Base = declarative_base()

  2. class User(Base):

  3.     __tablename__ = 'USER'

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

  5.     NAME = Column(String(32))

  6.     PASSWORD = Column(String(64))

  1.  

  2.     def __repr__(self):

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

  4.  

  5. Session_class = sessionmaker(bind=engine)

  6. Session = Session_class()

  7. # first 查出NAME='Golden'的第一个

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

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

  1. # NAME='Golden'

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

  3. print(data[0].PASSWORD)

  4. #all 查出所有

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

  6. print(data_2)

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

  8. print(data_3)

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

  10. print(data_4)

  11. # 多条件查询

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

  13. print(data_5)

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

  15. print(data_6)

  16. # 修改

  17. data_6.NAME = 'zhangsan'

  18. data_6.PASSWORD = '110'

  19. # 提交

  20. Session.commit()

查询、修改

  1. __author__ = 'Golden'

  2. #!/usr/bin/env python3

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

  4.  

  5. import sqlalchemy

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

  7. from sqlalchemy.ext.declarative import declarative_base

  8. from sqlalchemy.orm import sessionmaker

  1. engine = create_engine("mysql pymysql://root:123456@192.168.31.134/test_db",
  1.                        encoding='utf-8',echo=False)
  1. Base = declarative_base()

  2. class User(Base):

  3.     __tablename__ = 'USER'

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

  5.     NAME = Column(String(32))

  6.     PASSWORD = Column(String(64))

  1.  

  2.     def __repr__(self):

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

  4.  

  5. Session_class = sessionmaker(bind=engine)

  6. Session = Session_class()

  7. # first 查出NAME='Golden'的第一个

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

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

  1. # NAME='Golden'

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

  3. print(data[0].PASSWORD)

  4. #all 查出所有

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

  6. print(data_2)

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

  8. print(data_3)

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

  10. print(data_4)

  11. # 多条件查询

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

  13. print(data_5)

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

  15. print(data_6)

  16. # 修改

  17. data_6.NAME = 'zhangsan'

  18. data_6.PASSWORD = '110'

  19. # 提交

  20. Session.commit()

使用:

  • 导入模块:
    • 导入连接数据库模块:from sqlalchemy import create_engine
    • 如果需要创建新表,则需要导入表结构定义模块:from sqlalchemy.ext.declarative import declarative_base
    • 导入其他相关模块,主要是映射的类,如字段映射为Column,如数据类型int映射为Integer,如索引映射为Index,需要什么导入什么:from sqlalchemy import Column,Integer,String
    • 映射关系:
      数据库中 映射 模块【如果可以从多个模块处导入,用 | 分隔】【方式太多,可能有漏,但不影响导入】
      Table from sqlalchemy import Table
      int Integer from sqlalchemy.types import Integer
      索引 Index from sqlalchemy import Index
           
      字段、列 Column from sqlalchemy import Column
      varchar VARCHAR、String from sqlalchemy.types import String    |   from sqlalchemy import String
      外键 ForeignKey
      from sqlalchemy import ForeignKey
           
           
  • 连接数据库:连接对象=create_engine('数据库类型 数据库驱动名称://用户名:口令@机器地址:端口号/数据库名',编码格式,echo)
    • sqlalchemy支持多种API操作模式,可以使用不同的模式来连接操作数据库:'数据库类型 数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'
      • 比如pymsql【py3常用】:mysql pymysql://<username>:<password>@<host>/<dbname>[?<options>
    • 其他参数:
      • echo是否显示ORM转成实际sql语句的过程,echo=True为显
      • encoding为连接时使用的字符集

 

操作:

基本操作:

创建新表

方法一: 使用declarative

1.导入模块from sqlalchemy.ext.declarative import declarative_base

2.根据需要的元素来导入模块``from sqlalchemy import Column

  • 导入需要的数据类型【注:数据类型在sqlalchemy中也有指向,所以也可以from sqlalchemy import String,Integer,Char】:from sqlalchemy.types import *

3.创建连接,

3.使用declarative_base来获得一个类对象,此处我定义该对象为Base

定义一个类,继承declarative_base生成的类对象Base

使用__tablename__来定义表名

使用 列名 = Column(数据类型,其他列属性…)等类似格式来定义字段

  • nullable=False 代表这一列不可以为空,index=True 表示在该列创建索

创建表:Base.metadata.create_all(engine)

from sqlalchemy import create_engine#负责导入连接数据库的对象
from sqlalchemy.ext.declarative import declarative_base#负责导入创建表的api
from sqlalchemy import Column #负责导入列
from sqlalchemy.types import *#负责导入列类型

#数据库连接
engine = create_engine("mysql pymysql://root:[email protected]/python_test",encoding ='utf-8',echo=True)
#方式一:
Base = declarative_base()

class User(Base):
    __tablename__ = 'user'#表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

Base.metadata.create_all(engine)

 

  • 方法二:使用Table

    • 1.导入模块: from sqlalchemy import Table
    • 2.连接数据库:engine=create_engine(….)
    • 3.获取meta类,metadata=MetaData(engine)
    • 4.创建Table对象( 比如:t=Table("group" ,metadata,Column("id",Integer,primary_key=True),Column("group_name",String(32)))  )
    • 5.创建表:metadata.create_all()

      from sqlalchemy import create_engine
      from sqlalchemy import Table
      from sqlalchemy import MetaData
      from sqlalchemy import Column
      from sqlalchemy.types import *
      from sqlalchemy.ext.declarative import declarative_base
      
        ####下面的注释部分可以与上面一句的未注释的替换
        engine=create_engine("mysql pymysql://root:[email protected]/python_test",encoding ='utf-8',echo=True)
        metadata=MetaData(engine) ###  
        # Base=declarative_base()

        t=Table(
        "group" ,metadata,#表名
        # "group",Base.metadata,
        Column("id",Integer,primary_key=True),
        Column("group_name",String(32))
        )

        metadata.create_all()
        # Base.metadata.create_all(engine)

 

  • 查看表:
    • db_table=Base.metadata.tables#仅有当次运行中继承了Base而创建的新表
    • db_tables=engine.table_names()#仅有表名
  • 删除表:Base.metadata.drop_all(engine)
  • 修改表:
    • 直接修改表所对应的类结构是无法修改成功的,
    • 如果需要修改在程序中自定义的表的结构,那么需要手动修改,手动的方式有很多,比如直接engine.execute(sql语句)。。。。

 

 

 

  • 插入 数据【这里仅针对使用declarative_base创建的表,对于不是程序中才创建的,可以自己使用declarative_base建一个类来映射之前的表,只要映射一致,就能插入数据】

    • 1.连接数据库:engine=create_engine(….)
    • 1.导入模块:from sqlalchemy.orm import sessionmaker
    • 2.获取session_class类:Session_class=sessionmaker(bind=engine)
    • 3.获取session对象:s=Session_class()
    • 4.使用s来添加:
      • s.add()
      • s.add_all()
    • 5.提交数据: s.commit()

      from sqlalchemy import create_engine#负责导入连接数据库的对象
      from sqlalchemy.ext.declarative import declarative_base#负责导入创建表的api
      from sqlalchemy import Column #负责导入列
      from sqlalchemy.types import *#负责导入列类型
      
      #数据库连接
      engine = create_engine("mysql pymysql://root:[email protected]/python_test",encoding ='utf-8',echo=True)
      Base = declarative_base()
      class User(Base):
          __tablename__ = 'user'#表名
          id = Column(Integer,primary_key=True)
          name = Column(String(32))
          password = Column(String(64))
          group = Column(Integer)
      
      Base.metadata.create_all(engine)
      
        from sqlalchemy.orm import sessionmaker
        obj1=User(name='lisi',password='123456',group=1)

        Session=sessionmaker(bind=engine)
        s=Session()
        s.add(obj1)#

        users=[User(name='wangwu',password='123456',group=1),
               User(name='zhaoliu', password='123456', group=1),
               User(name='sunqi', password='123456', group=1)
               ]
        s.add_all(users)#

        s.commit()

 

 

  • 查找 数据

    • 同样适用sessionmaker来查找,与插入相同,需要创建session_class对象(我定义为s)
    • 使用s来查找:

      • s.query(表对应类)是相当于select对应表,后面可以跟first()、all()等来获取结果,也可以加filter、filter_by等来筛选结果
      • 获取全部 : s.query(表对应类).all() 【返回的是一个结果列表】
      • 查找指定: s.query(表对应类).filter(表对应类.xxx==xxxx)【filter获取的是结果集,需要使用all(),first()等方法来获取结果】
      • 查找指定: s.query(表对应类).filter_by(xxx=xxxx)
      • 附:虽然返回值是一个结果集,但这个集合是一个类对象,如果想查看内容,需要在表对应的类中增加__repr__方法。
      • 多个筛选条件使用“,”隔开
      • 常见可用筛选条件【User是一个表对应的类】:

        使用filter,filter_by时:
        User.name==’lisi’
        User.name.like(“lisi%”))
        User.name != ’lisi’
        User.name.any()
        or_(筛选条件) 【代表里面的多个筛选条件以or组合,需要导入:from sqlalchemy import or_】
        and_(筛选条件) 【代表里面的多个筛选条件以and组合,需要导入:from sqlalchemy import and_】【默认是and】
        in_([筛选条件])  【使用比如User.name.in_(['xiaxia', 'lilei', 'lover'])】
        使用all时,以下是放在query里面的:
        User.name  [这相当于不使用where的select name from 表]
         
         
      • 连接查询使用:s.query(表对应类).join(表对应类.xxx==xxxx)

      • 还有group_by,order_by等用法这里不做讲解[什么时候有空再补吧!]

        from sqlalchemy import create_engine#负责导入连接数据库的对象
        from sqlalchemy.ext.declarative import declarative_base#负责导入创建表的api
        from sqlalchemy import Column #负责导入列
        from sqlalchemy.types import *#负责导入列类型
        
        #数据库连接
        engine = create_engine("mysql pymysql://root:[email protected]/python_test",encoding ='utf-8')
        Base = declarative_base()
        class User(Base):
            __tablename__ = 'user'#表名
            id = Column(Integer,primary_key=True)
            name = Column(String(32))
            password = Column(String(64))
            group = Column(Integer)
        
            def __repr__(self):
                return "<id:%s name:%s group:%s>"%(self.id,self.name,self.group)
        Base.metadata.create_all(engine)
        
            from sqlalchemy.orm import sessionmaker
            obj1=User(name='lisi',password='123456',group=1)

            Session=sessionmaker(bind=engine)
            s=Session()

            a=s.query(User).all()
            a2=s.query(User).filter(User.name=='lisi').first()
            a3=s.query(User).filter_by(name='lisi').first()



            print(a)
            print(a2)
            print(a3)

 

 

  • 修改 数据:

    • 修改数据的基础是先查找到数据,查找:row=s.query(X).filter(X.xxx=xxx).first()
    • 使用赋值语句修改 :row.xxx=xxxx

      from sqlalchemy import create_engine#负责导入连接数据库的对象
      from sqlalchemy.ext.declarative import declarative_base#负责导入创建表的api
      from sqlalchemy import Column #负责导入列
      from sqlalchemy.types import *#负责导入列类型
      
      #数据库连接
      engine = create_engine("mysql pymysql://root:[email protected]/python_test",encoding ='utf-8')
      Base = declarative_base()
      class User(Base):
          __tablename__ = 'user'#表名
          id = Column(Integer,primary_key=True)
          name = Column(String(32))
          password = Column(String(64))
          group = Column(Integer)
      
          def __repr__(self):
              return "<id:%s name:%s group:%s>"%(self.id,self.name,self.group)
      Base.metadata.create_all(engine)
      
        from sqlalchemy.orm import sessionmaker
        obj1=User(name='lisi',password='123456',group=1)

        Session=sessionmaker(bind=engine)
        s=Session()


        row=s.query(User).filter(User.name=='lisi').first()
        row.name='lisi2'
        s.commit()

 

 

  • 删除 数据:

    • 删除数据的基础是先查找到数据,查找:row=s.query(X).filter(X.xxx=xxx)
    • 使用delete删除:row.delete()

      # coding: utf-8
      from sqlalchemy import create_engine#负责导入连接数据库的对象
      from sqlalchemy.ext.declarative import declarative_base#负责导入创建表的api
      from sqlalchemy import Column #负责导入列
      from sqlalchemy.types import *#负责导入列类型
      
      #数据库连接
      engine = create_engine("mysql pymysql://root:[email protected]/python_test",encoding ='utf-8')
      Base = declarative_base()
      class User(Base):
          __tablename__ = 'user'#表名
          id = Column(Integer,primary_key=True)
          name = Column(String(32))
          password = Column(String(64))
          group = Column(Integer)
          def __repr__(self):
              return "<id:%s name:%s group:%s>"%(self.id,self.name,self.group)
      
      Base.metadata.create_all(engine)
      
        from sqlalchemy.orm import sessionmaker
        obj1=User(name='lisi',password='123456',group=1)

        Session=sessionmaker(bind=engine)
        s=Session()


        a3=s.query(User).filter_by(name='lisi1')
        a3.delete()
        s.commit()

 

外键相关:

  • 外键使用foregin_key创建
  • 类中的relationship的作用:帮助ORM获知他们的外键关系,以便ORM使用外键获取相关数据
    • relationship中的backref的用途:relationship使得可以在一个表中定义的relationshop能被两个表使用,另一个表使用backref来获取相关信息
    • relationship中的foreign_keys的用途:当有多个relationship时,为了避免ORM混淆多个relationship,特别的标注哪个外键是哪个relationship
    • relationship中的secondary的用途:在多对多的关系中,填入的值是中间表,维持两边表关系。
  • 一对一的外键关系:
    • 1.导入模块:from sqlalchemy import Foreign_key
    • 2.建立外键(如:group = Column(Integer,ForeignKey("group.id")),建立关系(如:group_relation=relationship('Group',backref="g_users")
    • 3.插入数据
    • 4.查询到一条数据:如row=s.query(User).filter(User.name=='lisi').first()
    • 5.尝试A表调用关系来获取B(row.group_relation.group_name),B使用backref来获取A的数据(row2.g_users)
    • 下面的实例大概就是“一个开发人员对应一个开发组的关系”
#负责导入连接数据库的对象
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base#负责导入创建表的api
from sqlalchemy import Column,ForeignKey #负责导入列
from sqlalchemy.types import *#负责导入列类型
from sqlalchemy.orm import relationship

#数据库连接
engine = create_engine("mysql pymysql://root:[email protected]/python_test",encoding ='utf-8')
Base = declarative_base()



class Group(Base):
    __tablename__="group"
    id=Column(Integer,primary_key=True)
    group_name=Column(String(32),nullable=False)

    def __repr__(self):
        return "<id:%s group_name:%s>"%(self.id,self.group_name)

class User(Base):
    __tablename__ = 'user'#表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)
    password = Column(String(64),nullable=False)
    group = Column(Integer,ForeignKey("group.id"))#这里创建外键

    group_relation=relationship('Group',backref="g_users")#为ORM指明关系,方便ORM处理,第一个是对应的类
    def __repr__(self):
        return "<id:%s name:%s>"%(self.id,self.name)
Base.metadata.create_all(engine)

from sqlalchemy.orm import sessionmaker
# group1=Group(group_name='python')
# group2=Group(group_name='linux')
# group3=Group(group_name='AI')
# user1=User(name='lisi',password='123456',group=1)
# user2=User(name='zhangsan',password='123456',group=2)
# user3=User(name='wangwu',password='123456',group=3)
# user4=User(name='lilei',password='123456',group=3)


Session=sessionmaker(bind=engine)
s=Session()
# s.add_all([group1,group2,group3,user1,user2,user3,user4])
# s.commit()

# row=s.query(User).filter(User.name=='lisi').first()
row=s.query(User).first()
print(row.group_relation.group_name)#这里User通过关系来获取Group的数据
row2=s.query(Group).first()
print(row2)
print(row2.g_users)#这里Group通过relationship的backref来获取User的数据
  • 一对多关系,外键关联

    • 以一个老师能做一个班的班主任此外还能做另一个班的副班主任为例【即一个老师能对应多个班级】
    • 一对多关系的创建的核心是relationship中的foreign_keys
    • 附:当你建表成功而插入数据失败时,可以尝试先删除掉数据表,有时候因为外键依赖会导致插入失败

      #负责导入连接数据库的对象
      from sqlalchemy import create_engine
      from sqlalchemy.ext.declarative import declarative_base#负责导入创建表的api
      from sqlalchemy import Column,ForeignKey #负责导入列
      from sqlalchemy.types import *#负责导入列类型
      from sqlalchemy.orm import relationship
      
      #数据库连接
      engine = create_engine("mysql pymysql://root:[email protected]/python_test",encoding ='utf-8')
      Base = declarative_base()
      
        class Grade(Base):
            __tablename__="grade"
            id=Column(Integer,primary_key=True)
            grade_name=Column(String(32),nullable=False)

            def __repr__(self):
                return "<id:%s group_name:%s>"%(self.id,self.grade_name)

        class Teacher(Base):
            __tablename__ = 'teacher'#表名
            id = Column(Integer,primary_key=True)
            name = Column(String(32),nullable=False)
            primary_grade = Column(Integer,ForeignKey("grade.id"))
            second_grade = Column(Integer,ForeignKey("grade.id"))

            primary_grade_relation=relationship('Grade',backref="first_teacher",foreign_keys=[primary_grade])
            second_grade_relation=relationship('Grade',backref="second_teacher",foreign_keys=[second_grade])
            def __repr__(self):
                return "<id:%s name:%s>"%(self.id,self.name)


        Base.metadata.create_all(engine)

        from sqlalchemy.orm import sessionmaker
        # grade1=Grade(grade_name='python')
        # grade2=Grade(grade_name='linux')
        # grade3=Grade(grade_name='AI')
        # grade4=Grade(grade_name='Java')
        # t1=Teacher(name='lisi',primary_grade=1,second_grade=2)
        # t2=Teacher(name='zhangsan',primary_grade=2,second_grade=1)
        # t3=Teacher(name='wangwu',primary_grade=4,second_grade=3)
        # t4=Teacher(name='lilei',primary_grade_relation=grade3,second_grade=4)
        #这里外键相关的比如primary_grade=x可以使用primary_grade_relation=对象来代替,
        # 会根据对象来转成对应id,不过问题是不知道grade3的准确id,因为可能创建顺序不一致

        Session=sessionmaker(bind=engine)
        s=Session()
        # s.add_all([grade1,grade2,grade3,grade4])
        # s.add_all([t1,t2,t3,t4])
        # s.commit()

        row=s.query(Teacher).filter(Teacher.name=='lisi').first()

        print(row.name,row.primary_grade_relation.grade_name)#这里Teacher通过关系来获取Grade的数据
        print(row.name,row.second_grade_relation.grade_name)
        row2=s.query(Grade).first()
        print(row2.grade_name,row2.first_teacher)#这里Grade通过relationship的backref来获取Teacher的数据
        print(row2.grade_name,row2.second_teacher)

`` 

多对多外键关联

  • 以选课中一门课能有多名学生,一个学生可以选多门课为示例:
  • 其中relationship中的secondary的值是中间表,负责维持中间表与另外两表的关系,创建多对多的核心是secondary
#负责导入连接数据库的对象
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base#负责导入创建表的api
from sqlalchemy import Column,ForeignKey #负责导入列
from sqlalchemy.types import *#负责导入列类型
from sqlalchemy.orm import relationship

#数据库连接
engine = create_engine("mysql pymysql://root:[email protected]/python_test",encoding ='utf-8')
Base = declarative_base()

class SelectInfo(Base):
    __tablename__="selectClassInfo"
    id=Column(Integer,primary_key=True)
    sid=Column(Integer,ForeignKey("student.id"))
    cid=Column(Integer,ForeignKey("course.id"))


"""使用declarative_base和Table 创建表时,secondary的填写不一样
selectInfo2=Table(
'selectClassInfo',Base.metadata,
Column('sid',Integer,ForeignKey('student.id'))
Column('cid',Integer,ForeignKey('student.id'))
)
"""

class Student(Base):
    __tablename__="student"
    id=Column(Integer,primary_key=True)
    name=Column(String(32),nullable=False)

    def __repr__(self):
        return "<id:%s name:%s>"%(self.id,self.name)

class Course(Base):
    __tablename__ = 'course'
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)

    student_relation=relationship('Student',secondary="selectClassInfo",backref="courses")
    # student_relation=relationship('Student',secondary=selectClassInfo2,backref="courses") 
    # #如果使用Table来创建中间表,上面是这样填的

    def __repr__(self):
        return "<id:%s name:%s>"%(self.id,self.name)


Base.metadata.create_all(engine)

from sqlalchemy.orm import sessionmaker
#
# s1=Student(name='lisi')
# s2=Student(name='zhangsan')
# s3=Student(name='wangwu')
# s4=Student(name='lilei')
# c1=Course(name='python',student_relation=[s1,s2])
# c2=Course(name='linux',student_relation=[s3])
# c3=Course(name='AI',student_relation=[s3,s4])
# c4=Course(name='Java')
# c4.student_relation=[s1,s2,s3,s4]##在一边增加关系之后,在secondary中会加入两边的数据
#
#
#
Session=sessionmaker(bind=engine)
s=Session()
# s.add_all([s1,s2,s3,s4,c1,c2,c3,c4])
# s.commit()

row=s.query(Course).filter(Course.id=='4').first()

print(row.name,row.student_relation)#这里Course通过关系来获取Student的数据
row2=s.query(Student).filter(Student.id=="3").first()
print(row2.name,row2.courses)#这里Student通过relationship的backref来获取Course的数据

 

 

 

 

b、操作表

操作表那必须导入模块,创建相应类,相应增删改查的语法,详细见下code吧^^:

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

# 指定字符集、最大连接池数
engine = create_engine("mysql pymysql://root:[email protected]:3306/suoning4?charset=utf8", max_overflow=5)

Base = declarative_base()

# 创建单表
class Users(Base):
    # 表名
    __tablename__ = 'users'
    # 表字段
    id = Column(Integer, primary_key=True)  # 主键、默认自增
    name = Column(String(32))
    extra = Column(String(16))

    __table_args__ = (
    UniqueConstraint('id', 'name', name='uix_id_name'), # 唯一索引
    Index('ix_id_name', 'name', 'extra'),   # 普通索引
    )

    def __repr__(self):
        # 查是输出的内容格式,本质还是对象
        return "%s-%s" %(self.id, self.name)

# 一对多
class Favor(Base):
    __tablename__ = 'favor'
    nid = Column(Integer, primary_key=True)
    caption = Column(String(50), default='red', unique=True) # 默认值、唯一索引

    def __repr__(self):
        return "%s-%s" %(self.nid, self.caption)

class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    favor_id = Column(Integer, ForeignKey("favor.nid"))
    # 与生成表结构无关,仅用于查询方便
    favor = relationship("Favor", backref='pers')

# 多对多
class ServerToGroup(Base):
    # 关系表要放对应表上面,否则找不到
    __tablename__ = 'servertogroup'
    nid = Column(Integer, primary_key=True, autoincrement=True)
    server_id = Column(Integer, ForeignKey('server.id'))    # 外键
    group_id = Column(Integer, ForeignKey('group.id'))

    group = relationship("Group", backref='s2g')
    server = relationship("Server", backref='s2g')

class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False) # 不能为空
    port = Column(Integer, default=22)
    # group = relationship('Group',secondary=ServerToGroup,backref='host_list')


class Server(Base):
    __tablename__ = 'server'

    id = Column(Integer, primary_key=True, autoincrement=True) # 自增
    hostname = Column(String(64), unique=True, nullable=False)


def init_db():
    # 创建表
    Base.metadata.create_all(engine)

def drop_db():
    # 删除表
    Base.metadata.drop_all(engine)


# 先实例化sessionmaker类,Session对象加括号执行类下的__call__方法,
# 得到session对象,所以session可以调用Session类下的add,add_all等方法
Session = sessionmaker(bind=engine) # 指定引擎
session = Session()

# 增

# 添加一条
obj = Users(name="张三", extra='三儿')
session.add(obj)
# 添加多条
session.add_all([
    Users(name="李四", extra='四儿'),
    Users(name="汪五", extra='五儿'),
])
# 提交
session.commit()

# 删

session.query(Users).filter(Users.id > 2).delete()
session.query(Users).filter_by(id = 1).delete()
session.commit()

# 改

session.query(Users).filter(Users.id > 2).update({"name" : "nick"})
session.query(Users).filter(Users.id > 2).update({"name" : "nick", "extra":"niubily"})
session.query(Users).filter(Users.id > 2).update({Users.name: Users.name   "Suo"}, synchronize_session=False)
session.query(Users).filter(Users.id > 2).update({"num": Users.num   1}, synchronize_session="evaluate")
session.commit()

# 查
# all()结果为对象列表,first()为具体对象

ret = session.query(Users).all()
ret = session.query(Users.name, Users.extra).all()
ret = session.query(Users).filter_by(name='nick').all()
ret = session.query(Users).filter_by(name='nick').first()
print(ret)

 那如何加限制条件等,我要更灵活使用,好吧,还是见下 code:

# 条件
ret = session.query(Users).filter_by(name='nick').all()
ret = session.query(Users).filter(Users.id > 1, Users.name == 'nick').all()
ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'nick').all()
ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()
ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()
ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='nick'))).all()

from sqlalchemy import and_, or_        # 导入模块
ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'nick')).all()
ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'nick')).all()
ret = session.query(Users).filter(
    or_(
        Users.id < 2,
        and_(Users.name == 'nick', Users.id > 3),
        Users.extra != ""
    )).all()


# 通配符
ret = session.query(Users).filter(Users.name.like('n%')).all()
ret = session.query(Users).filter(~Users.name.like('n%')).all()

# 限制
ret = session.query(Users)[1:2]

# 排序
ret = session.query(Users).order_by(Users.name.desc()).all()
ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()

# 分组
from sqlalchemy.sql import func        # 导入模块

ret = session.query(Users).group_by(Users.extra).all()
ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).all()

ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()

# 连表

ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()

ret = session.query(Person).join(Favor).all()

ret = session.query(Person).join(Favor, isouter=True).all()

# isouter=True 理解为 left join ,如果不写为 inner join

# 组合
q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union(q2).all()

q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union_all(q2).all()

 

Python 操作 MySQL 之 pysql 与 ORM,pysqlorm 本文针对 Python 操作 MySQL 主要使用的两种方式讲解: 原生模块 pymsql ORM框...

回滚

  1. __author__ = 'Golden'

  2. #!/usr/bin/env python3

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

  4.  

  5. import sqlalchemy

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

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

操作:

基本操作:

  • 创建新表

    • 方法一: 使用declarative

      • 1.导入模块from sqlalchemy.ext.declarative import declarative_base
      • 2.根据需要的元素来导入模块``from sqlalchemy import Column
        • 导入需要的数据类型【注:数据类型在sqlalchemy中也有指向,所以也可以from sqlalchemy import String,Integer,Char】:from sqlalchemy.types import *
      • 3.创建连接,
      • 3.使用declarative_base来获得一个类对象,此处我定义该对象为Base
      • 定义一个类,继承declarative_base生成的类对象Base
        • 使用__tablename__来定义表名
        • 使用 列名 = Column(数据类型,其他列属性…)等类似格式来定义字段
          • nullable=False 代表这一列不可以为空,index=True 表示在该列创建索
      • 创建表:Base.metadata.create_all(engine)

        from sqlalchemy import create_engine#负责导入连接数据库的对象
        from sqlalchemy.ext.declarative import declarative_base#负责导入创建表的api
        from sqlalchemy import Column #负责导入列
        from sqlalchemy.types import *#负责导入列类型
        
        #数据库连接
        engine = create_engine("mysql pymysql://root:123456@localhost/python_test",encoding ='utf-8',echo=True)
        #方式一:
        Base = declarative_base()
        
        class User(Base):
            __tablename__ = 'user'#表名
            id = Column(Integer,primary_key=True)
            name = Column(String(32))
            password = Column(String(64))
        
        Base.metadata.create_all(engine)
        

     

    • 方法二:使用Table
      • 1.导入模块: from sqlalchemy import Table
      • 2.连接数据库:engine=create_engine(….)
      • 3.获取meta类,metadata=MetaData(engine)
      • 4.创建Table对象( 比如:t=Table("group" ,metadata,Column("id",Integer,primary_key=True),Column("group_name",String(32)))  )
      • 5.创建表:metadata.create_all()
        from sqlalchemy import create_engine
        from sqlalchemy import Table
        from sqlalchemy import MetaData
        from sqlalchemy import Column
        from sqlalchemy.types import *
        from sqlalchemy.ext.declarative import declarative_base
        
            ####下面的注释部分可以与上面一句的未注释的替换
            engine=create_engine("mysql pymysql://root:123456@localhost/python_test",encoding ='utf-8',echo=True)
            metadata=MetaData(engine) ###  
            # Base=declarative_base()

            t=Table(
            "group" ,metadata,#表名
            # "group",Base.metadata,
            Column("id",Integer,primary_key=True),
            Column("group_name",String(32))
            )

            metadata.create_all()
            # Base.metadata.create_all(engine)

 

  • 查看表:
    • db_table=Base.metadata.tables#仅有当次运行中继承了Base而创建的新表
    • db_tables=engine.table_names()#仅有表名
  • 删除表:Base.metadata.drop_all(engine)
  • 修改表:
    • 直接修改表所对应的类结构是无法修改成功的,
    • 如果需要修改在程序中自定义的表的结构,那么需要手动修改,手动的方式有很多,比如直接engine.execute(sql语句)。。。。

 

 

 

  • 插入 数据【这里仅针对使用declarative_base创建的表,对于不是程序中才创建的,可以自己使用declarative_base建一个类来映射之前的表,只要映射一致,就能插入数据】

    • 1.连接数据库:engine=create_engine(….)
    • 1.导入模块:from sqlalchemy.orm import sessionmaker
    • 2.获取session_class类:Session_class=sessionmaker(bind=engine)
    • 3.获取session对象:s=Session_class()
    • 4.使用s来添加:
      • s.add()
      • s.add_all()
    • 5.提交数据: s.commit()

      from sqlalchemy import create_engine#负责导入连接数据库的对象
      from sqlalchemy.ext.declarative import declarative_base#负责导入创建表的api
      from sqlalchemy import Column #负责导入列
      from sqlalchemy.types import *#负责导入列类型
      
      #数据库连接
      engine = create_engine("mysql pymysql://root:123456@localhost/python_test",encoding ='utf-8',echo=True)
      Base = declarative_base()
      class User(Base):
          __tablename__ = 'user'#表名
          id = Column(Integer,primary_key=True)
          name = Column(String(32))
          password = Column(String(64))
          group = Column(Integer)
      
      Base.metadata.create_all(engine)
      
        from sqlalchemy.orm import sessionmaker
        obj1=User(name='lisi',password='123456',group=1)

        Session=sessionmaker(bind=engine)
        s=Session()
        s.add(obj1)#

        users=[User(name='wangwu',password='123456',group=1),
               User(name='zhaoliu', password='123456', group=1),
               User(name='sunqi', password='123456', group=1)
               ]
        s.add_all(users)#

        s.commit()

 

 

  • 查找 数据

    • 同样适用sessionmaker来查找,与插入相同,需要创建session_class对象(我定义为s)
    • 使用s来查找:

      • s.query(表对应类)是相当于select对应表,后面可以跟first()、all()等来获取结果,也可以加filter、filter_by等来筛选结果
      • 获取全部 : s.query(表对应类).all() 【返回的是一个结果列表】
      • 查找指定: s.query(表对应类).filter(表对应类.xxx==xxxx)【filter获取的是结果集,需要使用all(),first()等方法来获取结果】
      • 查找指定: s.query(表对应类).filter_by(xxx=xxxx)
      • 附:虽然返回值是一个结果集,但这个集合是一个类对象,如果想查看内容,需要在表对应的类中增加__repr__方法。
      • 多个筛选条件使用“,”隔开
      • 常见可用筛选条件【User是一个表对应的类】:

        使用filter,filter_by时:
        User.name==’lisi’
        User.name.like(“lisi%”))
        User.name != ’lisi’
        User.name.any()
        or_(筛选条件) 【代表里面的多个筛选条件以or组合,需要导入:from sqlalchemy import or_】
        and_(筛选条件) 【代表里面的多个筛选条件以and组合,需要导入:from sqlalchemy import and_】【默认是and】
        in_([筛选条件])  【使用比如User.name.in_(['xiaxia', 'lilei', 'lover'])】
        使用all时,以下是放在query里面的:
        User.name  [这相当于不使用where的select name from 表]
         
         
      • 连接查询使用:s.query(表对应类).join(表对应类.xxx==xxxx)

      • 还有group_by,order_by等用法这里不做讲解[什么时候有空再补吧!]

        from sqlalchemy import create_engine#负责导入连接数据库的对象
        from sqlalchemy.ext.declarative import declarative_base#负责导入创建表的api
        from sqlalchemy import Column #负责导入列
        from sqlalchemy.types import *#负责导入列类型
        
        #数据库连接
        engine = create_engine("mysql pymysql://root:123456@localhost/python_test",encoding ='utf-8')
        Base = declarative_base()
        class User(Base):
            __tablename__ = 'user'#表名
            id = Column(Integer,primary_key=True)
            name = Column(String(32))
            password = Column(String(64))
            group = Column(Integer)
        
            def __repr__(self):
                return "<id:%s name:%s group:%s>"%(self.id,self.name,self.group)
        Base.metadata.create_all(engine)
        
            from sqlalchemy.orm import sessionmaker
            obj1=User(name='lisi',password='123456',group=1)

            Session=sessionmaker(bind=engine)
            s=Session()

            a=s.query(User).all()
            a2=s.query(User).filter(User.name=='lisi').first()
            a3=s.query(User).filter_by(name='lisi').first()



            print(a)
            print(a2)
            print(a3)

 

 

  • 修改 数据:

    • 修改数据的基础是先查找到数据,查找:row=s.query(X).filter(X.xxx=xxx).first()
    • 使用赋值语句修改 :row.xxx=xxxx

      from sqlalchemy import create_engine#负责导入连接数据库的对象
      from sqlalchemy.ext.declarative import declarative_base#负责导入创建表的api
      from sqlalchemy import Column #负责导入列
      from sqlalchemy.types import *#负责导入列类型
      
      #数据库连接
      engine = create_engine("mysql pymysql://root:123456@localhost/python_test",encoding ='utf-8')
      Base = declarative_base()
      class User(Base):
          __tablename__ = 'user'#表名
          id = Column(Integer,primary_key=True)
          name = Column(String(32))
          password = Column(String(64))
          group = Column(Integer)
      
          def __repr__(self):
              return "<id:%s name:%s group:%s>"%(self.id,self.name,self.group)
      Base.metadata.create_all(engine)
      
        from sqlalchemy.orm import sessionmaker
        obj1=User(name='lisi',password='123456',group=1)

        Session=sessionmaker(bind=engine)
        s=Session()


        row=s.query(User).filter(User.name=='lisi').first()
        row.name='lisi2'
        s.commit()

 

 

  • 删除 数据:

    • 删除数据的基础是先查找到数据,查找:row=s.query(X).filter(X.xxx=xxx)
    • 使用delete删除:row.delete()

      # coding: utf-8
      from sqlalchemy import create_engine#负责导入连接数据库的对象
      from sqlalchemy.ext.declarative import declarative_base#负责导入创建表的api
      from sqlalchemy import Column #负责导入列
      from sqlalchemy.types import *#负责导入列类型
      
      #数据库连接
      engine = create_engine("mysql pymysql://root:123456@localhost/python_test",encoding ='utf-8')
      Base = declarative_base()
      class User(Base):
          __tablename__ = 'user'#表名
          id = Column(Integer,primary_key=True)
          name = Column(String(32))
          password = Column(String(64))
          group = Column(Integer)
          def __repr__(self):
              return "<id:%s name:%s group:%s>"%(self.id,self.name,self.group)
      
      Base.metadata.create_all(engine)
      
        from sqlalchemy.orm import sessionmaker
        obj1=User(name='lisi',password='123456',group=1)

        Session=sessionmaker(bind=engine)
        s=Session()


        a3=s.query(User).filter_by(name='lisi1')
        a3.delete()
        s.commit()

 

  • 外键相关:

    • 外键使用foregin_key创建
    • 类中的relationship的作用:帮助ORM获知他们的外键关系,以便ORM使用外键获取相关数据
      • relationship中的backref的用途:relationship使得可以在一个表中定义的relationshop能被两个表使用,另一个表使用backref来获取相关信息
      • relationship中的foreign_keys的用途:当有多个relationship时,为了避免ORM混淆多个relationship,特别的标注哪个外键是哪个relationship
      • relationship中的secondary的用途:在多对多的关系中,填入的值是中间表,维持两边表关系。
    • 一对一的外键关系:

      • 1.导入模块:from sqlalchemy import Foreign_key
      • 2.建立外键(如:group = Column(Integer,ForeignKey("group.id")),建立关系(如:group_relation=relationship('Group',backref="g_users")
      • 3.插入数据
      • 4.查询到一条数据:如row=s.query(User).filter(User.name=='lisi').first()
      • 5.尝试A表调用关系来获取B(row.group_relation.group_name),B使用backref来获取A的数据(row2.g_users)
      • 下面的实例大概就是“一个开发人员对应一个开发组的关系”

      #负责导入连接数据库的对象 from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base#负责导入创建表的api from sqlalchemy import Column,ForeignKey #负责导入列 from sqlalchemy.types import *#负责导入列类型 from sqlalchemy.orm import relationship

      #数据库连接 engine = create_engine("mysql pymysql://root:123456@localhost/python_test",encoding ='utf-8') Base = declarative_base()

    class Group(Base):
        __tablename__="group"
        id=Column(Integer,primary_key=True)
        group_name=Column(String(32),nullable=False)

        def __repr__(self):
            return "<id:%s group_name:%s>"%(self.id,self.group_name)

    class User(Base):
        __tablename__ = 'user'#表名
        id = Column(Integer,primary_key=True)
        name = Column(String(32),nullable=False)
        password = Column(String(64),nullable=False)
        group = Column(Integer,ForeignKey("group.id"))#这里创建外键

        group_relation=relationship('Group',backref="g_users")#为ORM指明关系,方便ORM处理,第一个是对应的类
        def __repr__(self):
            return "<id:%s name:%s>"%(self.id,self.name)
    Base.metadata.create_all(engine)

    from sqlalchemy.orm import sessionmaker
    # group1=Group(group_name='python')
    # group2=Group(group_name='linux')
    # group3=Group(group_name='AI')
    # user1=User(name='lisi',password='123456',group=1)
    # user2=User(name='zhangsan',password='123456',group=2)
    # user3=User(name='wangwu',password='123456',group=3)
    # user4=User(name='lilei',password='123456',group=3)


    Session=sessionmaker(bind=engine)
    s=Session()
    # s.add_all([group1,group2,group3,user1,user2,user3,user4])
    # s.commit()

    # row=s.query(User).filter(User.name=='lisi').first()
    row=s.query(User).first()
    print(row.group_relation.group_name)#这里User通过关系来获取Group的数据
    row2=s.query(Group).first()
    print(row2)
    print(row2.g_users)#这里Group通过relationship的backref来获取User的数据

-   一对多关系,外键关联
    -   `以一个老师能做一个班的班主任此外还能做另一个班的副班主任为例【即一个老师能对应多个班级】`
    -   一对多关系的创建的核心是relationship中的foreign_keys
    -   `附:当你建表成功而插入数据失败时,可以尝试先删除掉数据表,有时候因为外键依赖会导致插入失败 

`

            #负责导入连接数据库的对象
            from sqlalchemy import create_engine
            from sqlalchemy.ext.declarative import declarative_base#负责导入创建表的api
            from sqlalchemy import Column,ForeignKey #负责导入列
            from sqlalchemy.types import *#负责导入列类型
            from sqlalchemy.orm import relationship

            #数据库连接
            engine = create_engine("mysql pymysql://root:123456@localhost/python_test",encoding ='utf-8')
            Base = declarative_base()



            class Grade(Base):
                __tablename__="grade"
                id=Column(Integer,primary_key=True)
                grade_name=Column(String(32),nullable=False)

                def __repr__(self):
                    return "<id:%s group_name:%s>"%(self.id,self.grade_name)

            class Teacher(Base):
                __tablename__ = 'teacher'#表名
                id = Column(Integer,primary_key=True)
                name = Column(String(32),nullable=False)
                primary_grade = Column(Integer,ForeignKey("grade.id"))
                second_grade = Column(Integer,ForeignKey("grade.id"))

                primary_grade_relation=relationship('Grade',backref="first_teacher",foreign_keys=[primary_grade])
                second_grade_relation=relationship('Grade',backref="second_teacher",foreign_keys=[second_grade])
                def __repr__(self):
                    return "<id:%s name:%s>"%(self.id,self.name)


            Base.metadata.create_all(engine)

            from sqlalchemy.orm import sessionmaker
            # grade1=Grade(grade_name='python')
            # grade2=Grade(grade_name='linux')
            # grade3=Grade(grade_name='AI')
            # grade4=Grade(grade_name='Java')
            # t1=Teacher(name='lisi',primary_grade=1,second_grade=2)
            # t2=Teacher(name='zhangsan',primary_grade=2,second_grade=1)
            # t3=Teacher(name='wangwu',primary_grade=4,second_grade=3)
            # t4=Teacher(name='lilei',primary_grade_relation=grade3,second_grade=4)
            #这里外键相关的比如primary_grade=x可以使用primary_grade_relation=对象来代替,
            # 会根据对象来转成对应id,不过问题是不知道grade3的准确id,因为可能创建顺序不一致

            Session=sessionmaker(bind=engine)
            s=Session()
            # s.add_all([grade1,grade2,grade3,grade4])
            # s.add_all([t1,t2,t3,t4])
            # s.commit()

            row=s.query(Teacher).filter(Teacher.name=='lisi').first()

            print(row.name,row.primary_grade_relation.grade_name)#这里Teacher通过关系来获取Grade的数据
            print(row.name,row.second_grade_relation.grade_name)
            row2=s.query(Grade).first()
            print(row2.grade_name,row2.first_teacher)#这里Grade通过relationship的backref来获取Teacher的数据
            print(row2.grade_name,row2.second_teacher)

`` 

-   多对多外键关联
    -   以选课中一门课能有多名学生,一个学生可以选多门课为示例:
    -   其中relationship中的secondary的值是中间表,负责维持中间表与另外两表的关系,创建多对多的核心是secondary
            #负责导入连接数据库的对象
            from sqlalchemy import create_engine
            from sqlalchemy.ext.declarative import declarative_base#负责导入创建表的api
            from sqlalchemy import Column,ForeignKey #负责导入列
            from sqlalchemy.types import *#负责导入列类型
            from sqlalchemy.orm import relationship

            #数据库连接
            engine = create_engine("mysql pymysql://root:123456@localhost/python_test",encoding ='utf-8')
            Base = declarative_base()

            class SelectInfo(Base):
                __tablename__="selectClassInfo"
                id=Column(Integer,primary_key=True)
                sid=Column(Integer,ForeignKey("student.id"))
                cid=Column(Integer,ForeignKey("course.id"))


            """使用declarative_base和Table 创建表时,secondary的填写不一样
            selectInfo2=Table(
            'selectClassInfo',Base.metadata,
            Column('sid',Integer,ForeignKey('student.id'))
            Column('cid',Integer,ForeignKey('student.id'))
            )
            """

            class Student(Base):
                __tablename__="student"
                id=Column(Integer,primary_key=True)
                name=Column(String(32),nullable=False)

                def __repr__(self):
                    return "<id:%s name:%s>"%(self.id,self.name)

            class Course(Base):
                __tablename__ = 'course'
                id = Column(Integer,primary_key=True)
                name = Column(String(32),nullable=False)

                student_relation=relationship('Student',secondary="selectClassInfo",backref="courses")
                # student_relation=relationship('Student',secondary=selectClassInfo2,backref="courses") 
                # #如果使用Table来创建中间表,上面是这样填的

                def __repr__(self):
                    return "<id:%s name:%s>"%(self.id,self.name)


            Base.metadata.create_all(engine)

            from sqlalchemy.orm import sessionmaker
            #
            # s1=Student(name='lisi')
            # s2=Student(name='zhangsan')
            # s3=Student(name='wangwu')
            # s4=Student(name='lilei')
            # c1=Course(name='python',student_relation=[s1,s2])
            # c2=Course(name='linux',student_relation=[s3])
            # c3=Course(name='AI',student_relation=[s3,s4])
            # c4=Course(name='Java')
            # c4.student_relation=[s1,s2,s3,s4]##在一边增加关系之后,在secondary中会加入两边的数据
            #
            #
            #
            Session=sessionmaker(bind=engine)
            s=Session()
            # s.add_all([s1,s2,s3,s4,c1,c2,c3,c4])
            # s.commit()

            row=s.query(Course).filter(Course.id=='4').first()

            print(row.name,row.student_relation)#这里Course通过关系来获取Student的数据
            row2=s.query(Student).filter(Student.id=="3").first()
            print(row2.name,row2.courses)#这里Student通过relationship的backref来获取Course的数据

 

 

 

 

补充说明:

1.engine 可以直接运行sql语句,方式是engine.execute(),返回值是结果集,可以使用fetchall等方法来获取结果

2.其实创建表还有很多方法,可以使用各种对象来创建【比如在上面Table方式中也可以使用t来create(engine)】,但建议使用方式一

3.同样的,不单创建表有各种方法,查看表,删除表等也有多种操作方式,也是因为可以使用多种对象来操作

4.session也可以直接运行sql语句: session.execute()

 

 

附上sessionmake API官方文档:

以及一个第三方辅助文档: 里面有不少关于sqlalchemy的用法例子

 


相关内容: 使用pymysql直接操作mysql 创建表 查看表 修改表 删除表 插入数据 查看数据...

统计和分组

  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.engine 可以直接运行sql语句,方式是engine.execute(),返回值是结果集,可以使用fetchall等方法来获取结果

2.其实创建表还有很多方法,可以使用各种对象来创建【比如在上面Table方式中也可以使用t来create(engine)】,但建议使用方式一

3.同样的,不单创建表有各种方法,查看表,删除表等也有多种操作方式,也是因为可以使用多种对象来操作

4.session也可以直接运行sql语句: session.execute()

 

 

附上sessionmake API官方文档:里面详尽而简单的讲解了用法

以及一个第三方辅助文档: 里面有不少关于sqlalchemy的用法例子

 


连表

  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_第十一篇,python与数据库之mysql。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_第十一篇,python与数据库之mysql。by(name="JAVA").first()

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

  1. print("通过作者表查询关联的书".center(50,"*"))

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

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

  1.  

  2. # 多对多删除,删除数据时不用管book_m2m_author,sqlalchemy会自动删除对应的数据

  1. # 通过书删除作者

  2. book_obj.authors.remove(author_obj)

  1.  

  2. # 直接删除作者

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

  4. session.delete(author_obj2)

  5. session.commit()

 

支持中文

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

  1. # 支持中文

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

支持中文

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

  1. # 支持中文

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

本文由澳门新萄京官方网站发布于www.8455.com,转载请注明出处:第十一篇,python与数据库之mysql

关键词: