python对excel数据增删改查_Python操作Mysql——使用python批量对数据进行增删改查...

如果对于sqlalchemy框架不会使用，可以看——Python操作MySql——使用SQLAlchemy ORM操作数据库

1 使用sqlalchemy 框架，连接数据库

from random import random

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String

from sqlalchemy.orm import sessionmaker

from sqlalchemy.pool import NullPool

#创建连接对象也就是为了连接到本地的数据库

engine = create_engine('mysql+pymysql://root:root@localhost:3306/testdab',

encoding='utf-8', # 编码格式

echo=True, # 是否开启sql执行语句的日志输出

pool_recycle=-1, # 多久之后对线程池中的线程进行一次连接的回收(重置) (默认为-1),其实session并不会被close

poolclass=NullPool # 无限制连接数

)

#声名Base

Base = declarative_base()

# 创建会话

session = sessionmaker(engine)

mySession = session()

2 创建类，与连接的数据库的字段相互映射

class User(Base):

__tablename__ = 'grade'

id = Column(Integer, primary_key=True, autoincrement=True)

name = Column(String(64), nullable=False)

Cgrade = Column(Integer, nullable=False)

Mgrade = Column(Integer, nullable=False)

Egrade = Column(Integer, nullable=False)

__table_args__ = {

"mysql_charset": "utf8"

}

3 批量插入数据100条

for i in range(1,100):

user = User(id = i,name="小红",Cgrade = int(random()*100),Egrade = int(random()*100),Mgrade = int(random()*100))

mySession.add(user)

mySession.commit()

4 批量修改数据

for i in range(1,100):

result = mySession.query(User).filter(User.id == i).first()

Cgrade = result.Cgrade + 5

sql = "update grade set Cgrade = %s where id = %s"

engine.execute(sql,(Cgrade,i))

5 批量删除数据(根据id)

for i in range(1,100):

mySession.query(User).filter(User.id == i).delete()

mySession.commit()

6 批量查询数据

for i in range(1,50):

sql = "select name,Cgrade from grade where id = %s"

result = engine.execute(sql,i)

print(result.fetchall())

7 完整代码

from random import random

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String

from sqlalchemy.orm import sessionmaker

from sqlalchemy.pool import NullPool

#创建连接对象也就是为了连接到本地的数据库

engine = create_engine('mysql+pymysql://root:root@localhost:3306/testdab',

encoding='utf-8', # 编码格式

echo=True, # 是否开启sql执行语句的日志输出

pool_recycle=-1, # 多久之后对线程池中的线程进行一次连接的回收(重置) (默认为-1),其实session并不会被close

poolclass=NullPool # 无限制连接数

)

#声名Base

Base = declarative_base()

# 创建会话

session = sessionmaker(engine)

mySession = session()

# 创建类，继承基类，用基本类型描述数据库结构

class User(Base):

__tablename__ = 'grade'

id = Column(Integer, primary_key=True, autoincrement=True)

name = Column(String(64), nullable=False)

Cgrade = Column(Integer, nullable=False)

Mgrade = Column(Integer, nullable=False)

Egrade = Column(Integer, nullable=False)

__table_args__ = {

"mysql_charset": "utf8"

}

# 插入100条数据

#for i in range(1,100):

# user = User(id = i,name="小红",Cgrade = int(random()*100),Egrade = int(random()*100),Mgrade = int(random()*100))

# mySession.add(user)

#mySession.commit()

#批量更改数据

#for i in range(1,100):

# result = mySession.query(User).filter(User.id == i).first()

# Cgrade = result.Cgrade + 5

# sql = "update grade set Cgrade = %s where id = %s"

# engine.execute(sql,(Cgrade,i))

for i in range(1,50):

sql = "select name,Cgrade from grade where id = %s"

result = engine.execute(sql,i)

print(result.fetchall())

#根据id批量删除数据

#for i in range(1,100):

# mySession.query(User).filter(User.id == i).delete()

#mySession.commit()

#sql语句查询

#result = engine.execute("select * from grade")

#print(result.fetchall())