Mysql操作(增删改)
class UserInfo(models.Model):
"""
用户表
"""
name = models.CharField(max_length=32, unique=True, error_messages={'unique': '用户已经存在'}, verbose_name="用户名")
password = models.CharField(max_length=128, verbose_name="密码")
token = model.CharField(max_length=64, verbose_name="tokon")
class Meta:
db_table = 'user'
计时装饰器
import time
def timewrapper(fn):
def inner(*args, **kwargs):
start_time = time.time()
ret = fn(*args, **kwargs)
print('time cost:', time.time() - start_time)
return ret
return inner
方法1：
ORM：
增加一条数据：
UserInfo.objects.create(name=‘admin’, password=‘123456’, token='123456).save() cost time: 0.02--0.03
或
user = UserInfo()
setattr(user, 'name', 'admin')
更新一条数据
UserInfo.objects.filter(name='admin').update(password='666666') cost time: 0.01--0.25
添加或更新
如果存在 obj = UserInfo.objects.get(name=‘admin’, password=‘123456’) 则更新token的值；不存在则新建
UserInfo.objects.update_create(name=‘admin’, password=‘123456’, defaults={'token': '123456'}) cost time: 0.01--0.25
等价与下面
defaults = {'first_name': 'Bob'}
try:
obj = Person.objects.get(first_name='John', last_name='Lennon')
for key, value in defaults.items():
setattr(obj, key, value)
obj.save()
except Person.DoesNotExist:
new_values = {'first_name': 'John', 'last_name': 'Lennon'}
new_values.update(defaults)
obj = Person(**new_values)
obj.save()
删除
UserInfo.objects.filter(id=11).delete() cost time:0.02--0.04
方法2：
SQL:
index --- 是MySQL的关键字，必须 `index`
import pymysql
@timewrapper
def create_role(request):
ret = {}
# 与数据库建立连接
conn = pymysql.connect(user='root', password='123456', db='my_test') cost time: 0.01--0.02
# 获取游标
cursor = conn.cursor()
sql = 'insert into user(name, remark) values ("123", "sdfasf")'
try:
# 执行sql语句
cursor.execute(sql)
# 事务
conn.commit()
except:
# 发生错误回滚
conn.rollback()
finally:
# 关闭游标
cursor.close()
# 关闭数据库
conn.close()
ret['code'] = 0
return JsonResponse(ret)
创建表
sql = """CREATE TABLE user (
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT )"
插入数据
sql = "INSERT INTO user(FIRST_NAME, \
LAST_NAME, AGE, SEX, INCOME) \
VALUES (\'%s\', \'%s\', \'%s\', \'%s\', \'%s\' )" % \
('Mac', 'Mohan', 20, 'M', 2000)
更新数据
sql = "UPDATE user SET sex=\'%s\' WHERE id=%s" % ('1', 2)
删除数据
sql = 'delete from user where name=\'%s\' ' % '6666'
方法3：
Pandas模块
需要安装
pip install mysql-connector-python pip install sqlalchemy pip install pandas
添加一条数据
from sqlalchemy import create_engine import pandas as pd def create_role(request):
# 创建引擎 engine = create_engine('mysql+mysqlconnector://root:123456@localhost:3306/yangji') cost time: 0.14 --0.15
# 连接数据库 connect = engine.connect()
# 读取数据库的表信息 df = pd.read_sql('select name, remark from test_fun', connect)
# 添加的数据 insert_df = pd.DataFrame({'name': ['6666',], 'remark': ['6666',]})
# 合并 重新把 id 排序 create_df = pd.concat([df, insert_df]).reset_index(drop=True)
create_df = pa.concat([df, insert_df], ignore_index=True)
# 将合并后的 df 全部替换表的数据 create_df.to_sql('test_fun', con=connect, if_exists='replace', index_label='id')
# 关闭连接
connect.close()
更新数据
df = pd.read_sql('select name, remark from test_fun', connect) cost time: 0.3--0.4
# df.loc[index, columns] 行索引，列索引 df.loc[1, 'name'] = '8888' df.to_sql('test_fun', con=connect, if_exists='replace', index_label='id') connect.close()
删除
df = pd.read_sql('select name, remark from test_fun', connect) cost time: 0.3--0.4
# 行索引 df.drop(index=1, inplace=True) df.to_sql('test_fun', con=connect, if_exists='replace', index_label='id') connect.close()
需求：在表中间（插入、删除、更新）一条数据，并重置排列顺序
注意： index 为 Mysql 的关键字，命名需注意，不可避免则 ---> `index` 加转义符
index max_time standard_time slot
1 60 20 1,2
2 100 70 5
3 60 30 8
4 60 10 10
from sqlalchemy import create_engine, Integer, NVARCHAR import pandas as pd def create_role(request): cost tiem: 0.1
engine = create_engine('mysql+mysqlconnector://root:123456@localhost:3306/yangji')
# 连接数据库 connect = engine.connect()
# 获取表所有数据并且转成 dataframe df = pd.read_sql('select * from formula_ef86ffdd89978ef49998d7cc07865e18', connect)
# 出入一条数据 ---df insert_df = pd.DataFrame({'index': [4,], 'max_time': [44,], 'slot': '44', 'standard_time': [4,]})
# 合并 new_df = pd.concat([df, insert_df])
# 按照 某一列排序 new_df.sort_values(by='index', inplace=True)
# 重置序号 new_df['index'] = range(1, len(new_df) + 1)
# 定制字段类型 dtypedict = { 'index': Integer(), 'standard_time': Integer(), 'max_time': Integer(), 'slot': NVARCHAR(length=50), }
# 替换原来表的数据 new_df.to_sql('formula_ef86ffdd89978ef49998d7cc07865e18', con=connect, if_exists='replace', dtype=dtypedict, index=False)
# 关闭数据库连接 connect.close() ret['code'] = 0 return JsonResponse(ret)
删除
def create_role(request): engine = create_engine('mysql+mysqlconnector://root:123456@localhost:3306/yangji') connect = engine.connect() df = pd.read_sql('select * from formula_ef86ffdd89978ef49998d7cc07865e18', connect) df.drop(index=4, inplace=True) df['index'] = range(1, len(df) + 1) dtypedict = { 'index': Integer(), 'standard_time': Integer(), 'max_time': Integer(), 'slot': NVARCHAR(length=50), } df.to_sql('formula_ef86ffdd89978ef49998d7cc07865e18', con=connect, if_exists='replace', dtype=dtypedict, index=False) connect.close()
更新
def create_role(request): conn = pymysql.connect(user='root', password='123456', db='yangji') cost time: 0.02 cursor = conn.cursor() sql = 'update formula_ef86ffdd89978ef49998d7cc07865e18 set max_time=100, standard_time=20, slot="20" where `index`=4' try: cursor.execute(sql) conn.commit() except: conn.rollback() finally: cursor.close()
