SQLAlchemy总结+
SQLAlchemy总结+
老七Linux
发布于 2018-05-31 09:49:59
发布于 2018-05-31 09:49:59
Review：
一、Mysql 操作
创建一个test库
create database test;
授权一个用户
grant all privileges on *.* to 'asd'@'%' identified by 'awerfsdf123';
创建表
create table student(id int not null);
查询
select * from tabel_name where 条件1 and 条件2
增加
insert into table_name (id, name, age, sex, grander) values(1001, 'zhdya', 28, 'M', 99), (1002, 'ajing', 45, 'F', 86);
修改
update table_name set id=10 where 条件判断
删除
delete from table_name where 条件判断
drop table table_name
联合查询
select a.id, b.name from A a join B b on a.id=b.tid
创建索引
create index idx_库名_表名_列名1_列名2 (列名1， 列名2)
查看sql是否走索引
explain select * from student where name='ling'二、链接数据库
Python2 使用的是MySQLdb
python3 使用的pymysql pip安装操作
1. 创建链接和游标
注意：在mysql连接中，尽量使用一个连接，确保mysql的并发数
conn = pymysql.connect(host='192.168.161.161', port=3306, user='root', passwd='aaaaa', db='zhdya')
cus = conn.curse()
2. 执行sql
sql = "select * from Student;"
cus.execute(sql)
cus.fetchone() 获取单个 返回值 tuple
cus.fetchall() 获取多个 返回值 list（单个元素是tuple）
cus.fetchmany(size=n) 获取多个
3. 关闭游标和连接
cus.close()
conn.close()
注意结合 try... exception.... finally 的使用SQLAlchemy
SQLAlchemy 是一种对象关系映射模型（Object Relational Mapper）， 简称ORM。
它展现了一种将用户定义的python中的类映射到数据库中表的方法。类的实例，就相当于表中的一行数据。简单来说呢， 就是让我们从SQL语句中抽离出来，只需要按照python的语法来写， 它会自动转换为相对应的SQL语句。
python中的类 == 数据库中的表
python中的类的属性 == 表中的字段
python中类的实例 == 表中的行1. 创建引擎
engine = create_engine('mysql+pymysql://username:password@hostname:port/db')2. 创建session
DBsession = sessionmaker(bind=engine)
session = DBsession()3.创建表
a. 获得engine
b. metadata = MetaData(engine)
c. student = Table('表名', metadata, Colume('id', Integer, primary_key=True), Colume('name', String(50))
d. metadata.create_all()4.插入数据
a. 先要有一个模型 ★★★★★
Base = declarative_base()
class Student(Base):
__tablename__ = 'student'
id = Column(Integer, primary_key=True)
name = Column(String(100), primary_key=True)
b. 导入模型类，实例化该类，
sutdent1 = Student(10001, 'zhang')
c. session.add(单实例) session.add_all([实例1, 实例2])5. 查询
filter和filter_by的区别：
filter：可以使用> < 等，但是列必须是： 表.列， filter的等于号是 ==
session.query(Student).filter(Student.id>100)
filter 不支持组合查询
session.query(Student).filter(Studnet.id>100).filter(name=='ling')
filter_by： 可以直接写列，不支持> < filter_by 等于也是 ==
session.query(Student).filter_by(id==10)
filter_by 可以支持组合查询
session.query(Student).filter_by(name=='ling' and id=='342')
select * from student where name like '%zhdya%';
模糊查询含有zhdya的关键字
模糊查询
session.query(Student).filter(Student.name.like('%zhdya%'))
获取数据的时候有两个方法：
one() tuple
all() list(单个元素是tuple)
如果在查询中不写one(), 或者all() 出来的就是sql语句6. 更新
1. 接着如上的先查出来
2. 更新一下类所对应的属性值就ok
3. session.commit()
student1 = session.query(Student).filter(Student.id==1001)
student1.name = "test"
session.commit()7. 删除
1. 先查出来
2. 直接调用delete()方法就可以
3. 提交一下8.统计， 分组，排序
统计：count()
只需要在查出来以后， 把one或者all替换成count()
统计有多少个
分组：group_by
查出来以后，把one或者all替换成group_by(属性)三、牛刀小试
已经创建了一个 student 数据表：
mysql> desc student;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| age | int(11) | YES | | NULL | |
| sex | varchar(100) | YES | | NULL | |
| address | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)① 插入一些数据 insert
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time : 2018/5/14 18:45
# @Author : zhdya
# @File : REVIEW22.py
from sqlalchemy import create_engine, Table, Column, Integer, MetaData, String
import pymysql
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class Student(Base):
__tablename__ = 'student'
id = Column('id', Integer, nullable=False, autoincrement=True, primary_key=True)
age = Column('age', Integer)
sex = Column('sex', String(100))
address = Column('address', String(100))
def create(session):
student11 = Student(id = 1001, age=23, sex='M', address='Suzhou')
student22 = Student(id = 1002, age=28, sex='F', address='Xuzhou')
session.add_all([student11,student22])
session.commit()
def main():
engine = create_engine("mysql+pymysql://root:[email protected]:3306/zhdya")
DBsession = sessionmaker(bind=engine)
session = DBsession()
create(session)
if __name__ == '__main__':
main()查看结果：
mysql> select * from student;
+------+------+------+---------+
| id | age | sex | address |
+------+------+------+---------+
| 1001 | 23 | M | Suzhou |
| 1002 | 28 | F | Xuzhou |
+------+------+------+---------+
2 rows in set (0.00 sec)② 查询数据 query
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time : 2018/5/14 18:45
# @Author : zhdya
# @File : REVIEW22.py
from sqlalchemy import create_engine, Table, Column, Integer, MetaData, String
import pymysql
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class Student(Base):
__tablename__ = 'student'
id = Column('id', Integer, nullable=False, autoincrement=True, primary_key=True)
age = Column('age', Integer)
sex = Column('sex', String(100))
address = Column('address', String(100))
def create(session):
student11 = Student(id = 1001, age=23, sex='M', address='Suzhou')
student22 = Student(id = 1002, age=28, sex='F', address='Xuzhou')
session.add_all([student11,student22])
session.commit()
def query(session):
aa = session.query(Student).filter(Student.address.like('%Suzhou%'))
for i in aa:
print(i.id)
print(i.age)
print(i.sex)
print(i.address)
def main():
engine = create_engine("mysql+pymysql://root:[email protected]:3306/zhdya")
DBsession = sessionmaker(bind=engine)
session = DBsession()
# create(session)
query(session)
if __name__ == '__main__':
main()
输出：
1001
23
M
Suzhou③ 更新数据 update
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time : 2018/5/14 18:45
# @Author : zhdya
# @File : REVIEW22.py
from sqlalchemy import create_engine, Table, Column, Integer, MetaData, String
import pymysql
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class Student(Base):
__tablename__ = 'student'
id = Column('id', Integer, nullable=False, autoincrement=True, primary_key=True)
age = Column('age', Integer)
sex = Column('sex', String(100))
address = Column('address', String(100))
def create(session):
student11 = Student(id = 1001, age=23, sex='M', address='Suzhou')
student22 = Student(id = 1002, age=28, sex='F', address='Xuzhou')
session.add_all([student11,student22])
session.commit()
def query(session):
aa = session.query(Student).filter(Student.address.like('%Suzhou%'))
for i in aa:
print(i.id)
print(i.age)
print(i.sex)
print(i.address)
def update(session):
bb = session.query(Student).filter(Student.age == 23).one()
bb.age = 22
session.commit()
def main():
engine = create_engine("mysql+pymysql://root:[email protected]:3306/zhdya")
DBsession = sessionmaker(bind=engine)
session = DBsession()
# create(session)
query(session)
update(session)
if __name__ == '__main__':
main()
查看更新的表字段：
mysql> select * from student;
+------+------+------+---------+
| id | age | sex | address |
+------+------+------+---------+
| 1001 | 22 | M | Suzhou |
| 1002 | 28 | F | Xuzhou |
+------+------+------+---------+
2 rows in set (0.01 sec)④ 删除数据 delete
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time : 2018/5/14 18:45
# @Author : zhdya
# @File : REVIEW22.py
from sqlalchemy import create_engine, Table, Column, Integer, MetaData, String
import pymysql
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class Student(Base):
__tablename__ = 'student'
id = Column('id', Integer, nullable=False, autoincrement=True, primary_key=True)
age = Column('age', Integer)
sex = Column('sex', String(100))
address = Column('address', String(100))
def create(session):
student11 = Student(id = 1001, age=23, sex='M', address='Suzhou')
student22 = Student(id = 1002, age=28, sex='F', address='Xuzhou')
session.add_all([student11,student22])
session.commit()
def query(session):
aa = session.query(Student).filter(Student.address.like('%Suzhou%'))
for i in aa:
print(i.id)
print(i.age)
print(i.sex)
print(i.address)
def update(session):
bb = session.query(Student).filter(Student.age == 23).one()
bb.age = 22
session.commit()
def delete(session):
session.query(Student).filter(Student.id >= 1002).delete()
session.commit()
def main():
engine = create_engine("mysql+pymysql://root:[email protected]:3306/zhdya")
DBsession = sessionmaker(bind=engine)
session = DBsession()
# create(session)
query(session)
# update(session)
delete(session)
if __name__ == '__main__':
main()
查看数据：
mysql> select * from student;
+------+------+------+---------+
| id | age | sex | address |
+------+------+------+---------+
| 1001 | 22 | M | Suzhou |
+------+------+------+---------+
1 row in set (0.00 sec)⑤ 统计 count
我就不写全部的函数体了，就是如上的一层层：
def count(session):
cc = session.query(Student).filter(Student.id >= 1002).count()
print(cc)
输出：
3 （刚给我又重新插入了3条数据）⑥ 排序 order_by && group_by
def groupBy(session):
groupby = session.query(Student).group_by(Student.id).all()
for i in groupby:
print(i.id)
def orderBy(session):
orderby = session.query(Student).order_by(Student.id.desc()).all()
for j in orderby:
print(j.id)
输出：
1001
1002
1022
1088
1088
1022
1002
1001
Ps：在使用order_by的时候使用了desc(), 意为：反序排序！！本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2017/05/14，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。