Python学习笔记Day12 - mysql、sqlite数据库操作

目录

1.mysql数据库 # 免费且开源，最流行

https://www.cnblogs.com/alex3714/articles/5950372.html

2.RDBMS 术语：

数据库，数据表，列，行
冗余：多余字段，用来提高查询速度
主键：唯一，标识 primary key
外键：用于关联两个表
复合键：将多个列作为一个索引键
索引：使用索引可快速访问数据库表中的特定信息。
索引是对数据库表中一列或多列的值进行排序的一种结构。
参照完整性：不允许引用不存在的实体

3.mysql命令

进入mysql命令模式: mysql -u root -p

显示信息：

show databases; # 显示所有的数据库
use mysql; # 打开数据库mysql
show tables; # 显示所有的表
desc user; # 显示表user结构
show columns from user; # 显示表user结构
select USER from user/G; # 显示USER字段数据
select * from user/G; # 显示所有数据

数据库操作：

create database testdb charset "utf8"; # 创建数据库，且让其支持中文
drop database testdb; # 删除数据库
show index from user; # 显示数据表的详细索引信息，包括PRIMARY KEY（主键）
quit; # 退出

4.数据库操作命令

创建表：

create table student(
stu_id INT NOT NULL AUTO_INCREMENT,
name CHAR(32) NOT NULL,
age INT NOT NULL,
register_date DATE,
PRIMARY KEY ( stu_id )
);
(1)如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
(2)AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
(3)PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。

插入数据：

insert into student (name,age,register_date) values ("alex li",22,"2016-03-4");

查询数据：

select column_name,column_name(*) from student where id>3 limit 3 offset 2;
(1)OFFSET指定开始查询的数据偏移量。
(2)LIMIT 属性来设定返回的记录数
(3)WHERE 语句来包含任何条件 [WHERE id>3 [AND (OR)] id<5.....
where date like "2016-04%"; 模糊查询

修改数据：

update student set age=22 ,name="Alex Li" where stu_id>3;

删除数据：

delete from student where stu_id=5;

排序：

select * from student where name like binary "%Li" ORDER BY stu_id desc;
# asc 升序 desc降序，默认升序, binary 只匹配大写

分组并统计：

select name, count(*) from student GROUP BY name;

统计时显示总计：

select coalesce(name, '总数'), SUM(age) as singin_count from student GROUP BY name WITH ROLLUP;
# 默认NULL，可用coalesce命名为'总数'

增删改表字段：

ALTER TABLE student drop register_date; # 删除register_date字段
alter table student add phone_number int(11) not null; # 添加phone_number字段
ALTER table student MODIFY name CHAR(10) not null DEFAULT no_one; #修改字段类型
ALTER table student CHANGE name newname CHAR(10); #修改字段名称和类型

修改表名：

ALTER TABLE testalter_tbl RENAME TO alter_tbl;

5.创建外键：

CREATE TABLE student2 (
id int(11) NOT NULL,
name char(16) NOT NULL,
class_id int(11) NOT NULL, #
PRIMARY KEY (id),
KEY fk_class_key (class_id),
CONSTRAINT fk_class_key FOREIGN KEY (class_id) REFERENCES class (id)
)

MySQL中处理NULL使用IS NULL和IS NOT NULL运算符。

6.连接join联合查询：

select * from a INNER JOIN b on a.a = b.b; #内连接 交集
select a.,b. from a,b where a.a = b.b;
select * from a LEFT JOIN b on a.a = b.b; #相对于左， 交集
select * from a RIGHT JOIN b on a.a = b.b; #相对于右，交集
select * from a FULL JOIN b on a.a = b.b; #并集 ，mysql并不直接支持full join
select * from a left join b on a.a = b.b UNION select * from a right join b on a.a = b.b;

7.事务：

一般来说，事务是必须满足4个条件（ACID）： Atomicity、Consistency、Isolation、Durability

  1. 事务的原子性（Atomicity）：一组事务，要么成功；要么撤回。

  2. 稳定性（Consistency）： 有非法数据（外键约束之类），事务撤回。

  3. 隔离性（Isolation）：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。
    事务的100%隔离，需要牺牲速度。

  4. 可靠性（Durability）：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。
    可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项 决定什么时候把事务保存到日志里。

    begin; ＃开始一个事务
    insert into a (a) values(555);
    rollback; 回滚，这样数据是不会写入的
    commit; 没问题提交命令就行；

8.索引：

单列索引&多列索引
主键即索引

创建索引：

CREATE INDEX indexName ON mytable(username(length));
ALTER mytable ADD INDEX [indexName] ON (username(length)) #alter修改表结构时指定
INDEX [indexName] (username(length)) #创建表时添加

删除索引：

DROP INDEX [indexName] ON mytable;

唯一索引:

与普通索引类似，不同点：索引列的值必须唯一，但允许有空值。
如果是组合索引，则列值的组合必须唯一。
CREATE UNIQUE INDEX indexName ON mytable(username(length))

显示索引信息:

SHOW INDEX FROM table_name\G

mysql练习题　　
更多mysql知识

9.pymysql python 操作 mysql 采用原生语句，比较复杂

import pymysql

创建连接

conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='ybn3201862', db='testdb')
# 创建游标
cursor = conn.cursor()

