sqlalchemy mysql+pymysql_python中的sql（pymysql和sqlalchemy）

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