import datetime
|
import logging
|
|
import pymysql
|
import sqlite3
|
|
config = {
|
"host": "gz-cdb-r13d0yi9.sql.tencentcdb.com",
|
"port": 62929,
|
"database": "everyday_words",
|
"charset": "utf8",
|
"user": "root",
|
"passwd": "Yeshi2016@"
|
}
|
|
|
class Mysqldb:
|
# 初始化方法
|
def __init__(self):
|
# 初始化方法中调用连接数据库的方法
|
self.conn = self.get_conn()
|
# 调用获取游标的方法
|
self.cursor = self.get_cursor()
|
|
def close(self):
|
self.conn.close()
|
|
# 连接数据库的方法
|
def get_conn(self):
|
# **config代表不定长参数
|
conn = pymysql.connect(**config)
|
return conn
|
|
# 获取游标
|
def get_cursor(self):
|
cursor = self.conn.cursor()
|
return cursor
|
|
# 查询sql语句返回的所有数据
|
def select_all(self, sql):
|
self.cursor.execute(sql)
|
return self.cursor.fetchall()
|
|
# 查询sql语句返回的一条数据
|
def select_one(self, sql):
|
self.cursor.execute(sql)
|
return self.cursor.fetchone()
|
|
# 查询sql语句返回的几条数据
|
def select_many(self, sql, num):
|
self.cursor.execute(sql)
|
return self.cursor.fetchmany(num)
|
|
# 增删改除了SQL语句不一样其他都是一样的，都需要提交
|
def execute(self, sql, args=None):
|
try:
|
# 执行语句
|
self.cursor.execute(sql, args)
|
# 提交
|
self.conn.commit()
|
except Exception as e:
|
print("提交出错\n:", e)
|
logging.exception(e)
|
# 如果出错要回滚
|
self.conn.rollback()
|
|
def execute_many(self, sql, args=None):
|
try:
|
# 执行语句
|
self.cursor.executemany(sql, args)
|
# 提交
|
self.conn.commit()
|
except Exception as e:
|
logging.exception(e)
|
print("提交出错\n:", e)
|
# 如果出错要回滚
|
self.conn.rollback()
|
|
# 当对象被销毁时，游标要关闭,连接也要关闭
|
# 创建时是先创建连接后创建游标，关闭时是先关闭游标后关闭连接
|
def __del__(self):
|
self.cursor.close()
|
self.conn.close()
|
|
|
def save_to_sqlite(datas):
|
sqlite_conn = sqlite3.connect('D:\\项目\\单词\\words.db')
|
sqlite_cursor = sqlite_conn.cursor()
|
for data in datas:
|
sql = "insert into table_english_words values(?,?,?,?,?,?,?)"
|
print(sql)
|
sqlite_cursor.execute(sql, (data[0],
|
data[1], data[2], data[3], data[4].strftime("%Y-%m-%d %H:%M"), data[5].strftime("%Y-%m-%d %H:%M"), data[6]))
|
sqlite_conn.commit()
|
sqlite_conn.close()
|
|
|
if __name__ == "__main__":
|
for i in range(0, 50):
|
results = Mysqldb().select_all(f"select * from table_english_words limit {i*1000}, 1000")
|
save_to_sqlite(results)
|