执行SQL，并返回受影响行数

effect_row = cursor.execute("insert into student (name,age,register_date) values ('jerome',24,'2018-09-26');")

data = [
('liu', 23, '2018-09-26'),
('li', 35, '2018-09-23'),
('Ma', 21, '2018-09-24'),
]
# 执行多行，默认事务
# effect_row = cursor.executemany("insert into student(name,age,register_date) values(%s,%s,%s)", data)

effect_row = cursor.execute("select * from student;")
print(effect_row)
print(cursor.fetchall())

提交，不然无法保存新建或者修改的数据

conn.commit()

关闭游标

cursor.close()
# 关闭连接
conn.close()

10.orm,英文全称object relational mapping,就是对象映射关系程序

通过orm将编程语言的对象模型和数据库的关系模型建立映射关系，这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了，而不用直接使用sql语言。

11.sqlalchemy python的MySQL操作方法

http://www.cnblogs.com/alex3714/articles/5978329.html

  1. 增删改查

    import sqlalchemy
    from sqlalchemy import create_engine
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy import Column, Integer, String, ForeignKey
    from sqlalchemy.orm import sessionmaker,relationship

    创建引擎，连接数据库,echo是否打印信息,echo = True

    engine = create_engine("mysql+pymysql://root:ybn3201862@localhost/testdb",
    encoding='utf-8')
    # engine = create_engine(r'sqlite:///C:\path\to\foo.db') # sqlite3
    # echo=Ture----echo默认为False，表示不打印执行的SQL语句等较详细的执行信息，改为Ture表示让其打印。

    check_same_thread=False----sqlite默认建立的对象只能让建立该对象的线程使用，而sqlalchemy是多线程的所以我们需要指定check_same_thread=False来让建立的对象任意线程都可使用。否则不时就会报错

    Base = declarative_base() # 生成orm基类

    class User(Base):
    tablename = 'user' # 表名
    id = Column(Integer, primary_key=True) # 主键
    name = Column(String(32)) # 列
    password = Column(String(64))

    def repr(self):
    return '<%s name:%s>' % (self.id, self.name)

    Base.metadata.create_all(engine) # 创建表结构 （通过父类调用子类）
    Session_class = sessionmaker(bind=engine) # 1.创建与数据库的会话session class类，绑定数据库引擎
    Session = Session_class() # 2.生成session实例

    添加

    user_obj = User(name = 'alex', password = 'alex3714') # 3.生成要创建的数据对象
    # user_obj = User(**{'name' :'alex', 'password' : 'alex3714'})
    Session.add(user_obj) # 4.把要创建的数据对象添加到session里，一会统一创建
    # Session.add_all([obj1,obj2]) #批量添加
    Session.commit() # 5.提交session，创建数据

    查询 # 默认不匹配大小写

    filter中写表示式，就类似python中的判断条件，比如类名.字段名 == "xxxx"；类名.id > 2

    filter_by中写参数，比如类名.字段名 = "sss"

    data = Session.query(User).filter_by(name="alex").all() # filter_by只能等于查
    # print(Session.query(User.name,User.id).all() ) #获取所有数据
    # data = Session.query(User).filter_by(name="alex").first() # first()取第一个
    # all() 以列表形式返回结果
    # first() 返回第一个结果，如果没有返回None
    # data = Session.query(User).filter(User.name == "alex").all() # filter() 需要加类名，双等号
    # data = Session.query(User).filter(User.id > 2).filter(User.id < 2).all() # filter() 大于
    print(data)

    修改

    data = Session.query(User).filter(User.name=="alex").all() # 修改要先查询
    data.name = 'Jack'
    data.password = '123412'

    删

    Session.query(User).filter(User.id > 2).delete()

    Session.rollback() # 此时你rollback一下 回滚

    print(Session.query(User).filter(User.name.in_(['Jack', 'rain'])).all()) # 再查就发现刚才添加的数据没有了。

    统计

    Session.query(User).filter(User.name.like("Ra%")).count()

    分组统计 # 按(func.count(User.name),User.name)输出

    from sqlalchemy import func

    print(Session.query(func.count(User.name), User.name).group_by(User.name).all())

  2. 外键

  3. 一对多外键

    外键

    class StudyRecord(Base):
    tablename = 'StudyRecord'
    id = Column(Integer, primary_key=True)
    date = Column(Integer, nullable=False)
    status = Column(String(32), nullable=False)
    stu_id = Column(Integer, ForeignKey('user.id')) # 外键绑定
    # 最NB的地方：允许你在user表里通过backref字段反向查出所有它在study record表里的关联项
    user = relationship("User", backref="study_record")

    def repr(self):
    return '<%s day:%s status:%s>' % (self.user.name, self.stu_id,self.status) #通过user.name反查

    Base.metadata.create_all(engine) # 创建表结构 （通过父类调用子类）

    study_obj = Session.query(StudyRecord).filter(User.id ==1).first()
    print(study_obj)

    连接join查询

    print(Session.query(User,Student).filter(User.id == Student.id).all())

    print(Session.query(Person).join(Favor).all()) # 需要外键关联

    obj转换为dict

    new_item = {c.name: getattr(item, c.name) for c in item.table.columns}

  4. 多对多关系（两个多对一）

    from sqlalchemy import Integer, ForeignKey, String, Column,create_engine
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy.orm import relationship,sessionmaker

    创建引擎，连接数据库,echo是否打印信息,echo = True

    engine = create_engine("mysql+pymysql://root:ybn3201862@localhost/testdb",
    encoding='utf-8')

    Base = declarative_base()

    class Customer(Base):
    tablename = 'customer'
    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    billing_address_id = Column(Integer, ForeignKey("address.id")) # 两个外键,分别指定id
    shipping_address_id = Column(Integer, ForeignKey("address.id"))

    billing_address = relationship("Address", foreign_keys=[billing_address_id])
    shipping_address = relationship("Address", foreign_keys=[shipping_address_id])
    # 两个外键时反向查系统分不清，需要加foreign_keys=指定

    class Address(Base):
    tablename = 'address'
    id = Column(Integer, primary_key=True)
    street = Column(String(64))
    city = Column(String(64))
    def repr(self):
    return self.street

    Base.metadata.create_all(engine)

    session_class = sessionmaker(bind=engine) # 1.创建与数据库的会话session class类，绑定数据库引擎
    session = session_class() # 2.生成session实例

    addr1 = Address(street='xueyuanlu1', city='Bj')
    addr2 = Address(street='xueyuanlu2', city='Bj')
    addr3 = Address(street='xueyuanlu3', city='Bj')

    session.add_all([addr1,addr2,addr3])

    c1 = Customer(name='jack',billing_address=addr2,shipping_address=addr3)
    c2 = Customer(name='clack',billing_address=addr1,shipping_address=addr1)

    session.add_all([c1,c2])

    obj = session.query(Customer).filter(Customer.name =='jack').first()
    print(obj.name,obj.billing_address,obj.shipping_address)

