python中的sql（pymysql和sqlalchemy）

最新推荐文章于 2025-04-22 10:08:14 发布

何须执手问年华丶

最新推荐文章于 2025-04-22 10:08:14 发布

阅读量681

点赞数

本文链接：https://blog.youkuaiyun.com/rainy_killer/article/details/89305534

版权

本文介绍了Python中两个常用的SQL库——pymysql和sqlalchemy。首先，详细阐述了pymysql的安装方法及增删改查的基础操作。接着，转向sqlalchemy，解析了其特有的SQL表达式语言，并讲解了使用sqlalchemy进行数据操作的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

pymysql

一、初识pymysql
#两种方式安装此模块 pip install pymysql 或者通过pycharm进行安装

# 导入pymysql
import pymysql
#创建连接
conn = pymysql.connect(
    host="localhost",
    user="root",
    password="123",
    database="day05",
    port = 3306
)
print(conn)
#获取游标
cur = conn.cursor()
# 编写sql语句
sql="insert into account VALUES (10,'李明',15000);"
#执行sql
cur.execute(sql)
#提交事物
conn.commit()
#关闭连接
cur.close()
conn.close()

二、pymysql的增删改查

import pymysql
conn = pymysql.connect(
    host = "localhost",
    user = "root",
    password = "123",
    database = "day05",
    port = 3306

)
print(conn)

cur = conn.cursor()

id = int(input("请输入id："))
name = input("请输入姓名：")
money = int(input("请输入钱："))

# start transaction 不用写，pymysql默认开启事务
'''
#插入语句

# sql = "insert into account VALUES (%s,%s,%s);"
# sql = "insert into account VALUES (%(id)s,%(name)s,%(money)s);"
# 
# # ret = cur.execute(sql,(id,name,money))
# ret = cur.execute(sql,{"id":id, "name":name,"money":money})
# print(ret)
'''

'''
#修改操作
sql = "update account set money =%s where NAME = %s;"
ret = cur.execute(sql,(14000,"杨洋"))
print(ret)
'''
'''
#删除操作
sql = "delete from account where money <= %s;"
ret = cur.execute(sql,10000)
print(ret)
'''

#查询操作
sql = "select * from account where money<%s;"
ret = cur.execute(sql,15000)
#获取一条
# cur.fetchone()
#获取两条
# cur.fetchmoney(2)
#全部获取
c1 = cur.fetchall()
print(c1)
print(ret)


conn.commit()

cur.close()
conn.close()

sqlalchemy

一、sqlalchemy格式

  from sqlalchemy import create_engine
    from sqlalchemy import Column,Integer,String
    from sqlalchemy.ext.declarative import declarative_base
    
    #1.创建引擎
    eng = create_engine("mysql+pymysql://root:123@localhost/day05?charset=utf8")
    print(eng)
    #2.创建基类
    Base = declarative_base()
    
    #3.创建类
    class Student(Base):
        __tablename__ = "children" #指定表格名称
        id = Column(Integer,primary_key=True,autoincrement=True)
        name = Column(String(32),nullable=False)
        email = Column(String(32),unique=True)
    
    #4.创建表格
    Base.metadata.create_all(eng)
    
    #5.删除表格
    
    Base.metadata.drop_all()

二、sqlalchemy中的增删改查

#sqlalchemy 和pymysql要配合使用
from sqlalchemy import create_engine
from sqlalchemy import Column,Integer,String
from sqlalchemy.ext.declarative import declarative_base

#1.创建引擎
eng = create_engine("mysql+pymysql://root:123@localhost/day05?charset=utf8")
print(eng)
#2.创建基类
Base = declarative_base()

#3.创建类
class Student(Base):
    __tablename__ = "children" #指定表格名称
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(32),nullable=False)
    email = Column(String(32),unique=True)

#4.创建表格
# Base.metadata.create_all(eng)

#5.添加记录
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=eng)
session = Session()

# student = Student(name = "刘备",email = "110@sq.com")# 创建student对象
# session.add(student) # 添加记录


# session.add_all([
#     Student(name='刘小争',email='120@sq.com'),
#     Student(name='刘大争',email='130@sq.com'),
#     Student(name='小小争',email='150@sq.com')
# ])#批量增加


#查询操作

student = session.query(Student).first()
print(student.id,student.name,student.email)
student2 = session.query(Student).get(ident=4)#使用唯一标示
print(student2.id,student2.name,student2.email)


session.commit()#提交事务
session.close()