以下是常用的一些pymysql操作数据库的操作。为了打包后方便读取与写入不同数据库,留出了更改数据库的接口。接口文件类似于:
localhostroot
xxxxxx test
3306
utf8
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
import pymysql import time # 读取外部配置 with open('database_config_test.txt', 'r') as f: item = f.readlines() with open('chart_config_write.txt', 'r') as c: chart_name = c.readline() # 连接数据库 conn = pymysql.connect(host=item[0].strip('\n'), user=item[1].strip('\n'), passwd=item[2].strip('\n'), db=item[3].strip('\n'), port=int(item[4].strip('\n')), charset=item[5].strip('\n')) curser = conn.cursor(cursor=pymysql.cursors.DictCursor) # 创建表 sql = 'create table if not exists %s(ID bigint primary key auto_increment, \ model varchar(255), \ order_number varchar(255), \ barcode varchar(255) unique, \ case_number varchar(255), \ time varchar(255));' % chart_name curser.execute(sql) """ # 插入新列 sql_1 = 'alter table %s add order_number varchar(255) after model'%chart_name curser.execute(sql_1) """ """ # 写入数据 sql = 'insert into {} (model, order_number, barcode, case_number, time) value(%s, %s, %s, %s, %s)'.format(chart_name) now = time.strftime("%Y/%m/%d/%H:%M:%S") item = ('JK6602', 'MO23042301', '233679013010175P4W00189', 2, now) curser.execute(sql, item) """ # 更新数据库中某个数据 now = time.strftime("%Y/%m/%d/%H:%M:%S") sql = "update %s set barcode = '233679013011066P5B00001', time = '%s' where barcode = '23367901301106600001'" % (chart_name, now) curser.execute(sql) """ # 删除数据库中一条数据 sql = 'delete from %s where ID = 6'%chart_name curser.execute(sql) """ conn.commit() # 没有此语句将无法保存数据库的修改 curser.close() cur = conn.cursor(cursor=pymysql.cursors.DictCursor) # cursor=pymysql.cursors.DictCursor sql = 'select barcode from %s order by ID desc limit 10;' % chart_name cur.execute(sql) row = cur.fetchall() for i in row: print(i['barcode']) cur.close() conn.close()View Code
标签:name,curser,数据库,pymsql,chart,item,sql,操作 From: https://www.cnblogs.com/abwblog/p/17414836.html