12.利用api，创建和实例化增删改查分开

m2m_api.py

一本书可以有多个作者，一个作者又可以出版多本书

from sqlalchemy import Table, Column, Integer,String,DATE, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

创建表的第二种方式，建立两张表之间的关联,复合外键

book_m2m_author = Table('book_m2m_author', Base.metadata,
Column('book_id',Integer,ForeignKey('books.id')),
Column('author_id',Integer,ForeignKey('authors.id')),
)

class Book(Base):
tablename = 'books'
id = Column(Integer,primary_key=True)
name = Column(String(64))
pub_date = Column(DATE)
authors = relationship('Author',secondary=book_m2m_author,backref='books')

def repr(self):
return self.name

class Author(Base):
tablename = 'authors'
id = Column(Integer, primary_key=True)
name = Column(String(32))

def repr(self):
return self.name

创建引擎，连接数据库,echo是否打印信息,echo = True

engine = create_engine("mysql+pymysql://root:ybn3201862@localhost/testdb?charset=utf8")

Base.metadata.create_all(engine)

m2m.py

from Day12_mysql import m2m_api
from sqlalchemy.orm import sessionmaker

Session_class = sessionmaker(bind=m2m_api.engine) # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
session = Session_class() # 生成session实例

创建

b1 = m2m_api.Book(name="跟Alex学Python")
b2 = m2m_api.Book(name="跟Alex学把妹")
b3 = m2m_api.Book(name="跟Alex学装逼")
b4 = m2m_api.Book(name="跟Alex学开车")

