PyMySQL模塊
PyMySQL安裝
使用pypi
? pypi即python package index
? 是python語言的軟件倉庫
? 官方站點為https://pypi.python.org
使用國內(nèi)鏡像站點
? 為了實現(xiàn)安裝加速，可以配置pip安裝時采用國內(nèi)鏡 像站點
[root@localhost ~]# mkdir ~/.pip/
[root@localhost ~]# vim ~/.pip/pip.conf
[global]
index-url=http://pypi.douban.com/simple/
[install]
trusted-host=pypi.douban.com
通過pip安裝PyMySQL模塊
? 安裝依賴包
[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
配置mysql或mariadb
- 安裝
- 啟動
- 修改密碼
- 創(chuàng)建數(shù)據(jù)庫
[root@localhost ~]# yum install -y mariadb-server
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# mysql
MariaDB [(none)]> create database nqe default charset utf8;
PyMySQL應用
數(shù)據(jù)庫
為一個小型企業(yè)編寫數(shù)據(jù)庫，能夠記錄員工信息，記錄發(fā)工資情況。
經(jīng)過調(diào)查，需要這些字段：姓名、出生日期、聯(lián)系方式、部門、工資日、基本工資、獎金、總工資。
關系型數(shù)據(jù)庫，應該盡量減少數(shù)據(jù)冗余（重復的數(shù)據(jù)）
| 姓名 | 生日 | 聯(lián)系方式 | 部門 | 工資日 | 基本工資 | 獎金 | 總工資 |
|---|---|---|---|---|---|---|---|
| 張三 | 19950221 | 13242356 | 運維 | 20190510 | 10000 | 2000 | 12000 |
| 張三 | 19950221 | 13242356 | 運維部 | 20190610 | 10000 | 2000 | 12000 |
為了減少數(shù)據(jù)冗余，可以將字段存放到不同的表中：員工表、部門表、工資表。
員工表：
| 姓名 | 生日 | 聯(lián)系方式 | 部門ID |
|---|---|---|---|
| 張三 | 19950221 | 13242356 | 2 |
部門表：
| 部門ID | 部門名稱 |
|---|---|
| 2 | 運維 |
工資表：
| 姓名 | 工資日 | 基本工資 | 獎金 | 總工資 |
|---|---|---|---|---|
| 張三 | 20190510 | 10000 | 2000 | 12000 |
雖然各張表已經(jīng)分開了，但是字段并不符合關系型數(shù)據(jù)庫的要求。
關系型數(shù)據(jù)庫字段需要滿足數(shù)據(jù)庫范式：
1. 所謂第一范式（1NF）是指在關系模型中，所有的域都應該是原子性的。聯(lián)系方式不滿足1NF，因為它包括家庭住址、電話號碼、email等，所以要把聯(lián)系方式拆分成更小的項目。
2. 2NF在1NF的基礎上，非碼屬性必須完全依賴于碼。簡單來說就是表需要一個主鍵。根據(jù)2NF，最好為員工表加上員工ID作為主鍵；工資表應該記錄的是員工ID，而不是員工姓名，但是員工ID也不能成為主鍵，因為每個月都要發(fā)工資，用現(xiàn)有的任何字段作為主鍵都不合適，干脆強加一個主鍵。
3. 第三范式（3NF）任何非主屬性不得傳遞依賴于主屬性，非主屬性不能依賴其他非主屬性。工資表中的總工資依賴于基本工資和獎金，它不應該出現(xiàn)在表中。
最終確定了三張表：
員工表：員工ID、姓名、email、部門ID
部門表：部門ID、部門名稱
工資表：工資日、員工ID、基本工資、獎金
配置mysql或mariadb
- 安裝
- 啟動
- 修改密碼
- 創(chuàng)建數(shù)據(jù)庫
[root@localhost ~]# yum install -y mariadb-server
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# mysql
MariaDB [(none)]> create database nqe default charset utf8;
# 練習
[root@localhost ~]# vim py_mysql.py
#! /usr/local/python3
import pymysql
conn = 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/python3
import pymysql
conn = 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 -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 5.5.64-MariaDB MariaDB Server
Copyright (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 names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [nqe]> show tables;
+---------------+
| Tables_in_nqe |
+---------------+
| departments |
| employees |
| salary |
+---------------+
3 rows in set (0.00 sec)
# > drop tables 刪除表
import pymysql
conn = 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, '開發(fā)部'), (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 | 開發(fā)部 |
| 5 | 測試部 |
| 6 | 市場部 |
+--------+-----------+
6 rows in set (0.00 sec)
# > delete from departments;
import pymysql
conn = 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, '開發(fā)部'), (5, '測試部'), (6, '市場部'))
import pymysql
conn = 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 pymysql
conn = 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 | 開發(fā)部 |
| 5 | 測試部 |
| 6 | 市場部 |
+--------+-----------------+
6 rows in set (0.00 sec)
import pymysql
conn = 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 | 開發(fā)部 |
| 5 | 測試部 |
+--------+-----------------+
5 rows in set (0.00 sec)
連接數(shù)據(jù)庫
? 創(chuàng)建連接是訪問數(shù)據(jù)庫的第一步
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()
插入數(shù)據(jù)
? 對數(shù)據(jù)庫表做修改操作，必須要commit
sql1 = "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()
查詢數(shù)據(jù)
? 可以取出表中一條、多條或全部記錄
sql4 = "select * from departments"
cur.execute(sql4)
result = cur.fetchone()
print(result)
result2 = cur.fetchmany(2)
print(result2)
result3 = cur.fetchall()
print(result3)
移動游標
? 如果希望不是從頭取數(shù)據(jù)，可以先移動游標
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)s
print(result4)
修改數(shù)據(jù)
? 通過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()
向表中添加數(shù)據(jù)
1. 通過pymysql模塊創(chuàng)建數(shù)據(jù)庫的表
2. 向employees表插入數(shù)據(jù)
3. 向salary表插入數(shù)據(jù)
4. 插入的數(shù)據(jù)需要commit到數(shù)據(jù)庫中
SQLAlchemy基礎
SQLAlchemy概述
安裝
? 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許可證發(fā)
? SQLAlchemy“采用簡單的Python語言，為高效和高性能的數(shù)據(jù)庫訪問設計，實現(xiàn)了完整的企業(yè)級持久模型”
? SQLAlchemy的理念是，SQL數(shù)據(jù)庫的量級和性能重要于對象集合；而對象集合的抽象又重要于表和行
? 目標是提供能兼容眾多數(shù)據(jù)庫（如 SQLite、MySQL、Postgresql、Oracle、MS-SQL、SQLServer 和 Firebird）的企業(yè)級持久性模型
架構
SQL Alchemy ORM
Object Relational Mapper (ORM)
SQL Alchemy Core
Schema / Types SQL Expression Language Engine
Connection Pooling Dialect
DBAPI
ORM模型
? ORM即對象關系映射
? 數(shù)據(jù)庫表是一個二維表，包含多行多列。把一個表的內(nèi)容用Python的數(shù)據(jù)結構表示出來的話，可以用一個list表示多行，list的每一個元素是tuple，表示一行記錄
[
('1', 'Michael'),
('2', 'Bob'),
('3', 'Adam')
]
- 對象：指OOP編程的方式
- 關系：關系型數(shù)據(jù)庫
- 將python中的class映射到數(shù)據(jù)庫的表
- class中的類變量映射到數(shù)據(jù)庫表中的每個字段
- class的每個實例映射到數(shù)據(jù)庫表中的每行記錄
ORM模型（續(xù)1）
? 用tuple表示一行很難看出表的結構。如果把一個tuple用class實例來表示，就可以更容易地看出表的結構來
class User(object):
def __init__(self, id, name):
self.id = id
self.name = name
[
User('1', 'Michael'),
User('2', 'Bob'),
User('3', 'Adam')
]
數(shù)據(jù)庫對象管理
連接mysql
? 通過create_engine實現(xiàn)數(shù)據(jù)庫的連接
[root@bogon bin]# mysql -uroot -ptedu.cn
MariaDB [(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的時候，配置過程從描述數(shù)據(jù)庫表開始
? 通過自定義類映射相應的表
? 通過聲明系統(tǒng)實現(xiàn)類映射
? 首先通過聲明系統(tǒng)，定義基類
>>> from sqlalchemy.ext.declarative import declarative_base
>>> Base = declarative_base()
創(chuàng)建映射類
? 一旦創(chuàng)建了基類，就可以創(chuàng)建自定義映射類了
>>> 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 "<Department(dep_name='%s')>" % self.dep_name
//__repr__是可選項
創(chuàng)建架構
? 類構建完成后，表的信息將被寫入到表的元數(shù)據(jù) （metadata）
>>> Departments.__table__
Table('departments', MetaData(bind=None), Column('dep_id', Integer(),
table=<departments>, primary_key=True, nullable=False), olumn('dep_name', String(), table=<departments>), schema=None)
創(chuàng)建架構（續(xù)1）
? 通過表的映射類，在數(shù)據(jù)庫中創(chuàng)建表
>>> Base.metadata.create_all(engine)
創(chuàng)建映射類的實例
? 創(chuàng)建實例時，并不會真正在表中添加記錄
dep_dev = Departments(dep_name='developments')
print(dep_dev.dep_name)
print(str(dep_dev.dep_id))
創(chuàng)建會話類
? ORM訪問數(shù)據(jù)庫的句柄被稱作Session
>>> from sqlalchemy.orm import sessionmaker
>>> Session = sessionmaker(bind=engine)
如果在創(chuàng)建session前還未創(chuàng)建engine，操作如下
>>> Session = sessionmaker()
>>> Session.configure(bind=engine) //創(chuàng)建engine后執(zhí)行
添加新對象
? 會話類的實例對象用于綁定到數(shù)據(jù)庫
? 實例化類的對象，并不打開任何連接
? 當實例初次使用，它將從Engine維護的連接池中獲 得一個連接
? 當所有的事務均被commit或會話對象被關閉時，連接結束
>>> session = Session()
>>> session.add(dep_dev)
>>> session.commit()
>>> print(str(dep_dev.dep_id))
>>> session.close()
添加新對象（續(xù)1）
? 可以創(chuàng)建多個實例，批量添加記錄
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映射關系也可用于表間創(chuàng)建外鍵約束
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 "<Employees(name='%s')>" % self.name
新建數(shù)據(jù)庫
MariaDB [(none)]> create database mqe default charset utf8;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use mqe;
Database changed
dbconn.py
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine(
# mysql+pymymysql://用戶名:密碼@服務器/數(shù)據(jù)庫?參數(shù)
'mysql+pymysql://root:123@127.0.0.1/mqe?charset=utf8',
encoding='utf8',
# echo=True # 在屏幕上輸出日志，生產(chǎn)環(huán)境中不要使用
)
# 創(chuàng)建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__':
# 如果庫中沒有相關的表則創(chuàng)建，有的話不會創(chuàng)建
Base.metadata.create_all(engine)
[root@localhost ~]# python3 dbconn.py
MariaDB [(none)]> use mqe;
Database changed
MariaDB [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, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine(
# mysql+pymymysql://用戶名:密碼@服務器/數(shù)據(jù)庫?參數(shù)
'mysql+pymysql://root:tedu.cn@127.0.0.1/mqe?charset=utf8',
encoding='utf8',
# echo=True # 在屏幕上輸出日志，生產(chǎn)環(huán)境中不要使用
)
# 創(chuàng)建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__':
# 如果庫中沒有相關的表則創(chuàng)建，有的話不會創(chuàng)建
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, Date
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine(
# mysql+pymymysql://用戶名:密碼@服務器/數(shù)據(jù)庫?參數(shù)
'mysql+pymysql://root:tedu.cn@127.0.0.1/tedu1812?charset=utf8',
encoding='utf8',
# echo=True # 在屏幕上輸出日志，生產(chǎn)環(huán)境中不要使用
)
# 創(chuàng)建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__':
# 如果庫中沒有相關的表則創(chuàng)建，有的話不會創(chuàng)建
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
....
# 創(chuàng)建ORM的基類
Base = declarative_base()
Session = sessionmaker(bind=engine)
....
創(chuàng)建表
1. 創(chuàng)建employees表
2. 創(chuàng)建部門表
3. 創(chuàng)建salary表
4. 表間創(chuàng)建恰當?shù)年P系
添加數(shù)據(jù)
1. 分別在部門表、員工表和工資表中加入數(shù)據(jù)
2. 通過SQLAlchemy代碼實現(xiàn)
3. 分別練習每次加入一行數(shù)據(jù)和每次可加入多行數(shù)據(jù)
SQLAlchemy進階
查詢操作
基本查詢
? 通過作用于session的query()函數(shù)創(chuàng)建查詢對象
? query()函數(shù)可以接收多種參數(shù)
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描述符進行查詢
? 使用ORM描述符進行查詢
? 返回值是元組
from myorm import Employees, Session
session = Session()
for name, phone in session.query(Employees.name,Employees.phone):
print(name, phone)
排序
? 通這order_by()函數(shù)可以實現(xiàn)按指定字段排序
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)
提取部分數(shù)據(jù)
? 通過“切片”的方式，實現(xiàn)部分數(shù)據(jù)的提取
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()函數(shù)實現(xiàn)結果過濾
from myorm import Session, Departments
session = Session()
for row in
session.query(Departments.dep_name).filter(Departments.dep_id==2):
print(row.dep_name)
結果過濾（續(xù)1）
? filter()函數(shù)可以疊加使用
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'))
常用過濾操作符（續(xù)1）
? 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()方法實現(xiàn)多表查詢
q = session.query(
Employees.name, Departments.dep_name).join(Departments)
print(q.all())
更新數(shù)據(jù)
? 通過會話的update()方法更新
from myorm import Session, Departments
session = Session()
q1 = session.query(Departments).filter(Departments.dep_id==6)
q1.update({Departments.dep_name: '運維部'})
session.commit()
session.close()
更新數(shù)據(jù)（續(xù)1）
? 通過會話的字段賦值更新
from myorm import Session, Departments
session = Session()
q2 = session.query(Departments).get(1) # get(1)查詢主鍵是1的記錄
q2.dep_name = '開發(fā)部'
session.commit()
session.close()
刪除記錄
? 通過會話的delete()方法進行記錄刪除
from myorm import Session, Departments
session = Session()
q1 = session.query(Departments).get(7)
session.delete(q1)
session.commit()
session.close()
crud.py
from dbconn import Session, Department, Employee, Salary
session = 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='開發(fā)部')
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 | 開發(fā)部 |
| 5 | 測試部 |
| 2 | 財務部 |
| 3 | 運維部 |
+--------+-----------+
5 rows in set (0.00 sec)
from dbconn import Session, Department, Employee, Salary
session = 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, Salary
session = Session()
...
#######################
qset1 = session.query(Department)
print(qset1) # qset1只是個sql語句，當取具體值的時候，才真正查數(shù)據(jù)庫
# 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
******************************
[<dbconn.Department object at 0x7f5369838c10>, <dbconn.Department object at 0x7f5369838c90>, <dbconn.Department object at 0x7f5369838d50>, <dbconn.Department object at 0x7f5369838dd0>, <dbconn.Department object at 0x7f5369838e50>]
******************************
1: 人事部
4: 開發(fā)部
5: 測試部
2: 財務部
3: 運維部
from dbconn import Session, Department, Employee, Salary
session = Session()
...
#######################
qset3 = session.query(Employee.emp_name, Employee.email)
# 查詢的參數(shù)是字段，返回的結果是元組
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, Salary
session = 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: 開發(fā)部
from dbconn import Session, Department, Employee, Salary
session = 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
[<dbconn.Department object at 0x7f5471b02e50>]
2 財務部
from dbconn import Session, Department, Employee, Salary
session = 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, Salary
session = 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, Salary
session = 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 開發(fā)部
3 運維部
from dbconn import Session, Department, Employee, Salary
session = 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 開發(fā)部
5 測試部
2 財務部
3 運維部
from dbconn import Session, Department, Employee, Salary
session = Session()
...
#######################
# query中先寫的是Employee，join中要寫Department
qset10 = 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
('王濤', '運維部')
('張鈞', '運維部')
('蘇艷', '人事部')
('吳計印', '開發(fā)部')
('康志文', '開發(fā)部')
('胡志強', '測試部')
('李浩', '財務部')
from dbconn import Session, Department, Employee, Salary
session = 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
('運維部', '王濤')
('運維部', '張鈞')
('人事部', '蘇艷')
('開發(fā)部', '吳計印')
('開發(fā)部', '康志文')
('測試部', '胡志強')
('財務部', '李浩')
from dbconn import Session, Department, Employee, Salary
session = Session()
...
#######################
# 修改數(shù)據(jù)，先找到實例，再給實例的屬性重新賦值
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 | 開發(fā)部 |
| 5 | 測試部 |
| 2 | 財務部 |
| 3 | 運維部 |
+--------+-----------------+
5 rows in set (0.00 sec)
from dbconn import Session, Department, Employee, Salary
session = 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)
操作數(shù)據(jù)
1. 修改部門表，將人事部改為人力資源部
2. 如果存在設計部，將設計部刪除
3. 查詢所有每個員工及其所在部門
