连接

from sqlalchemy import create_engine

create_engine()返回一个Engine的实例，并且它表示通过数据库语法处理细节的核心接口，在这种情况下，数据库语法将会被解释称Python的类方法

 engine = create_engine('mysql+pymysql://root:123456@localhost:3306/test',echo=True)

连接 echo参数为True时，会显示每条执行的sql语句

engine = create_engine('mysql+pymysql://root:123456@localhost:3306/test')

声明映像

<>通过使用Declarative方法，我们可以创建一些包含描述要被映射的实际数据库表的准则的映射类。
<>使用Declarative方法定义的映射类依据一个基类，这个基类是维系类和数据表关系的目录——我们所说的Declarative base class。在一个普通的模块入口中，应用通常只需要有一个base的实例。我们通过declarative_base()功能创建一个基类：

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

有了这个base，我们可以依据这个base定义任意数量的映射类：

from sqlalchemy import Column,Integer,String
class User(Base):   
    __tablename__ = 'users'    #__tablename__是必须的
    id = Column(Integer,primary_key = True)  #主键是必须的
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

【注意】用Declarative 构造的一个类至少需要一个tablename属性，一个主键行。

创建映射类的实例

ed_user = User(name='ed',fullname='Ed join',password='desapssword')
ed_user1 = User(name='two',fullname='myTwo',password='desapssword')

创建会话Session

<>以上已经准备好和数据库会话了，ORM通过Session与数据库建立连接的
<>当应用第一次载入时，我们定义一个Session类（声明Create_engine()的同时），这个Session类为新的Session对象提供工厂服务。

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)

这个定制的Session类会创建绑定到数据库的Session对象。如果需要和数据库建立连接，只需要实例化一个

Session:
session =Session()

虽然上面的Session已经和数据库引擎Engine关联，但是还没有打开任何连接。当它第一次被使用时，就会从Engine维护的一个连接池中检索是否存在连接，如果存在便会保持连接知道我们提交所有更改并且/或者关闭session对象。

添加新对象

session.add(ed_user)
session.add(ed_user1)

至此，我们可以认为，新添加的这个对象实例还在等待着；ed_user对象现在并不代表数据库中的一行数据。直到使用flush进程，Session才会让SQL保持连接。如果查询这条数据的话，所有等待信息会被第一时间刷新，查询结果也会立即发行。
<1>通过commit()可以提交所有剩余的更改到数据库。
<2>注意：提交、查询都会执行所有的等待信息。

 session.commit()

回滚

session.rollback()

查询

通过Session的query()方法创建一个查询对象。这个函数的参数数量是可变的，参数可以是任何类或者类的描述集合
<>下面是一个迭代输出User类的例子：

for instance in session.query(User).order_by(User.id):
    print(instance.name,instance.fullname)

Query也支持ORM描述作为参数。任何时候，多个类的实体或者是基于列的实体表达都可以作为query()函数的参数，返回类型是元组：

for name,fullname in session.query(User.name,User.fullname):
     print(name,fullname)

 for row in session.query(User,User.name).all():
     print(row.User.id,row.name)

label() 相当于row.name 字段起别名

for row in session.query(User.name.label("name_label")).all():
     print(row.name_label)

aliased()类(数据表)起别名

from sqlalchemy.orm import aliased
user_alias = aliased(User,name='user_alias')
for row in session.query(user_alias,user_alias.name).all():
     print(row.user_alias)
     print(row.name)

Query 的基本操作包括LIMIT和OFFSET，使用python数组切片和ORDERBY结合可以让操作变得很方便。
只查询第二条和第三条数据

 for u in session.query(User).order_by(User.id)[1:3]:
    print(u.name)

使用关键字变量过滤查询结果，filter 和filter_by都使用。

for u in session.query(User).filter(~User.name.in_(['ed'])).all():
    print(u.name)

下面列出几个常用的操作：

query.filter(User.name == 'ed') #equals
query.filter(User.name != 'ed') #not equals
query.filter(User.name.like('%ed%')) #LIKE
uery.filter(User.name.in_(['ed','wendy', 'jack'])) #IN
query.filter(User.name.in_(session.query(User.name).filter(User.name.like('%ed%'))#IN
query.filter(~User.name.in_(['ed','wendy', 'jack']))#not IN
query.filter(User.name == None)#is None
query.filter(User.name != None)#not None

and 操作：

from sqlalchemy import and_
query.filter(and_(User.name =='ed',User.fullname =='Ed Jones')) # and
query.filter(User.name == 'ed',User.fullname =='Ed Jones') # and
query.filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')# and

or操作

from sqlalchemy import or_
query.filter(or_(User.name =='ed', User.name =='wendy')) #or
query.filter(User.name.match('wendy')) #match

返回列表和数量
all()返回一个列表：可以进行python列表操作。

#all() 所有的
l=session.query(User).filter(User.name.like('%t%')).order_by(User.id).all()   
 #first() 第一条 
l=session.query(User).filter(User.id>1).order_by(User.id).first()
 #one() 唯一的一条
 l=session.query(User).filter(User.id == 2).order_by(User.id).one()   

统计 计数
count() 用来统计查询结果的数量。

 count = session.query(User).filter(User.name.like("%t%")).count()
# print(count)

func.count() 方法比count() 更高级一点

from sqlalchemy import func
fc=session.query(func.count(User.name),User.name).group_by(User.name).all()
 

总结 常用技巧

创建一个test库
create database test;
授权一个用户
grant all privileges on *.* to 'xiang'@'%' 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, 'ling', 25, 'M', 99), (2, 'ajing', 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='ling'

链接数据库
Python2 使用的是MySQLdb
python3 使用的pymysql  pip安装

1. 创建链接和游标
注意：在mysql连接中，尽量使用一个连接，确保mysql的并发数
conn = pymysql.connect(host='', port=, user='', passwd='', db='')
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
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, 'ling')
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 '%ling%';
模糊查询含有ling的关键字

模糊查询
session.query(Student).filter(Student.name like('%ling%'))

获取数据的时候有两个方法：
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(属性)