有个问题希望老师帮解答下： pymysql 是一个包名，但是如下的这条语句调用 connect 函数时，使用的是 “包名.方法名”，中间没有模块名，这个怎么理解？ # 连接数据库 connection = pymysql.connect(host='localhost', user='root', password='admin123', database='db', cursorclass=pymysql.cursors.DictCursor)

你可以使用 Python 的字典来存储日期和温度的对应关系，然后将这个字典作为一个字段存储在新的表中。以下是一个示例代码： ```python # 原始数据 data = [ (1, '北京', '20220620', 30), (2, '北京', '20220621', 28), ] # 创建一个空的字典来存储新的数据 new_data = {} # 遍历原始数据 for row in data: id, city, date, temp = row # 如果这个城市还没有在新的数据中，就添加一个新的条目 if city not in new_data: new_data[city] = {} # 将日期和温度添加到这个城市的字典中 new_data[city][date] = temp # 创建一个空的列表来存储新的表格数据 new_table = [] # 遍历新的数据 for city, dates in new_data.items(): # 将 ID，城市，和日期温度字典添加到新的表格数据中 new_table.append((id, city, dates)) # 打印新的表格数据 for row in new_table: print(row) ``` 这段代码会在终端输出 `[(1, '北京', {'20220620': 30, '20220621': 28})]`。这就是你想要的结果。希望这个答案对你有所帮助！

import pymysql import json conn = pymysql.connect(host='localhost', user='root', password='123123123', database='db', cursorclass=pymysql.cursors.DictCursor) # print(conn) with conn: with conn.cursor() as cursor: sql = 'INSERT INTO weather (city, date, temp) VALUES (%s, %s, %s)' cursor.execute(sql, ('北京', '20220620', 302)) cursor.execute(sql, ('北京', '20220621', 28)) conn.commit() with conn.cursor() as cursor: sql = 'SELECT * FROM weather' cursor.execute(sql) results = cursor.fetchall() city_data = {} date_temp = {} for result in results: date_temp[result['date']] = int(result['temp']) city_data[result['city']] = date_temp # print(results) # print(city_data) conn.commit() with conn.cursor() as cursor: sql = 'INSERT INTO weather_new (city, date_temp) VALUES (%s, %s)' for city, datetemp in city_data.items(): value = json.dumps(datetemp) cursor.execute(sql, (city, value)) conn.commit()

# %% import json import pymysql.cursors connection = pymysql.connect( host='localhost', user='root', password='root', database='db', cursorclass=pymysql.cursors.DictCursor ) # %% with connection: with connection.cursor() as cursor: sql = "INSERT INTO `weather1` (`city`,`date`,`temp`) VALUES (%s,%s,%s)" cursor.execute(sql, ('ningbo', '20230709', '29')) cursor.execute(sql, ('ningbo', '20230710', '31')) cursor.execute(sql, ('ningbo', '20230711', '32')) cursor.execute(sql, ('ningbo', '20230712', '30')) cursor.execute(sql, ('hangzhou', '20230709', '35')) cursor.execute(sql, ('hangzhou', '20230710', '36')) cursor.execute(sql, ('hangzhou', '20230711', '37')) connection.commit() with connection.cursor() as cursor: sql = 'SELECT `city`,`date`,`temp` FROM `weather1`' cursor.execute(sql) result = cursor.fetchall() city_to_date_temp = {} for d in result: if d['city'] in city_to_date_temp: city_to_date_temp[d['city']][d['date']] = d['temp'] else: city_to_date_temp[d['city']] = {} city_to_date_temp[d['city']][d['date']] = d['temp'] with connection.cursor() as cursor: sql = 'INSERT INTO `weather2` (`city`,`date_temp`) VALUES (%s,%s)' for city, date_temp in city_to_date_temp.items(): print(city, json.dumps(date_temp)) cursor.execute(sql, (city, json.dumps(date_temp))) connection.commit()

import pymysql.cursors from collections import UserDict class MyDict(UserDict): def __setitem__(self, key, value) -> None: if key in self.data.keys(): self.data[key].update(value) else: self.data[key] = value myDict = MyDict(); conn = pymysql.connect(host='localhost', user='root', password='', database='pydb', cursorclass=pymysql.cursors.DictCursor) with conn: with conn.cursor() as cursor: sql = "SELECT * FROM `weather`" cursor.execute(sql) result = cursor.fetchall() for res in result: myDict[res["city"]] = { res["date"] : res["temp"] } print(myDict) for myCity,myDateTemp in myDict.items(): sql = "INSERT INTO `weather_new` (`city`, `date_temp`) VALUES (%s, %s)" cursor.execute(sql, (myCity, str(myDateTemp))) # conn 不能自动提交数据，必须手动提交 conn.commit()

# 函数：将关于 date 和 temp 的【字典】结果，保存到数据库 def save_to_newtb(city, result_dict): conn = pymysql.connect(host='localhost', user='root', password='admin123', database='db', cursorclass=pymysql.cursors.DictCursor) with conn: with conn.cursor() as cursor: sql = "INSERT INTO `weather_clob` (`city`, `date_temp`) VALUES (%s, %s)" cursor.execute(sql, (city, str(result_dict))) # conn 不能自动提交数据，必须手动提交 conn.commit() # 主函数： def main(): city_list = city_list = find_all_cities() for i in city_list: city_temp = i["city"] result_list = find_clob_by_city(city_temp) result_dict = transform_list_to_dict(result_list) save_to_newtb(city_temp, result_dict) # 测试 if __name__ == '__main__': main()

import pymysql.cursors # 函数：找到 weather 表中所有的 city def find_all_cities(): conn = pymysql.connect(host='localhost', user='root', password='admin123', database='db', cursorclass=pymysql.cursors.DictCursor) with conn: with conn.cursor() as cursor: sql = "SELECT distinct `city` FROM `weather`" cursor.execute(sql) result = cursor.fetchall() return result # 函数：根据 city_name, 从 weather 表中找到对应的 date 和 temp，并返回结果列表 def find_clob_by_city(city_name): conn = pymysql.connect(host='localhost', user='root', password='admin123', database='db', cursorclass=pymysql.cursors.DictCursor) with conn: with conn.cursor() as cursor: sql = "SELECT `date`, `temp` FROM `weather` WHERE `city`=%s" cursor.execute(sql, (city_name)) result = cursor.fetchall() return result # 函数：将关于 date 和 temp 的【列表】结果，转化成【字典】结果 def transform_list_to_dict(result_list): result_dict = {} for i in result_list: data_key = i["date"] data_value = int(i["temp"]) result_dict[data_key] = data_value return result_dict