本文共 25863 字,大约阅读时间需要 86 分钟。
• pypi即python package index
• 是python语言的软件仓库
• 官方站点为https://pypi.python.org
• 为了实现安装加速,可以配置pip安装时采用国内镜 像站点 [root@localhost ~]# mkdir ~/.pip/ [root@localhost ~]# vim ~/.pip/pip.conf[global]index-url=http://pypi.douban.com/simple/[install]trusted-host=pypi.douban.com
• 安装依赖包[root@localhost ~]# yum install -y gcc• 本地安装[root@localhost ~]# pip3 install PyMySQL-0.8.0.tar.gz• 离线安装[root@localhost ~]# cd /root/python_bao/zzg_pypkgs/pymysql_pkgs[root@localhost pymysql_pkgs]# pip3 install *• 在线安装[root@localhost packages]# pip3 install pymysql
[root@localhost ~]# yum install -y mariadb-server[root@localhost ~]# systemctl start mariadb[root@localhost ~]# mysqlMariaDB [(none)]> create database nqe default charset utf8;
为一个小型企业编写数据库,能够记录员工信息,记录发工资情况。
经过调查,需要这些字段:姓名、出生日期、联系方式、部门、工资日、基本工资、奖金、总工资。
关系型数据库,应该尽量减少数据冗余(重复的数据)
姓名 | 生日 | 联系方式 | 部门 | 工资日 | 基本工资 | 奖金 | 总工资 |
---|---|---|---|---|---|---|---|
张三 | 19950221 | 13242356 | 运维 | 20190510 | 10000 | 2000 | 12000 |
张三 | 19950221 | 13242356 | 运维部 | 20190610 | 10000 | 2000 | 12000 |
为了减少数据冗余,可以将字段存放到不同的表中:员工表、部门表、工资表。
员工表:
姓名 | 生日 | 联系方式 | 部门ID |
---|---|---|---|
张三 | 19950221 | 13242356 | 2 |
部门表:
部门ID | 部门名称 |
---|---|
2 | 运维 |
工资表:
姓名 | 工资日 | 基本工资 | 奖金 | 总工资 |
---|---|---|---|---|
张三 | 20190510 | 10000 | 2000 | 12000 |
虽然各张表已经分开了,但是字段并不符合关系型数据库的要求。
1. 所谓第一范式(1NF)是指在关系模型中,所有的域都应该是原子性的。联系方式不满足1NF,因为它包括家庭住址、电话号码、email等,所以要把联系方式拆分成更小的项目。2. 2NF在1NF的基础上,非码属性必须完全依赖于码。简单来说就是表需要一个主键。根据2NF,最好为员工表加上员工ID作为主键;工资表应该记录的是员工ID,而不是员工姓名,但是员工ID也不能成为主键,因为每个月都要发工资,用现有的任何字段作为主键都不合适,干脆强加一个主键。3. 第三范式(3NF)任何非主属性不得传递依赖于主属性,非主属性不能依赖其他非主属性。工资表中的总工资依赖于基本工资和奖金,它不应该出现在表中。最终确定了三张表:员工表:员工ID、姓名、email、部门ID部门表:部门ID、部门名称工资表:工资日、员工ID、基本工资、奖金
[root@localhost ~]# yum install -y mariadb-server[root@localhost ~]# systemctl start mariadb[root@localhost ~]# mysqlMariaDB [(none)]> create database nqe default charset utf8;
# 练习[root@localhost ~]# vim py_mysql.py#! /usr/local/python3import pymysqlconn = pymysql.connect( host = '127.0.0.1', port = 3306, user = 'root', passwd = '123', db = 'nqe', charset = 'utf8')cursor = conn.cursor()[root@localhost ~]# python3 py_mysql.py[root@localhost ~]# vim py_mysql.py#! /usr/local/python3import pymysqlconn = pymysql.connect( host = '127.0.0.1', port = 3306, user = 'root', passwd = '123', db = 'nqe', charset = 'utf8')cursor = conn.cursor()create_dep = '''CREATE TABLE departments(dep_id INT, dep_name VARCHAR(50),PRIMARY KEY(dep_id))'''create_emp = '''CREATE TABLE employees(emp_id INT, emp_name VARCHAR(50), email VARCHAR(50), dep_id INT,PRIMARY KEY(emp_id), FOREIGN KEY(dep_id) REFERENCES departments(dep_id))'''create_sal = '''CREATE TABLE salary(id INT, date DATE, emp_id INT, basic INT, awards INT,PRIMARY KEY(id), FOREIGN KEY(emp_id) REFERENCES employees(emp_id))'''cursor.execute(create_dep)cursor.execute(create_emp)cursor.execute(create_sal)conn.commit() # 提交改动cursor.close() # 关闭游标conn.close() # 关闭连接[root@localhost ~]# python3 py_mysql.py
[root@localhost ~]# mysql -u root -pEnter password: Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 15Server version: 5.5.64-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || nqe || performance_schema || test |+--------------------+7 rows in set (0.00 sec)MariaDB [(none)]> use nqe;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [nqe]> show tables;+---------------+| Tables_in_nqe |+---------------+| departments || employees || salary |+---------------+3 rows in set (0.00 sec)# > drop tables 删除表
import pymysqlconn = pymysql.connect( host='127.0.0.1', port=3306, user='root', passwd='123', db='nqe', charset='utf8')cursor = conn.cursor() # 游标...# 插入语句insert_dep = 'INSERT INTO departments VALUES(%s, %s)'cursor.executemany(insert_dep, [(1, '人事部')])deps = [(2, '财务部'), (3, '运维部'), (4, '开发部'), (5, '测试部'), (6, '市场部')]cursor.executemany(insert_dep, deps)#############################################conn.commit() # 提交改动cursor.close() # 关闭游标conn.close() # 关闭连接
MariaDB [nqe]> select * from departments;+--------+-----------+| dep_id | dep_name |+--------+-----------+| 1 | 人事部 || 2 | 财务部 || 3 | 运维部 || 4 | 开发部 || 5 | 测试部 || 6 | 市场部 |+--------+-----------+6 rows in set (0.00 sec)# > delete from departments;
import pymysqlconn = pymysql.connect( host='127.0.0.1', port=3306, user='root', passwd='123', db='nqe', charset='utf8')cursor = conn.cursor() # 游标...# 基础查询select1 = 'SELECT * FROM departments'cursor.execute(select1)print(cursor.fetchone())print('*' * 20)print(cursor.fetchmany(2))print('*' * 20)print(cursor.fetchall())#############################################conn.commit() # 提交改动cursor.close() # 关闭游标conn.close() # 关闭连接
[root@localhost ~]# python3 py_mysql.py (1, '人事部')********************((2, '财务部'), (3, '运维部'))********************((4, '开发部'), (5, '测试部'), (6, '市场部'))
import pymysqlconn = pymysql.connect( host='127.0.0.1', port=3306, user='root', passwd='123', db='nqe', charset='utf8')cursor = conn.cursor() # 游标...# 基础查询select1 = 'SELECT * FROM departments'cursor.execute(select1)print(cursor.fetchone())print('*' * 20)print(cursor.fetchmany(2))print('*' * 20)print(cursor.fetchall())#############################################conn.commit() # 提交改动cursor.close() # 关闭游标conn.close() # 关闭连接
MariaDB [nqe]> select * from departments order by dep_id;
[root@localhost ~]# python3 py_mysql.py (3, '运维部')********************(1, '人事部')
import pymysqlconn = pymysql.connect( host='127.0.0.1', port=3306, user='root', passwd='123', db='nqe', charset='utf8')cursor = conn.cursor() # 游标...# 修改update1 = 'UPDATE departments set dep_name=%s WHERE dep_name=%s'cursor.execute(update1, ('人力资源部', '人事部'))#############################################conn.commit() # 提交改动cursor.close() # 关闭游标conn.close() # 关闭连接
[root@localhost ~]# python3 py_mysql.py
MariaDB [nqe]> select * from departments order by dep_id;+--------+-----------------+| dep_id | dep_name |+--------+-----------------+| 1 | 人力资源部 || 2 | 财务部 || 3 | 运维部 || 4 | 开发部 || 5 | 测试部 || 6 | 市场部 |+--------+-----------------+6 rows in set (0.00 sec)
import pymysqlconn = pymysql.connect( host='127.0.0.1', port=3306, user='root', passwd='123', db='nqe', charset='utf8')cursor = conn.cursor() # 游标...# 删除delete1 = 'DELETE FROM departments WHERE dep_name=%s'cursor.execute(delete1, ('市场部',))conn.commit() # 提交改动cursor.close() # 关闭游标conn.close() # 关闭连接
[root@localhost ~]# python3 py_mysql.py
MariaDB [nqe]> select * from departments order by dep_id;+--------+-----------------+| dep_id | dep_name |+--------+-----------------+| 1 | 人力资源部 || 2 | 财务部 || 3 | 运维部 || 4 | 开发部 || 5 | 测试部 |+--------+-----------------+5 rows in set (0.00 sec)
• 创建连接是访问数据库的第一步conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', passwd='tedu.cn', db=nsd_cloud', charset='utf8')
• 游标(cursor)就是游动的标识 • 通俗的说,一条sql取出对应n条结果资源的接口/句柄,就 是游标,沿着游标可以一次取出一行cursor = conn.cursor()
• 对数据库表做修改操作,必须要commitsql1 = "insert into departments(dep_name) values(%s)"result = cur.execute(sql1, ('development',))sql2 = "insert into departments(dep_name) values(%s)"data = [('hr',), ('op',)]result = cur.executemany(sql2, data)sql3 = "insert into departments(dep_name) values(%s)"data = [('行政',), ('财务',), ('运营',)]result = cur.executemany(sql3, data)conn.commit()
• 可以取出表中一条、多条或全部记录sql4 = "select * from departments"cur.execute(sql4)result = cur.fetchone()print(result)result2 = cur.fetchmany(2)print(result2)result3 = cur.fetchall()print(result3)
• 如果希望不是从头取数据,可以先移动游标cur.scroll(1, mode="ralative")cur.scroll(2, mode="absolute")sql5 = "select * from departments"cur.execute(sql5)cur.scroll(3, mode='absolute')result4 = cur.fetchmany(2)sprint(result4)
• 通过update修改某一字段的值sql6 = "update departments set dep_name=%s where dep_name=%s"result = cur.execute(sql6, ('operations', 'op'))print(result)conn.commit()
• 通过delete删除记录sql7 = "delete from departments where dep_id=%s"result = cur.execute(sql7, (6,))print(result)conn.commit()
1. 通过pymysql模块创建数据库的表2. 向employees表插入数据3. 向salary表插入数据4. 插入的数据需要commit到数据库中
• SQLAlchemy由官方收录,可以直接安装 [root@localhost ~]# cd python_bao/zzg_pypkgs/sqlalchemy_pkgs/[root@localhost sqlalchemy_pkgs]# pip3 install *[root@localhost sqlalchemy_pkgs]# pip3 install SQLAlchemy-1.2.14.tar.gz
• SQLAlchemy是Python编程语下的一款开源软件。提供 SQL 具包及对象关系映射(ORM) 工具,使用MIT许可证发
• SQLAlchemy“采用简单的Python语言,为高效和高性能的数据库访问设计,实现了完整的企业级持久模型”
• SQLAlchemy的理念是,SQL数据库的量级和性能重要于对象集合;而对象集合的抽象又重要于表和行
• 目标是提供能兼容众多数据库(如 SQLite、MySQL、Postgresql、Oracle、MS-SQL、SQLServer 和 Firebird)的企业级持久性模型
SQL Alchemy ORM Object Relational Mapper (ORM)SQL Alchemy CoreSchema / Types SQL Expression Language Engine Connection Pooling Dialect DBAPI
• ORM即对象关系映射 • 数据库表是一个二维表,包含多行多列。把一个表的内容用Python的数据结构表示出来的话,可以用一个list表示多行,list的每一个元素是tuple,表示一行记录[ ('1', 'Michael'), ('2', 'Bob'), ('3', 'Adam')] - 对象:指OOP编程的方式 - 关系:关系型数据库 - 将python中的class映射到数据库的表 - class中的类变量映射到数据库表中的每个字段 - class的每个实例映射到数据库表中的每行记录
• 用tuple表示一行很难看出表的结构。如果把一个tuple用class实例来表示,就可以更容易地看出表的结构来class User(object):def __init__(self, id, name):self.id = idself.name = name[User('1', 'Michael'),User('2', 'Bob'),User('3', 'Adam')]
• 通过create_engine实现数据库的连接 [root@bogon bin]# mysql -uroot -ptedu.cnMariaDB [(none)]> create database tarena default char set utf8;>>> from sqlalchemy import create_engine>>> engine = create_engine( 'mysql+pymysql://root:tedu.cn@localhost/tarena?charset=utf8', encoding='utf8',echo=True)//echo=True表示将日志输出到终端屏幕,默认为False
• 当使用ORM的时候,配置过程从描述数据库表开始 • 通过自定义类映射相应的表 • 通过声明系统实现类映射 • 首先通过声明系统,定义基类>>> from sqlalchemy.ext.declarative import declarative_base>>> Base = declarative_base()
• 一旦创建了基类,就可以创建自定义映射类了>>> from sqlalchemy import Column, Integer, String>>> class Departments(Base):... __tablename__ = 'departments'... dep_id = Column(Integer, primary_key=True)... dep_name = Column(String(20))... def __repr__(self):... return "" % self.dep_name//__repr__是可选项
• 类构建完成后,表的信息将被写入到表的元数据 (metadata)>>> Departments.__table__Table('departments', MetaData(bind=None), Column('dep_id', Integer(), table=, primary_key=True, nullable=False), olumn('dep_name', String(), table= ), schema=None)
• 通过表的映射类,在数据库中创建表>>> Base.metadata.create_all(engine)
• 创建实例时,并不会真正在表中添加记录dep_dev = Departments(dep_name='developments')print(dep_dev.dep_name)print(str(dep_dev.dep_id))
• ORM访问数据库的句柄被称作Session>>> from sqlalchemy.orm import sessionmaker>>> Session = sessionmaker(bind=engine)如果在创建session前还未创建engine,操作如下>>> Session = sessionmaker()>>> Session.configure(bind=engine) //创建engine后执行
• 会话类的实例对象用于绑定到数据库 • 实例化类的对象,并不打开任何连接 • 当实例初次使用,它将从Engine维护的连接池中获 得一个连接 • 当所有的事务均被commit或会话对象被关闭时,连接结束>>> session = Session()>>> session.add(dep_dev)>>> session.commit()>>> print(str(dep_dev.dep_id))>>> session.close()
• 可以创建多个实例,批量添加记录dep_hr = Departments(dep_name='hr')dep_op = Departments(dep_name='operations')dep_finance = Departments(dep_name='财务')dep_xz = Departments(dep_name='行政’)Session = sessionmaker(engine)session = Session()session.add_all([dep_hr, dep_op, dep_finance, dep_xz])session.commit()session.close()
• ORM映射关系也可用于表间创建外键约束class Employees(Base): __tablename__ = 'employees' emp_id = Column(Integer, primary_key=True) name = Column(String(20)) genda = Column(String(10)) phone = Column(String(11)) dep_id = Column(Integer, ForeignKey('departments.dep_id')) def __repr__(self): return "" % self.name
MariaDB [(none)]> create database mqe default charset utf8;Query OK, 1 row affected (0.00 sec)MariaDB [(none)]> use mqe;Database changed
from sqlalchemy import create_engine, Column, Integer, String, ForeignKeyfrom sqlalchemy.ext.declarative import declarative_baseengine = create_engine( # mysql+pymymysql://用户名:密码@服务器/数据库?参数 'mysql+pymysql://root:123@127.0.0.1/mqe?charset=utf8', encoding='utf8', # echo=True # 在屏幕上输出日志,生产环境中不要使用)# 创建ORM的基类Base = declarative_base()class Department(Base): __tablename__ = 'departments' # 定义库中的表名 dep_id = Column(Integer, primary_key=True) dep_name = Column(String(50), unique=True, nullable=False)if __name__ == '__main__': # 如果库中没有相关的表则创建,有的话不会创建 Base.metadata.create_all(engine)
[root@localhost ~]# python3 dbconn.py
MariaDB [(none)]> use mqe;Database changedMariaDB [mqe]> show tables;Empty set (0.01 sec)MariaDB [mqe]> show tables;+---------------+| Tables_in_mqe |+---------------+| departments |+---------------+1 row in set (0.00 sec)# > drop tables 删除表
from sqlalchemy import create_engine, Column, Integer, String, ForeignKeyfrom sqlalchemy.ext.declarative import declarative_baseengine = create_engine( # mysql+pymymysql://用户名:密码@服务器/数据库?参数 'mysql+pymysql://root:tedu.cn@127.0.0.1/mqe?charset=utf8', encoding='utf8', # echo=True # 在屏幕上输出日志,生产环境中不要使用)# 创建ORM的基类Base = declarative_base()class Department(Base):...class Employee(Base): __tablename__ = 'employees' emp_id = Column(Integer, primary_key=True) emp_name = Column(String(50), nullable=False) email = Column(String(50), unique=True, nullable=False) dep_id = Column(Integer, ForeignKey('departments.dep_id')) if __name__ == '__main__': # 如果库中没有相关的表则创建,有的话不会创建 Base.metadata.create_all(engine)
[root@localhost ~]# python3 dbconn.py
MariaDB [mqe]> show tables;+---------------+| Tables_in_mqe |+---------------+| departments || employees |+---------------+2 rows in set (0.00 sec)
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, Datefrom sqlalchemy.ext.declarative import declarative_baseengine = create_engine( # mysql+pymymysql://用户名:密码@服务器/数据库?参数 'mysql+pymysql://root:tedu.cn@127.0.0.1/tedu1812?charset=utf8', encoding='utf8', # echo=True # 在屏幕上输出日志,生产环境中不要使用)# 创建ORM的基类Base = declarative_base()class Department(Base):...class Employee(Base):...class Salary(Base): __tablename__ = 'salary' id = Column(Integer, primary_key=True) date = Column(Date, nullable=False) emp_id = Column(Integer, ForeignKey('employees.emp_id')) basic = Column(Integer) awards = Column(Integer)if __name__ == '__main__': # 如果库中没有相关的表则创建,有的话不会创建 Base.metadata.create_all(engine)
[root@localhost ~]# python3 dbconn.py
MariaDB [mqe]> show tables;+---------------+| Tables_in_mqe |+---------------+| departments || employees || salary |+---------------+3 rows in set (0.00 sec)
....from sqlalchemy.orm import sessionmaker....# 创建ORM的基类Base = declarative_base()Session = sessionmaker(bind=engine)....
1. 创建employees表2. 创建部门表3. 创建salary表4. 表间创建恰当的关系
1. 分别在部门表、员工表和工资表中加入数据2. 通过SQLAlchemy代码实现3. 分别练习每次加入一行数据和每次可加入多行数据
• 通过作用于session的query()函数创建查询对象 • query()函数可以接收多种参数 from myorm import Session, Departments session = Session() for instance in session.query(Departments).order_by(Departments.dep_id): print(instance.dep_id, instance.dep_name)
• 使用ORM描述符进行查询 • 返回值是元组 from myorm import Employees, Session session = Session() for name, phone in session.query(Employees.name,Employees.phone): print(name, phone)
• 通这order_by()函数可以实现按指定字段排序 from myorm import Session, Departments session = Session() for instance in session.query(Departments).order_by(Departments.dep_id): print(instance.dep_id,instance.dep_name)
• 通过“切片”的方式,实现部分数据的提取 from myorm import Session, Departments session = Session() for row in session.query(Departments, Departments.dep_name)[2:5]: print(row.Departments, row.dep_name)
• 通过filter()函数实现结果过滤 from myorm import Session, Departments session = Session() for row in session.query(Departments.dep_name).filter(Departments.dep_id==2): print(row.dep_name)
• filter()函数可以叠加使用 from myorm import Session, Salary session = Session() for row in session.query(Salary.emp_id, Salary.base, Salary.award)\ .filter(Salary.award>2000).filter(Salary.base>10000): print(row.emp_id)
• 相等 query.filter(Employees.name=='john')• 不相等 query.filter(Employees.name!='john')• 模糊查询 query.filter(Employees.name.like('%j'))
• in query.filter(new_emp.name.in_(['bob', 'john'])• not in query.filter(~new_emp.name.in_(['bob', 'john']) • 字段为空 query.filter(new_emp.name.is_(None)) • 字段不为空 query.filter(new_emp.name.isnot(None))
• all()返回列表 • first()返回结果中的第一条记录
• 通过join()方法实现多表查询q = session.query(Employees.name, Departments.dep_name).join(Departments)print(q.all())
• 通过会话的update()方法更新from myorm import Session, Departmentssession = Session()q1 = session.query(Departments).filter(Departments.dep_id==6)q1.update({ Departments.dep_name: '运维部'})session.commit()session.close()
• 通过会话的字段赋值更新from myorm import Session, Departmentssession = Session()q2 = session.query(Departments).get(1) # get(1)查询主键是1的记录q2.dep_name = '开发部'session.commit()session.close()
• 通过会话的delete()方法进行记录删除from myorm import Session, Departmentssession = Session()q1 = session.query(Departments).get(7)session.delete(q1)session.commit()session.close()
from dbconn import Session, Department, Employee, Salarysession = Session()########################hr = Department(dep_id=1, dep_name='人事部')finance = Department(dep_id=2, dep_name='财务部')ops = Department(dep_id=3, dep_name='运维部')dev = Department(dep_id=4, dep_name='开发部')qa = Department(dep_id=5, dep_name='测试部')session.add_all([hr, finance, ops, dev, qa])session.commit()session.close()
[root@localhost ~]# python3 crud.py
MariaDB [mqe]> select * from departments;+--------+-----------+| dep_id | dep_name |+--------+-----------+| 1 | 人事部 || 4 | 开发部 || 5 | 测试部 || 2 | 财务部 || 3 | 运维部 |+--------+-----------+5 rows in set (0.00 sec)
from dbconn import Session, Department, Employee, Salarysession = Session()...#########################wt = Employee( emp_id=1, emp_name='王涛', email='wangtao@qq.com', dep_id=3)zj = Employee( emp_id=2, emp_name='张钧', email='zhangjun@163.com', dep_id=3)sy = Employee( emp_id=3, emp_name='苏艳', email='suyan@qq.com', dep_id=1)wjy = Employee( emp_id=4, emp_name='吴计印', email='wujiying@126.com', dep_id=4)kzw = Employee( emp_id=5, emp_name='康志文', email='kangzhiwen@qq.com', dep_id=4)hzq = Employee( emp_id=6, emp_name='胡志强', email='huzhiqiang@163.com', dep_id=5)lh = Employee( emp_id=7, emp_name='李浩', email='lihao@126.com', dep_id=2)session.add_all([wt, zj, sy, wjy, kzw, hzq, lh])session.commit()session.close()
[root@localhost ~]# python3 crud.py
MariaDB [mqe]> select * from employees;+--------+-----------+--------------------+--------+| emp_id | emp_name | email | dep_id |+--------+-----------+--------------------+--------+| 1 | 王涛 | wangtao@qq.com | 3 || 2 | 张钧 | zhangjun@163.com | 3 || 3 | 苏艳 | suyan@qq.com | 1 || 4 | 吴计印 | wujiying@126.com | 4 || 5 | 康志文 | kangzhiwen@qq.com | 4 || 6 | 胡志强 | huzhiqiang@163.com | 5 || 7 | 李浩 | lihao@126.com | 2 |+--------+-----------+--------------------+--------+7 rows in set (0.00 sec)
from dbconn import Session, Department, Employee, Salarysession = Session()...#######################qset1 = session.query(Department)print(qset1) # qset1只是个sql语句,当取具体值的时候,才真正查数据库# qset1.all()取出全部的部门,因为查询的是类名,所以返回所有的实例组成的列表print('*' * 30)print(qset1.all())print('*' * 30)for dep in qset1: # 遍历实例列表中的每个实例 print('%s: %s' % (dep.dep_id, dep.dep_name))session.commit()session.close()
[root@localhost ~]# python3 crud.py SELECT departments.dep_id AS departments_dep_id, departments.dep_name AS departments_dep_name FROM departments******************************[, , , , ]******************************1: 人事部4: 开发部5: 测试部2: 财务部3: 运维部
from dbconn import Session, Department, Employee, Salarysession = Session()...#######################qset3 = session.query(Employee.emp_name, Employee.email)# 查询的参数是字段,返回的结果是元组for item in qset3: print(item)print('*' * 30)for name, email in qset3: print('%s: %s' % (name, email))session.commit()session.close()
[root@localhost ~]# python3 crud.py ('王涛', 'wangtao@qq.com')('张钧', 'zhangjun@163.com')('苏艳', 'suyan@qq.com')('吴计印', 'wujiying@126.com')('康志文', 'kangzhiwen@qq.com')('胡志强', 'huzhiqiang@163.com')('李浩', 'lihao@126.com')******************************王涛: wangtao@qq.com张钧: zhangjun@163.com苏艳: suyan@qq.com吴计印: wujiying@126.com康志文: kangzhiwen@qq.com胡志强: huzhiqiang@163.com李浩: lihao@126.com
from dbconn import Session, Department, Employee, Salarysession = Session()...#########################qset4 = session.query(Department).order_by(Department.dep_id)[1:4]for dep in qset4: # 遍历实例列表中的每个实例 print('%s: %s' % (dep.dep_id, dep.dep_name))session.commit()session.close()
[root@localhost ~]# python3 crud.py 2: 财务部3: 运维部4: 开发部
from dbconn import Session, Department, Employee, Salarysession = Session()...#######################qset5 = session.query(Department).filter(Department.dep_id==2)print(qset5)print(qset5.all()) # all()返回列表dep = qset5.one() # 返回一个实例,如果返回值不是一个,将报错print(dep.dep_id, dep.dep_name)session.commit()session.close()
[root@localhost ~]# python3 crud.py SELECT departments.dep_id AS departments_dep_id, departments.dep_name AS departments_dep_name FROM departments WHERE departments.dep_id = %(dep_id_1)s[]2 财务部
from dbconn import Session, Department, Employee, Salarysession = Session()...#######################qset6 = session.query(Department).filter(Department.dep_id>1).filter(Department.dep_id<4)for dep in qset6: print(dep.dep_id, dep.dep_name, sep=', ')session.commit()session.close()
[root@localhost ~]# python3 crud.py 2, 财务部3, 运维部
from dbconn import Session, Department, Employee, Salarysession = Session()...#######################qset7 = session.query(Employee).filter(Employee.email.like('%@qq.com'))for emp in qset7: print(emp.emp_name, emp.email)session.commit()session.close()
[root@localhost ~]# python3 crud.py 王涛 wangtao@qq.com苏艳 suyan@qq.com康志文 kangzhiwen@qq.com
from dbconn import Session, Department, Employee, Salarysession = Session()...#######################qset8 = session.query(Department).filter(Department.dep_id.in_([3, 4]))for dep in qset8: print(dep.dep_id, dep.dep_name)session.commit()session.close()
[root@localhost ~]# python3 crud.py 4 开发部3 运维部
from dbconn import Session, Department, Employee, Salarysession = Session()...#######################qset9 = session.query(Department).filter(Department.dep_name.isnot(None))for dep in qset9: print(dep.dep_id, dep.dep_name)session.commit()session.close()
[root@localhost ~]# python3 crud.py 1 人事部4 开发部5 测试部2 财务部3 运维部
from dbconn import Session, Department, Employee, Salarysession = Session()...######################## query中先写的是Employee,join中要写Departmentqset10 = session.query(Employee.emp_name, Department.dep_name).join(Department)for row in qset10: print(row) session.commit()session.close()
[root@localhost ~]# python3 crud.py ('王涛', '运维部')('张钧', '运维部')('苏艳', '人事部')('吴计印', '开发部')('康志文', '开发部')('胡志强', '测试部')('李浩', '财务部')
from dbconn import Session, Department, Employee, Salarysession = Session()...#######################qset11 = session.query(Department.dep_name, Employee.emp_name).join(Employee)for row in qset11: print(row)session.commit()session.close()
[root@localhost ~]# python3 crud.py ('运维部', '王涛')('运维部', '张钧')('人事部', '苏艳')('开发部', '吴计印')('开发部', '康志文')('测试部', '胡志强')('财务部', '李浩')
from dbconn import Session, Department, Employee, Salarysession = Session()...######################## 修改数据,先找到实例,再给实例的属性重新赋值qset12 = session.query(Department).filter(Department.dep_name=='人事部')hr = qset12.one()hr.dep_name='人力资源部'session.commit()session.close()
[root@localhost ~]# python3 crud.py
MariaDB [mqe]> select * from departments;+--------+-----------------+| dep_id | dep_name |+--------+-----------------+| 1 | 人力资源部 || 4 | 开发部 || 5 | 测试部 || 2 | 财务部 || 3 | 运维部 |+--------+-----------------+5 rows in set (0.00 sec)
from dbconn import Session, Department, Employee, Salarysession = Session()...######################## 删除,只要找到实例,然后删除即可qset13 = session.query(Employee).filter(Employee.emp_id==6)emp = qset13.one()session.delete(emp)session.commit()session.close()
MariaDB [mqe]> select * from employees;+--------+-----------+-------------------+--------+| emp_id | emp_name | email | dep_id |+--------+-----------+-------------------+--------+| 1 | 王涛 | wangtao@qq.com | 3 || 2 | 张钧 | zhangjun@163.com | 3 || 3 | 苏艳 | suyan@qq.com | 1 || 4 | 吴计印 | wujiying@126.com | 4 || 5 | 康志文 | kangzhiwen@qq.com | 4 || 7 | 李浩 | lihao@126.com | 2 |+--------+-----------+-------------------+--------+6 rows in set (0.00 sec)
1. 修改部门表,将人事部改为人力资源部2. 如果存在设计部,将设计部删除3. 查询所有每个员工及其所在部门
转载地址:http://qmnwi.baihongyu.com/