sqlalchemy mysql+pymysql_python中的sql(pymysql和sqlalchemy)

本文介绍了如何在Python中使用pymysql和sqlalchemy进行数据库操作。首先,展示了pymysql的基本用法,包括连接数据库、执行SQL(增删改查)及关闭连接。然后,详细解释了sqlalchemy的使用,包括创建引擎、定义数据模型、进行增删改查操作。示例中,创建了Student类并演示了查询和添加记录的方法。

摘要生成于 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

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()

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值