SQL相关操作

创建一个test库

create database test;

授权一个用户

grant all privileges on *.* to 'yangjian'@'%' identified by '1qaz@WSX';

创建表

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 (1, 'yangjian', 25, 'M', 99), (2, 'zhangsan', 45, 'F', 88);

更改

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='yangjian';

 

连接数据库

Python2 使用的是MySQLdbpython3 使用的pymysql pip安装

 

1. 创建连接和游标注意：在mysql连接中，尽量使用一个连接，确保mysql的并发数

conn = pymysql.connect(host='', port=, user='', passwd='', db='') cus = conn.cursor()

 

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

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(0 class Student(Base): __tablename__ = 'student' id = Column(Integer, primary_key=True) name = Column(String(100), primary_key=True)

b. 导入模型类，实例化该类

sutdent1 = Student(1, 'yangjian')

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=='yangjian')

 

filter_by： 可以直接写列，不支持< > filter_by 等于是== session.query(Student).filter_by(id==10) filter_by 支持组合查询 session.query(Student).filter_by(name=='yangjian' and id=='342') select * from student where name like '%yangjian%';

 

模糊查询模糊查询含有yang的关键字

session.query(Student).filter(Student.name.like('%ling%'))

获取数据的时候有两个方法：one() tupleall() list(单个元素是tuple)

session.query(Student).filter(Student.name like('%ling%')).one() session.query(Student).filter(Student.name like('%ling%')).all()

如果在查询中不写one(), 或者all()  出来的就是sql语句

 

6. 更新a. 先查出来b. 更新一下类所对应的属性值就可以c. 提交一下session.commit()

student1 = session.query(Student).filter(Student.id==1001) student1.name = "test" session.commit()

 

7. 删除a. 先查出来b. 直接调用delete()方法就可以c. 提交一下

session.query(Student).filter(Student.id == 10001).delete()

 

8.统计， 分组，排序统计：count()只需要在查出来以后， 把one或者all替换成count()

session.query(Student).filter(Student.name.like("%ng%")).count()

分组：group_by查出来以后，把one或者all替换成group_by(属性)

session.query(Student).filter(Student.name.like("%yang%")).order_by(Student.id.desc()).all() #按降序排序

 

下面是一个完整的连接数据库以及增删改查例子：

from sqlalchemy import create_engine, Integer, String, Column from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker Base = declarative_base() class Student(Base): __tablename__ = 'student' id = Column(Integer, primary_key=True) name = Column(String(100)) age = Column(Integer) address = Column(String(100)) # 更新函数 def update(session): student1 = session.query(Student).filter(Student.id == 1001).one() student1.name='test123' session.commit() student2 = session.query(Student).filter(Student.id == 1001).one() print(student2.name)# 删除函数 def delete(session): session.query(Student).filter(Student.id == 1001).delete() session.commit() # 插入函数 def insert(session): student1 = Student(id=1004, name='yangjian', age=28, address='chengdu') session.add(student1) session.commit() # 统计函数 def count(session): numnber = session.query(Student).filter().count() print("total student is {0}".format(numnber)) # 分组函数 def groupBy(session): groupByAge = session.query(Student).group_by(Student.age).all() print(groupByAge) for i in groupByAge: print(i.id, i.name, i.age, i.address) # 排序函数 def orderBy(session): orderByAge = session.query(Student).order_by(Student.age.desc()).all() for x in orderByAge: print(x.id, x.name, x.age, x.address) # 主函数 def main(): engine = create_engine('mysql+pymysql://yangjian:123456@192.168.127.129/sqlalchemy') DBsession = sessionmaker(bind=engine) session = DBsession() insert(session) # 增加 # update(session) # 修改 # delete(session) # 删除 # count(session) # 统计 # groupBy(session) # 分组 #orderBy(session) # 排序 if __name__ == '__main__': main()

 

转载于:https://www.cnblogs.com/yangjian319/p/9036024.html