a1 = m2m_api.Author(name="Alex")
a2 = m2m_api.Author(name="Jack")
a3 = m2m_api.Author(name="Rain")

创建关联关系

b1.authors = [a1, a2]
b2.authors = [a1, a2, a3]

session.add_all([b1, b2, b3, b4, a1, a2, a3])

查询

author_obj = session.query(m2m_api.Author).filter(m2m_api.Author.name == 'Alex').first()
print(author_obj.books[1])
book_obj = session.query(m2m_api.Book).filter(m2m_api.Book.id == 1).first()
print(book_obj.authors)

删除

book_obj.authors.remove(author_obj) #从一本书里删除一个作者

author_obj =session.query(m2m_api.Author).filter_by(name="Alex").first()

# print(author_obj.name , author_obj.books)

session.delete(author_obj) #删除一个作者

session.commit()

13.数据库支持中文

engine = create_engine("mysql+pymysql://root:ybn3201862@localhost/testdb?charset=utf8")

SQLite数据库

导入SQLite驱动:

import sqlite3

连接到SQLite数据库

数据库文件是test.db

如果文件不存在，会自动在当前目录创建:

conn = sqlite3.connect('test.db')

将结果转换成字典输出

def dict_factory(cursor, row):

d = {}

for idx, col in enumerate(cursor.description):

d[col[0]] = row[idx]

return d

# 添加：需要放在cursor定义前

conn.row_factory = dict_factory

创建一个Cursor:

cursor = conn.cursor()

执行一条SQL语句，创建user表:

cursor.execute(
'create table user (id varchar(20) primary key, name varchar(20))')

继续执行一条SQL语句，插入一条记录:

cursor.execute("insert into user (id, name) values ('1', 'Michael')")
# 使用Cursor对象执行insert，update，delete语句时，
# 执行结果由rowcount返回影响的行数，就可以拿到执行结果。

通过rowcount获得插入的行数:

cursor.rowcount

执行查询语句:

如果带有参数，需要把参数用?按照位置传递，有几个?占位符就必须对应几个参数

cursor.execute('select * from user where name=? and pwd=?',
('abc', 'password'))
# cursor.execute('select * from user where id=?', ('1',))

查询表字段

cursor.execute('select * from work_record')
col_name_list = [tuple[0] for tuple in cursor.description]
print(col_name_list)

查询表名：

自带sqlite_master表储存table等数据

cursor.execute('select name from sqlite_master where type = "table"')

获得查询结果集:

values = cursor.fetchall()
print(values)
# 结果集是一个list，每个元素都是一个tuple，对应一行记录。
# [('1', 'Michael')]

关闭Cursor:

cursor.close()
# 提交事务:
conn.commit()
# 关闭Connection:
conn.close()

222

import os
import sqlite3

已存在则移除

db_file = os.path.join(os.path.dirname(file), 'test.db')
if os.path.isfile(db_file):
os.remove(db_file)

初始数据:

conn = sqlite3.connect(db_file)
cursor = conn.cursor()
cursor.execute('create table user(id varchar(20) primary key, name varchar(20), score int)')
cursor.execute(r"insert into user values ('A-001', 'Adam', 95)")
cursor.execute(r"insert into user values ('A-002', 'Bart', 62)")
cursor.execute(r"insert into user values ('A-003', 'Lisa', 78)")
cursor.close()
conn.commit()
conn.close()

def get_score_in(low, high):
'返回指定分数区间的名字，按分数从低到高排序 '
conn = sqlite3.connect(db_file)
cursor = conn.cursor()
cursor.execute('select name from user where score>? and score<=? order by score', (low, high))
res_list = cursor.fetchall()
cursor.close()
conn.close()
return list(map(lambda res_tuple: res_tuple[0], res_list))

测试:

assert get_score_in(80, 95) == ['Adam']
assert get_score_in(60, 80) == ['Bart', 'Lisa']
assert get_score_in(60, 100) == ['Bart', 'Lisa', 'Adam']

print('Pass')

posted @ 2020-07-04 23:51  Jerome12138  阅读(163)  评论(0)    收藏  举报