義龘
2022-08-12 10:19
采纳率: 100%
浏览 53

不会修改代码,使得代码可以批量将csv文件插入数据库中去

问题遇到的现象和发生背景 师兄让我给了我一段代码,让我能够实现将文件批量输入到数据库中,现在代码不报错,也不知道怎么修改,数据也进不去数据库,不知道怎么办
问题相关代码,请勿粘贴截图

class ConnectMysql:
def init(self, opt):
self.opt = opt
self.server = SSHTunnelForwarder(
ssh_address_or_host=(self.opt.server_ip, self.opt.server_port),
ssh_username=self.opt.server_username,
ssh_password=self.opt.server_password,
remote_bind_address=(self.opt.mysql_ip,self.opt.mysql_port)
)
self.server.start()
self.connect = pymysql.connect(
host=self.opt.mysql_ip,
port=self.opt.mysql_port,
user=self.opt.mysql_username,
passwd=self.opt.mysql_password,
db=self.opt.databaseName,
charset='utf8'
)
self.cursor = self.connect.cursor()

    print("cursor",self.cursor)
    print("connect",self.connect)


# def __del__(self):
#     #self.cursor.close()
#     #self.connect.close()
#     #self.server.close()
#     #print('close success!')

def read_csv_columns(self,filepath):
    csv_name = filepath
    data = pd.read_csv(csv_name, encoding='gb2312')
    # 这里不变
    columns = data.columns.tolist()
    return columns

def read_csv_values(self,filepath)
    csv_name=filepath
    data = pd.read_csv(csv_name, encoding='gb2312')
    #这里不需要变化
    print('the shape of data we get.',data.shape)
    data.replace([np.inf, -np.inf], np.nan, inplace=True)
    data.dropna(inplace=True)
    print(data.shape)
    data = pd.DataFrame(data)
    #data_3 = list(data.values)

    return data

def write_mysql(self):

    data = self.read_csv_values()
    for i in data:
        data_6 = tuple(i)
        sql = 'insert into {} values{}'.format(self.opt.tableName, data_6)
        #print(sql)
        self.cursor.execute(sql)
        self.commit()
    print("\nComplete write data operation!")

def commit(self):
    # 定义一个确认事务运行
    self.connect.commit()

def create(self):
    columns = self.read_csv_columns()
    'CREATE TABLE {}({})'.format(self.opt.tableName, columns)
    #print(text)
    self.cursor.execute('CREATE TABLE {}({})'.format(self.opt.tableName, columns))
    self.commit()

def exists(self):
    sql = "SHOW TABLES LIKE '{}' ".format(self.opt.tableName)
    self.cursor.execute(sql)
    result = self.cursor.fetchall()
    return len(result) != 0

def start_write_csv(self):
    '''
    yang write
    :return:none
    '''

    if ~self.exists():
        self.create()
    self.write_mysql()

def read_data_from_mysql(self,time):
    '''
    time: the number of data we get
    get data from mysql
    :return:
    '''

    sql = 'SELECT * FROM {}'.format(self.opt.tableName)
    self.cursor.execute(sql)
    data_dict = []
    for field in self.cursor.description:
        data_dict.append(field[0])
    data = self.cursor.fetchall()
    df = pd.DataFrame(list(data))
    df.columns = data_dict

    return df.head(time)
运行结果及报错内容

不知道怎么该能成功

我的解答思路和尝试过的方法

师兄说添加insert语句就行了,但我发现代码里已经有了,不知道怎么改代码零零

我想要达到的结果

能批量将文件输入到数据库中去

3条回答 默认 最新

相关推荐 更多相似问题