前言
应用场景
-
爬虫数据写入数据库
优点
-
简洁、通用、直接复制可用
先修基础!
-
1、
Python 任意字典生成 insert 语句
2、
Python 任意字典生成 update 语句
3、
Python 连接 MySQL 基础
INSERT
import pymysql
class Mysql:
def __init__(self, db_name, tb_name):
self.db = pymysql.connect(
host='localhost', user='root', password='yellow', charset='utf8', db=db_name)
self.cursor = self.db.cursor()
self.tb = tb_name
def __del__(self):
self.cursor.close()
self.db.close()
def insert(self, dt):
ls = [(k, dt[k]) for k in dt if dt[k] is not None]
sql = 'insert %s (' % self.tb + ','.join(i[0] for i in ls) +\
') values (' + ','.join('%r' % i[1] for i in ls) + ')'
self.cursor.execute(sql)
self.db.commit()
UPDATE
import pymysql
class Mysql:
def __init__(self, db_name):
self.db = pymysql.connect(
host='localhost', user='root', password='yellow', charset='utf8', db=db_name)
self.cursor = self.db.cursor()
def __del__(self):
self.cursor.close()
self.db.close()
def commit(self, sql):
try:
self.cursor.execute(sql)
self.db.commit()
except Exception as error:
print('\033[031m', error, '\033[0m', sep='')
def update(self, dt, dt_condition, table):
sql = 'UPDATE %s SET ' % table + ','.join('%s=%r' % (k, dt[k]) for k in dt)\
+ ' WHERE ' + ' AND '.join('%s=%r' % (k, dt_condition[k]) for k in dt_condition)
self.commit(sql)
无则插入,有则插入
from pymysql.connections import Connection
class Mysql:
def __init__(self, password, database, host='localhost', user='root'):
self.db = Connection(
user=user,
password=password,
host=host,
database=database,
port=3306,
charset='UTF8')
self.cursor = self.db.cursor()
def __del__(self):
self.cursor.close()
self.db.close()
def commit(self, sql):
print(sql)
try:
self.cursor.execute(sql)
self.db.commit()
except Exception as e:
print(e)
def fetchall(self, query):
self.cursor.execute(query)
return self.cursor.fetchall()
def insert(self, dt, tb):
ls = [(k, v) for k, v in dt.items() if v is not None]
sql = 'INSERT %s (' % tb + ','.join(i[0] for i in ls) + \
') VALUES (' + ','.join('%r' % i[1] for i in ls) + ')'
self.commit(sql)
def update(self, dt, dt_condition, tb):
sql = 'UPDATE %s SET ' % tb + ','.join('%s=%r' % (k, v) for k, v in dt.items()) \
+ ' WHERE ' + ' AND '.join('%s=%r' % (k, v) for k, v in dt_condition.items())
self.commit(sql)
def insert_or_update(self, dt, dt_condition, tb):
"""有则更新,无则插入"""
query = 'SELECT * FROM ' + tb + ' WHERE ' + \
' AND '.join('%s=%r' % (k, v) for k, v in dt_condition.items())
if self.fetchall(query) == ():
dt.update(dt_condition)
self.insert(dt, tb)
else:
self.update(dt, dt_condition, tb)