廖雪峰Python学习笔记day16

学习笔记day15

# python study day16

# python内置了SQLite3数据库,可以直接使用. SQLite是一种嵌入型数据库
# import sqlite3 # 导入SQLite驱动
# # 连接到SQLite数据库
# # 数据库文件是test.db
# # 如果文件不存在，会自动在当前目录创建
# conn = sqlite3.connect('test.db')
# cursor = conn.cursor() # 创建一个cursor游标
# # 执行一条SQL语句，建表
# cursor.execute('create table user(id primary key, name varchar(20))')
# cursor.execute('insert into user(id, name) values(\'1\',\'Alice\')')
# cursor.rowcount # 获取影响行数
# cursor.close() # 关闭cursor
# conn.commit() # 提交事务
# conn.close() # 关闭连接

# # 查询数据库
# conn = sqlite3.connect('test.db')
# cursor = conn.cursor()
# cursor.execute('select * from user where id = ?', ('1',))
# values = cursor.fetchall() # 获取结果集
# print(values) # >>> [('1', 'Alice')]
# # 结果集是一个list，每个元素都是一个tuple，对应一行记录。
# cursor.close()
# conn.close()
# 
# import os, 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 users(id varchar(20) primary key, name varchar(20), score int)')
# cursor.execute(r"insert into users values ('A-001', 'Adam', 95)")
# cursor.execute(r"insert into users values ('A-002', 'Bart', 62)")
# cursor.execute(r"insert into users values ('A-003', 'Lisa', 78)")
# cursor.close()
# conn.commit()
# conn.close()

# def get_score_in(low, high):
#     try:
#         conn = sqlite3.connect('test.db')
#         cursor = conn.cursor()
#         cursor.execute('select name from (select * from users where score between ? and ? order by score)a', (low, high))
#         values = cursor.fetchall()
#     except:
#         return []
#     finally:
#         cursor.close()
#         conn.close()
#         names = []
#         for per in values:
#             names.append(per[0])
#         return names
# # 测试:
# assert get_score_in(80, 95) == ['Adam'], get_score_in(80, 95)
# assert get_score_in(60, 80) == ['Bart', 'Lisa'], get_score_in(60, 80)
# assert get_score_in(60, 100) == ['Bart', 'Lisa', 'Adam'], get_score_in(60, 100)
# print('Pass')

# Mysql 数据库安装驱动后, 使用和SQLite一致
# import mysql.connector
# conn = mysql.connector.connect(user='root', password='root123', database='test')
# cursor = conn.cursor()
# cursor.execute('craete table user(id varchar(20),name varchar(20))')
# cursor.execute('insert into user(id, name) values(\'1\', \'Alice\')')
# cursor.rowcount()
# conn.commit()
# cursor.close()
# cursor = conn.cursor()
# cursor.execute('select * from user where id= %s', ('1',)) # Mysql的占位符是%s
# values = cursor.fetchall()
# print(values)
# cursor.close()
# conn.close()

# AQLAlchemy ORM(Object-Relational-Mapping)持久层框架
# from sqlalchemy import Column, String, create_engine
# from sqlalchemy.orm import sessionmaker
# from sqlalchemy.ext.declarative import declarative_base
# 
# Base = declarative_base() # 创建对象基类
# 
# class User(Base):
#     __tablename__ = 'user' # 表名
#     id = Column(String(20), primary_key=True) # 表字段结构
#     userName = Column(String(20))
# 
# # 初始化数据库连接：'数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'
# engine = create_engine('mysql+mysqlconnector://root:root123@localhost:3306/test')
# DBSession = sessionmaker(bind=engine) # 创建DBSession类型    
# # 添加记录  
# session = DBSession() # 创建session对象
# new_user = User(id='11', userName='Bob') # 创建新User对象
# session.add(new_user) # 添加到session
# session.commit() # 提交保存
# session.close()
# # 查询记录
# session = DBSession()
# # 创建Query查询，filter是where条件，调用one()返回唯一行，调用all()返回所有行
# user = session.query(User).filter(User.id=='11').one()
# print('type', type(user))
# print('name', user.name) 
# session.close()  
# 一对多关系
# class User(Base):
#     __tablename__ = 'user'
#     id = Column(String(20), primary_key)
#     name = Column(String(20))
#     books = relationship('Book') # 一对多,一个用户多本书
# class Book(Base):
#     __tablename__ = 'book'
#     id = Column(String(20), primary_key)
#     name = Column(String(20))
#     user_id = Column(String(20), ForeignKey('user.id')) # 外键关联id

学习笔记day17