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)