Python学习_19 数据库操作复习



Python学习_19 数据库操作复习

1、数据库sql操作复习

a、创建一个库

mysql> create database yanzi;

b、授权一个用户

grant all privileges on *.* to 'yanzi'@'%' identified by 'password' with grant option;

c、创建一个表

create table student(id int not null, )

d、查询

select * from table_name where 条件1 and 条件2;

e、增

insert into table_name(id, name, age, sex, grander) values (1, 'yanzi', 25, 'M', 99),  (2, 'aj', 45, 'F', 87);

f、改

update table_name set id=10 where 条件

g、删除

delect from table_name where 条件;

删表：trop table table_name;

h、联合查询

select a.id,b.name from A a jion B b on a.id=b.id;

i、创建索引

create index idx_yanzidb_tablename_id_name;

j、查看sql是否走索引

explain select * from student where name='yanzi';

2、数据库连接

python2使用的是MySQLdb

python3使用的是pymysql

a、创建连接和游标

注：在mysql连接中，尽量使用一个连接，确保mysql并发数

import pymysql

con = pymysql.connect(host='193.112.207.252', port=3306, user='yanzi', passwd='yanzi1097',db='yanzi')

cus = con.cursor()

b、执行sql

#!/usr/bin/env python

# -*- coding: utf-8 -*-

# @Time : 2018\5\13 0013 20:03

# @Author : xiexiaolong

# @File : demon1.py

import pymysql

con = pymysql.connect(host='193.112.207.252', port=3306, user='yanzi', passwd='yanzi1097',db='yanzi')

cus = con.cursor()

sql = '''select * from student '''

cus.execute(sql)

a = cus.fetchall()

print(a)

c、关闭游标和数据库

cus.close()

con.close()

注：结合try exception finally的使用

例：

#!/usr/bin/env python

# -*- coding: utf-8 -*-

# @Time    : 2018\5\13 0013 20:03

# @Author  : xiexiaolong

# @File    : demon1.py

import pymysql

con = pymysql.connect(host='193.112.207.252', port=3306, user='yanzi', passwd='yanzi1097',db='yanzi')

cus = con.cursor()

sql = '''insert  student (id, name, age, address)  values (1004, 'xiao', 23, 'gansu') '''

try:

    cus.execute(sql)

con.commit ()

except:

    con.Error

finally:

    cus.close()

    con.close()

3、AQLAlchemy

把一个tuple用class实例来表示，把关系数据库的表结构映射到对象上

a、创建引擎

engine = create_engine( 'mysql+pymysql://yanzi:yanzi1097@193.112.207.252:3306/yanzi' )

b、创建session

DBsession = sessionmaker(bind=engine)

session = DBsession

c、创建表

1.获得engine

2.metadata = MetaData(engine)

3.student = Table('表名', metadata, Colume('id', Integer, primary_key=True), Colume('name', String(50))

4.metadata.create_all()

例：

注： metadata.create_all(engine) 方法会判断操作的表是否存在，如果不存在则创建，如果存在则不创建

#!/usr/bin/env python

# -*- coding: utf-8 -*-

# @Time    : 2018\5\13 0013 22:43

# @Author  : xiexiaolong

# @File    : demon2.py

from sqlalchemy import engine, create_engine, MetaData, Table, Column, Integer, String

from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql+ pymysql://yanzi:yanzi1097@193.112.207.252:3306/yanzi ')

DBsession = sessionmaker(bind=engine)

session = DBsession

metadata = MetaData(engine)

teachers = Table('teachers', metadata,

                 Column('id', Integer, primary_key=True),

                 Column('name', String(50)),

                 Column('age', Integer),

                 Column('sex', String(10)),

                 Column('name', String(50))

)

metadata.create_all(engine)

d、添加数据

建一个类

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])

例：

#!/usr/bin/env python

# -*- coding: utf-8 -*-

# @Time    : 2018\5\13 0013 23:10

# @Author  : xiexiaolong

# @File    : demon3.py

#导入

from sqlalchemy import engine, create_engine, MetaData, Table, Column, Integer, String

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import sessionmaker

#初始化数据库连接

engine = create_engine('mysql+ pymysql://yanzi:yanzi1097@193.112.207.252:3306/yanzi ')

#创建DBsession类型

