这两个模块可以直接通过pip安装:
打开mysql创建几个表:
CREATE TABLE `student_age`(
`id` int not null,
`age` int not null,
`sex` ENUM('M', 'F') not null,
PRIMARY KEY(`id`)
);
INSERT INTO student_age(id, age, sex) VALUES(1, 18, 'M'), (2, 26, 'M'), (3, 20, 'F');
CREATE TABLE `student_name`(
`id` int not null auto_increment,
`name` varchar(10) not null default '',
`stu_id` int not null,
PRIMARY KEY(`id`),
FOREIGN KEY(`stu_id`) REFERENCES `student_age`(`id`)
);
INSERT INTO student_name(name, stu_id) VALUES('Jack', 1), ('Eric', 2), ('Alice', 3);
pymysql基本连接示例:
import pymysql
#建立管道
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='test')
#创建游标
cursor = conn.cursor()
effect_rows = cursor.execute('SELECT * FROM student_name')
print('rows[%d]' % effect_rows, cursor.fetchall())
#关闭连接
conn.close()
pymysql插入数据和事物的效果一样,可以实现回滚,自增id占用,必须提交才会生效:
import pymysql, time
#设置数据库连接参数
host = 'localhost'
port = 3306
user = 'root'
passwd = '123456'
db = 'test'
#创建通道
conn = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db)
#创建游标
cursor = conn.cursor()
#写sql语句
sql = "INSERT INTO student_age(id, age, sex) VALUES(4, 21, 'F')"
#执行sql语句
effect_row = cursor.execute(sql)
#打印影响行
print(effect_row)
time.sleep(30)
#提交数据
conn.commit()
#关闭通道
conn.close()
orm操作数据库新建一张表:
'''
对象关系映射(英语:(Object Relational Mapping,简称ORM,或O/RM,或O/R mapping),是一种程序技术,用于实现面向对象编程语言里不同类型系统的数据之间的转换 。从效果上说,它其实是创建了一个可在编程语言里使用的--“虚拟对象数据库”。
'''
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
engine = create_engine("mysql+pymysql://root:123456@localhost/test", encoding="utf-8", echo=True)
#生成orm基类
Base = declarative_base()
class User(Base):
#表名
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String(32))
password = Column(String(64))
#创建表结构
Base.metadata.create_all(engine)
新增数据:
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
#创建工程
engine = create_engine("mysql+pymysql://root:123456@localhost/test", encoding='utf-8', echo=True)
#创建基本处理类
Base = declarative_base()
#