数据库基础
检测mysql相关模块是否导入成功(mysql-connector、 mysql-connector-python模块)
import mysql.connector
print(mysql.connector.apilevel) #api级别
print(mysql.connector.paramstyle) #参数类型
#结果
#2.0
#pyformat
生成数据库
#1.windows 客户端启动mysql,
#2.密码:123456
#3. #创建数据库
mysql> create database amlapp;
创建lesson表
import mysql.connector
conn = mysql.connector.connect(user='root', password='123456', database='amlapp')
cursor = conn.cursor()
cursor.execute('create table lesson(\
ID varchar(20) primary key, \
name varchar(20))'
)
cursor.close()
conn.close()
#结果
#Process finished with exit code 0
案例
#1.创建数据库amltmp
#登录mysql客户端 输入密码123456
#执行 create database amltmp;
#查看 show databases;
#2.创建测试表并插值
import mysql.connector
conn = mysql.connector.connect(user='root', password='123456', database='amltmp')
cursor = conn.cursor()
cursor.execute("create table lilei_test1(\
ID varchar(20) primary key,\
name varchar(20))")
cursor.execute('insert into lilei_test1(id,name) values(%s,%s)',['0001','张三'])
cursor.execute('insert into lilei_test1(id,name) values(%s,%s)',['0002','李四'])
cursor.execute('insert into lilei_test1(id,name) values(%s,%s)',['0003','王五'])
conn.commit() #提交数据
cursor.close() #关闭游标
conn.close() #关闭连接
#3.查询数据
import mysql.connector
conn = mysql.connector.connect(user="root",password="123456",database="amltmp")
cursor = conn.cursor()
cursor.execute("select * from lilei_test1")
x = cursor.fetchall()
print(x)
for i in x:
print(i)
cursor.close()
conn.close()
#结果
#[('0001', '张三'), ('0002', '李四'), ('0003', '王五')]
#('0001', '张三')
#('0002', '李四')
#('0003', '王五')
ORM基础
什么是ORM
Object-Relational-Mapping,把关系数据库的表结构映射到对象上
#数据库查询出来的数据如下:
[('0001', '张三'), ('0002', '李四'), ('0003', '王五')]
('0001', '张三')
('0002', '李四')
('0003', '王五')
#都是以列表或元组的形式展现
#python对象:
class people:
def __init__(self,id,name):
self.id = id
self.name = name
zhangsan = people("0001","张三")
lisi = people("0002","李四")
wangwu = people("0003","王五")
#上述便是关系数据库的表结构映射到对象
#这样的操作并不需要我们一个一个对应映射关系,python的sqlalchemy库已经帮我们实现了!!!
ORM案例(设计)
- 构造数据库:Mysql操作
- 插入数据:Python语句
- 查询数据:Python语句
构造数据库
#生成数据库
create database score;
#查看数据库
show databases;
#进入数据库
use score;
#构建表
create table math(
id varchar(20) primary key,
name varchar(20),
ms int);
#查看表
show tables;
插入数据
from sqlalchemy import Column,create_engine,String,Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base() #基类
#建立类(对象)和数据库表结构的映射
class math(Base):
__tablename__ = 'math'
id = Column(String(20), primary_key=True)
name = Column(String(20))
ms = Column(Integer)
#建立数据库连接
engine = create_engine('mysql+mysqlconnector://root:123456@localhost:3306/score')
#建立会话
DBSession = sessionmaker(bind=engine)
#对象的实例化
Session = DBSession()
#构造对象
zhangsan = math(id='0001',name='张三',ms=88)
lisi = math(id='0002',name='李四',ms=99)
wangwu = math(id='0003',name='王五',ms=66)
#将对象加入到数据库
Session.add(zhangsan)
Session.add(lisi)
Session.add(wangwu)
#提交数据
Session.commit()
#关闭连接
Session.close()
查询数据
from sqlalchemy import Column,String,Integer,create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class math(Base):
__tablename__ = 'math'
id = Column(String(20),primary_key=True)
name = Column(String(20))
ms = Column(Integer)
engine = create_engine('mysql+mysqlconnector://root:123456@localhost:3306/score')
DBSession = sessionmaker(bind=engine)
session = DBSession()
#获取math对象所有数据
user = session.query(math.id,math.name,math.ms).all()
print("type:",type(user))
for i in user:
print(i)
session.close()
#结果
#type: <class 'list'>
#('0001', '张三', 88)
#('0002', '李四', 99)
#('0003', '王五', 66)