DBsession = sessionmaker(bind=engine)

session = DBsession()

#创建对象的基类

Base = declarative_base()

#定义表对象

class Teachers(Base):

    __tablename__ = 'teachers'

    id = Column(Integer, primary_key=True)

    name = Column(String(50))

    age = Column(Integer)

    address = Column(String(100))

teachers1 = Teachers(id=101, name='yanzi', age=23, address="shenzh")

teachers2 = Teachers(id=102, name='yanzi1', age=23, address="shenzh3")

teachers3 = Teachers(id=103, name='yanzi2', age=23, address="shenzh4")

teachers4 = Teachers(id=104, name='yanzi3', age=23, address="shenzh56")

session.add_all([teachers1,teachers2,teachers3,teachers4])

session.commit ()

session.closs()

e、查询

filter和filter_by的区别

filter：可以使用> < 等，但是列必须是： 表.列， filter的等于号是==

filter 不支持组合查询

filter_by： 可以直接写列，不支持< > filter_by 等于是=

filter_by 可以支持组合查询

session.query(Student).filter_by(name='ling' and id='342')

模糊查询

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

获取数据的时候有两个方法：

one()   tuple

all()   list(单个元素是tuple)

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

例：

#!/usr/bin/env python

# -*- coding: utf-8 -*-

# @Time    : 2018\5\13 0013 23:52

# @Author  : xiexiaolong

# @File    : demon4.py

from sqlalchemy import create_engine, Column, Integer, String

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql+ pymysql://yanzi:yanzi1097@193.112.207.252/yanzi ')

DBsession = sessionmaker(bind=engine)

session = DBsession()

Base = declarative_base()

class Teachers(Base):

    __tablename__ = 'teachers'

    id = Column(Integer, primary_key=True)

    name = Column(String(50))

    age = Column(Integer)

    address = Column(String(100))

select = session.query(Teachers).filter(Teachers.id==101).all()

print(select)

for i in select:

    print(i.id)

结果：

D:\python\venv\Scripts\python.exe D:/python/0513/demon4.py

[<__main__.Teachers object at 0x00000000037F38D0>]

101

Process finished with exit code 0

f、更新

1.  先查出来

2. 跟新一下类所对应的属性值就ok

3. session.commit ()

student1 = session.query(Student).filter(Student.id=1001)

student1.name = "test"

session.commit ()

g、删除

1. 先查出来

2. 直接调用delete()方法就可以

3. session.commit ()

h、统计、分组、排序

统计：count()

只需要在查出来以后， 把one或者all替换成count()

分组：group_by

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

排序：

例：

#!/usr/bin/env python

# -*- coding: utf-8 -*-

# @Time    : 2018\5\13 0013 23:52

# @Author  : xiexiaolong

# @File    : demon4.py

from sqlalchemy import create_engine, Column, Integer, String

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Teachers(Base):

    __tablename__ = 'teachers'

    id = Column(Integer, primary_key=True)

    name = Column(String(50))

    age = Column(Integer)

    address = Column(String(100))

def update(session):

    teachers1 = session.query(Teachers).filter(Teachers.id==101).one()

    teachers1.name='test1'

session.commit ()

def delect(session):

    session.query(Teachers).filter(Teachers.id==102).delete()

session.commit ()

def insert(session):

    teachers1 = Teachers(id=105, name='inset1',age=34, address='dddd')

    session.add(teachers1)

session.commit ()

def count(session):

    number = session.query(Teachers).filter(Teachers.id>100).count()

    print("count is {0}".format(number))

def groupby(session):

    groupByAge = session.query(Teachers).group_by(Teachers.age).all()

    print("groupByAge is {0}".format(groupByAge))

    for i in groupByAge:

        print(i.id,i.name,i.age,i.address)

def orderBy(session):

    orderByAge = session.query(Teachers).order_by(Teachers.age.desc()).all()

    for i in orderByAge:

        print(i.id,i.name,i.age,i.address)

def main():

    engine = create_engine('mysql+ pymysql://yanzi:yanzi1097@193.112.207.252/yanzi ')

    DBsession = sessionmaker(bind=engine)

    session = DBsession()

    insert(session)

    update(session)

    delect(session)

    count(session)

    groupby(session)

    orderBy(session)

if __name__ == '__main__':

    main()