lr 操作mysql 增删改_Mysql操作(增删改)

classUserInfo(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")classMeta:

db_table= 'user'

计时装饰器

importtimedeftimewrapper(fn):def inner(*args, **kwargs):

start_time=time.time()

ret= fn(*args, **kwargs)print('time cost:', time.time() -start_time)returnretreturn 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

defcreate_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'] =0return 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 = "UPDATEuser 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 importcreate_engineimportpandas as pddefcreate_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 importcreate_engine, Integer, NVARCHARimportpandas as pddefcreate_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'] =0return JsonResponse(ret)

删除

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

更新

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