二，MySQL与Python交互
首先要安装第三方库pymysql，可以直接在pycharm中搜索安装，也可以在window的dos窗口中输入pip install pymysql进行安装，如果觉得比较慢，还可以加上国内镜像源，速度是嗖嗖的🚀…
用python连接数据库
import pymysql
# 创建连接
conn = pymysql.connect(
host="localhost",
port=3306,
user="root",
passwd="201314",
db="hepengli",
)
# 创建游标
cursor = conn.cursor()
# 执行SQL，并返回影响行数
effect_row = cursor.execute("select * from student")
print(effect_row)
G:\Python38\python.exe G:/Project1/self_taught/week_12/mysql_1.py
4
Process finished with exit code 0
显示hepengli数据库中的student表中有四条数据
查看数据
只需在刚才代码下面加上
print(cursor.fetchone()) # 获取一条
print(cursor.fetchone())
print(cursor.fetchone())
G:\Python38\python.exe G:/Project1/self_taught/week_12/mysql_1.py
4
(1, 'hepengli', '2021-1-3')
(2, 'laowang', '2021-1-2')
(3, 'laoxu', '2021-1-1')
Process finished with exit code 0
这个感觉有点不太方便，假如要看全部数据呢？
import pymysql
# 创建连接
conn = pymysql.connect(
host="localhost",
port=3306,
user="root",
passwd="201314",
db="hepengli",
)
# 创建游标
cursor = conn.cursor()
# 执行SQL，并返回影响行数
effect_row = cursor.execute("select * from student")
print(effect_row)
# print(cursor.fetchone()) # 获取一条
# print(cursor.fetchone())
# print(cursor.fetchone())
print(cursor.fetchall()) # 获取全部
G:\Python38\python.exe G:/Project1/self_taught/week_12/mysql_1.py
4
((1, 'hepengli', '2021-1-3'), (2, 'laowang', '2021-1-2'), (3, 'laoxu', '2021-1-1'), (4, 'liusang', '2021-1-4'))
Process finished with exit code 0
插入数据
import pymysql
# 创建连接
conn = pymysql.connect(
host="localhost",
port=3306,
user="root",
passwd="201314",
db="hepengli",
)
# 创建游标
cursor = conn.cursor()
# 执行SQL，并返回影响行数
effect_row = cursor.execute("select * from student")
# print(effect_row)
# print(cursor.fetchone()) # 获取一条
# print(cursor.fetchone())
# print(cursor.fetchone())
# print(cursor.fetchall()) # 获取全部
data = [
(5, "wangba", "2020-03-06"),
(6, "haha", "2020-05-20"),
]
cursor.executemany("insert into student (id,name,sdate) values(%s, %s, %s)", data)
conn.commit() # 提交
再去MySQL中查询
mysql> select * from student;
+----+----------+------------+
| id | name | sdate |
+----+----------+------------+
| 1 | hepengli | 2021-1-3 |
| 2 | laowang | 2021-1-2 |
| 3 | laoxu | 2021-1-1 |
| 4 | liusang | 2021-1-4 |
| 5 | wangba | 2020-03-06 |
| 6 | haha | 2020-05-20 |
+----+----------+------------+
6 rows in set (0.00 sec)
可以看到已被插入进去
三，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:201314@localhost/hepengli",
encoding="utf-8") # 先去掉最后的, echo=True
Base = declarative_base() # 生成orm基类
class User(Base):
__tablename__ = "user1" # 表名
id = Column(Integer, primary_key=True)
name = Column(String(32))
password = Column(String(64))
Base.metadata.create_all(engine) # 创建表结构
然后去数据库查看一下
mysql> show tables;
+--------------------+
| Tables_in_hepengli |
+--------------------+
| address |
| authors |
| book_m2m_author |
| books |
| customer |
| student |
| study |
| user1 |
+--------------------+
8 rows in set (0.23 sec)
mysql> desc user1;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
| password | varchar(64) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.35 sec)
表已创建完成
插入数据
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:201314@localhost/hepengli",
encoding="utf-8") # 先去掉最后的, echo=True
Base = declarative_base() # 生成orm基类
class User(Base):
__tablename__ = "user1" # 表名
id = Column(Integer, primary_key=True)
name = Column(String(32))
password = Column(String(64))
Base.metadata.create_all(engine) # 创建表结构
Session_class = sessionmaker(bind=engine) # 创建与数据库会话的session class
Session = Session_class() # 生成session实例，相当于cursor
user1 = User(name="hepengli", password="123456")
user2 = User(name="liujie", password="012345")
# print(user1, user2)
Session.add(user1) # 把要创建的数据对象添加到这个session里，一会同意创建
Session.add(user2)
# print(user1.name, user1.password) # 此时依然没有创建
# print(user2.name, user2.password)
Session.commit() # 现在才统一提交
mysql> select * from user1;
+----+----------+----------+
| id | name | password |
+----+----------+----------+
| 1 | hepengli | 123456 |
| 2 | liujie | 012345 |
+----+----------+----------+
2 rows in set (0.00 sec)
数据已插入
查询
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:201314@localhost/hepengli",
encoding="utf-8") # 先去掉最后的, echo=True
Base = declarative_base() # 生成orm基类
class User(Base):
__tablename__ = "user1" # 表名
id = Column(Integer, primary_key=True)
name = Column(String(32))
password = Column(String(64))
def __repr__(self):
return "%s %s" % (self.name, self.password)
Base.metadata.create_all(engine) # 创建表结构
Session_class = sessionmaker(bind=engine) # 创建与数据库会话的session class
Session = Session_class() # 生成session实例，相当于cursor
data = Session.query(User).filter().all()
print(data)
G:\Python38\python.exe G:/Project1/self_taught/week_12/orm_us2.py
[hepengli 123456, liujie 012345]
Process finished with exit code 0
查询user1表中id大于1的信息，只需更改一行代码
data = Session.query(User).filter(User.id > 1).all()
G:\Python38\python.exe G:/Project1/self_taught/week_12/orm_us2.py
[liujie 012345]
Process finished with exit code 0
再往user1表中插入两条数据
mysql> select * from user1;
+----+----------+----------+
| id | name | password |
+----+----------+----------+
| 1 | hepengli | 123456 |
| 2 | liujie | 012345 |
| 3 | xiaowang | 111111 |
| 4 | xiaoming | 222222 |
+----+----------+----------+
4 rows in set (0.00 sec)
查询user1表中id大于1的，小于4的
data = Session.query(User).filter(User.id > 1).filter(User.id < 4).all()
G:\Python38\python.exe G:/Project1/self_taught/week_12/orm_us2.py
[liujie 012345, xiaowang 111111]
Process finished with exit code 0
更改数据
比如说，要把id为1的name改为liubangguo，password改为789456
data = Session.query(User).filter(User.id == 1).first()
data.name = "liubangguo"
data.password = "789456"
Session.commit()
运行成功，再去数据库查看
mysql> select * from user1;
+----+------------+----------+
| id | name | password |
+----+------------+----------+
| 1 | liubangguo | 789456 |
| 2 | liujie | 012345 |
| 3 | xiaowang | 111111 |
| 4 | xiaoming | 222222 |
+----+------------+----------+
4 rows in set (0.00 sec)
可以看到id为1的数据已被更改
外键关联
先创建两个表，一张student(学生表)，study(学习表)，并且有外键关联
# 外键关联
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
engine = create_engine("mysql+pymysql://root:201314@localhost/hepengli",
encoding="utf-8") # 先去掉最后的, echo=True
Base = declarative_base() # 生成orm基类
class Student(Base):
__tablename__ = "student" # 表名
id = Column(Integer, primary_key=True)
name = Column(String(32))
sdate = Column(String(64))
class Study(Base):
__tablename__ = "study"
id = Column(Integer, primary_key=True)
day = Column(Integer)
status = Column(String(32))
stu_id = Column(Integer, ForeignKey("student.id"))
student = relationship("Student", backref="my_Study")
Base.metadata.create_all(engine)
session_class = sessionmaker(bind=engine)
session = session_class()
student_1 = Student(name="hepengli", sdate="2021-1-3")
student_2 = Student(name="laowang", sdate="2021-1-2")
student_3 = Student(name="laoxu", sdate="2021-1-1")
student_4 = Student(name="liusang", sdate="2021-1-4")
study_1 = Study(day=1, status="yes", stu_id=1)
study_2 = Study(day=2, status="no", stu_id=1)
study_3 = Study(day=3, status="yes", stu_id=3)
study_4 = Study(day=4, status="yes", stu_id=2)
session.add_all([student_1, student_2, student_3, student_4,
study_1, study_2, study_3, study_4])
session.commit()
如何通过student表知道hepengli一共上了几节课
# 外键关联
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
engine = create_engine("mysql+pymysql://root:201314@localhost/hepengli",
encoding="utf-8") # 先去掉最后的, echo=True
Base = declarative_base() # 生成orm基类
class Student(Base):
__tablename__ = "student" # 表名
id = Column(Integer, primary_key=True)
name = Column(String(32))
sdate = Column(String(64))
def __repr__(self):
return "%s %s" % (self.name, self.sdate)
class Study(Base):
__tablename__ = "study"
id = Column(Integer, primary_key=True)
day = Column(Integer)
status = Column(String(32))
stu_id = Column(Integer, ForeignKey("student.id"))
student = relationship("Student", backref="my_Study")
def __repr__(self):
return "%s day:%s status:%s stu_id:%s" % (self.student.name, self.day, self.status, self.stu_id)
Base.metadata.create_all(engine)
session_class = sessionmaker(bind=engine)
session = session_class()
stu_obj = session.query(Student).filter(Student.name == "hepengli").first()
print(stu_obj.my_Study)
session.commit()
G:\Python38\python.exe G:/Project1/self_taught/week_12/orm.us3.py
[hepengli day:1 status:yes stu_id:1, hepengli day:2 status:no stu_id:1]
Process finished with exit code 0
多外键关联
创建两张表，一张Customer(客户表)，一张Address(地址表)，且有多外建关联
orm_us4.py
# 多外键关联
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
engine = create_engine("mysql+pymysql://root:201314@localhost/hepengli",
encoding="utf-8") # 先去掉最后的, echo=True
Base = declarative_base() # 生成orm基类
class Customer(Base):
__tablename__ = "customer"
id = Column(Integer, primary_key=True)
name = Column(String(64))
billing_address_id = Column(Integer, ForeignKey("address.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])
class Address(Base):
__tablename__ = "address"
id = Column(Integer, primary_key=True)
street = Column(String(64))
city = Column(String(64))
state = Column(String(64))
def __repr__(self):
return self.street
Base.metadata.create_all(engine) # 创建表结构
然后去另外一个文件做增删改查等操作(表结构创建好后，就把创建表结构那句代码注释了)
orm_us4_create.py
先插入数据
from self_taught.week_12 import orm_us4
from sqlalchemy.orm import sessionmaker
session_class = sessionmaker(bind=orm_us4.engine)
session = session_class()
addr1 = orm_us4.Address(street="yantaqu", city="xi'an", state="shanxi")
addr2 = orm_us4.Address(street="weiyangqu", city="xi'an", state="shanxi")
addr3 = orm_us4.Address(street="beilinqu", city="xi'an", state="shanxi")
addr4 = orm_us4.Address(street="baqiaoqu", city="xi'an", state="shanxi")
session.add_all([addr1, addr2, addr3, addr4])
c1 = orm_us4.Customer(name="hepengli", billing_address=addr1, shipping_address=addr4)
c2 = orm_us4.Customer(name="laowang", billing_address=addr2, shipping_address=addr2)
c3 = orm_us4.Customer(name="laoxu", billing_address=addr4, shipping_address=addr3)
c4 = orm_us4.Customer(name="liusang", billing_address=addr3, shipping_address=addr3)
session.add_all([c1, c2, c3, c4])
session.commit()
进行信息查询
from self_taught.week_12 import orm_us4
from sqlalchemy.orm import sessionmaker
session_class = sessionmaker(bind=orm_us4.engine)
session = session_class()
obj = session.query(orm_us4.Customer).filter(orm_us4.Customer.name == "hepengli").first()
print(obj.name, obj.billing_address, obj.shipping_address)
session.commit()
G:\Python38\python.exe G:/Project1/self_taught/week_12/orm_us4_create.py
hepengli yantaqu baqiaoqu
Process finished with exit code 0
多对多关联
创建三个表，books(书表)，author(作者表)，book_m2m_author
orm_us5.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
engine = create_engine("mysql+pymysql://root:201314@localhost/hepengli",
encoding="utf-8")
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
Base.metadata.create_all(engine) # 创建表结构
(和上面的一样)
orm_us5_create.py
先插入数据
from self_taught.week_12 import orm_us5
from sqlalchemy.orm import sessionmaker
session_class = sessionmaker(bind=orm_us5.engine)
session = session_class()
book1 = orm_us5.Book(name="python", pub_date="2019-8-14")
book2 = orm_us5.Book(name="java", pub_date="2016-5-24")
book3 = orm_us5.Book(name="linux", pub_date="2007-10-9")
book4 = orm_us5.Book(name="unix", pub_date="2018-11-13")
auth1 = orm_us5.Author(name="hepengli")
auth2 = orm_us5.Author(name="liujie")
auth3 = orm_us5.Author(name="liubangguo")
auth4 = orm_us5.Author(name="laowang")
book1.authors = [auth1, auth2]
book3.authors = [auth4, auth1]
session.add_all([book1, book2, book3, book4, auth1, auth2, auth3, auth4])
session.commit()
查询
from self_taught.week_12 import orm_us5
from sqlalchemy.orm import sessionmaker
session_class = sessionmaker(bind=orm_us5.engine)
session = session_class()
author_obj = session.query(orm_us5.Author).filter(orm_us5.Author.name == "hepengli").first()
print(author_obj.books[1].pub_date)
session.commit()
G:\Python38\python.exe G:/Project1/self_taught/week_12/orm_us5_create.py
2019-08-14
Process finished with exit code 0
from self_taught.week_12 import orm_us5
from sqlalchemy.orm import sessionmaker
session_class = sessionmaker(bind=orm_us5.engine)
session = session_class()
book_obj = session.query(orm_us5.Book).filter(orm_us5.Book.id == 2).first()
print(book_obj.authors)
session.commit()
G:\Python38\python.exe G:/Project1/self_taught/week_12/orm_us5_create.py
[laowang, hepengli]
Process finished with exit code 0
好了，这周的学习内容就到此为止，望各位看客大佬发现有不足或错误能留言相告，臣不胜感激！！！
链接: 满足低调之心基础十二(1).
扫一扫
208
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
