需求:从mysql数据库中查询某表的数据,并将表中数据生成csv文件保存在指定文件下。
话不多说,直接上代码:
import pymysqlfrom settings import MYSQL_HOST, MYSQL_DB, MYSQL_PWD, MYSQL_USERimport csvdef mysql_db_sz_stock():# 连接数据库connect = pymysql.connect(host=MYSQL_HOST, # 本地数据库user=MYSQL_USER,password=MYSQL_PWD,db=MYSQL_DB,charset='utf8') # 服务器名,账户,密码,数据库名称cursor = connect.cursor()sql = "select * from daily_view"cursor.execute(sql)data = cursor.fetchall()mit() # 提交到数据库执行print("查询数据成功")# ---------------------关闭数据库cursor.close() # 关闭游标connect.close() # 关闭数据库连接return data# 将数据保存为csv文件def write_csv():data = mysql_db_sz_stock()filename = './csv/daily_view.csv' # 文件名和路径with open(filename, mode='w', encoding='utf-8') as f:write = csv.writer(f, dialect='excel')write.writerow(['symbol', 'datetime', 'Open', 'High', 'Low', 'Close', 'Volume']) # 先写下标题for item in data:write.writerow(item)write_csv()
运行后发现csv文件中数据隔行显示
解决办法:添加newline
def write_csv():data = mysql_db_sz_stock()filename = './csv/daily_view.csv' # 文件名和路径with open(filename, mode='w', newline='', encoding='utf-8') as f: # 添加newline=''write = csv.writer(f, dialect='excel')write.writerow(['symbol', 'datetime', 'Open', 'High', 'Low', 'Close', 'Volume']) # 先写下标题for item in data:write.writerow(item)
问题解决。