保存数据和标题到 MySql 使用 Python

huangapple go评论59阅读模式
英文:

Save data along with headers My Sql python

问题

将 CSV 文件与标题一起保存。

以下是我正在使用的代码,但在这里我只得到了数据部分,没有标题部分。

sql_data = "select * from ETSY_seller where Crawl_Date='2022-12-14' and Cohort = '418k'"
sql_cursor.execute(sql_data, multi=True)

data = sql_cursor.fetchall()

fp = open('/home/ec2-user/TV_eCommerce/US/extra/cohort418k_20221214.csv', 'w')

myFile = csv.writer(fp)

myFile.writerows(data)

fp.close()
英文:

How to Save the csv file along with headers.

Below is the code which I am using but here I am getting only data not the headers part.

sql_data = "select * from ETSY_seller where Crawl_Date='2022-12-14' and Cohort = '418k'";
sql_cursor.execute(sql_data,multi=True)

data = sql_cursor.fetchall()

fp = open('/home/ec2-user/TV_eCommerce/US/extra/cohort418k_20221214.csv', 'w')

myFile = csv.writer(fp)

myFile.writerows(data)

fp.close()

答案1

得分: 1

以下是代码的中文翻译:

# 修改您的代码以在 CSV 文件中包含标题:

import csv
import mysql.connector

# 建立数据库连接和游标
mydb = mysql.connector.connect(
    host="your_host",  # 您的主机
    user="your_username",  # 您的用户名
    password="your_password",  # 您的密码
    database="your_database"  # 您的数据库名称
)
mycursor = mydb.cursor()

# 执行 SQL 查询以获取数据和标题
sql = "SELECT * FROM ETSY_seller WHERE Crawl_Date='2022-12-14' and Cohort = '418k'"
mycursor.execute(sql)
headers = [i[0] for i in mycursor.description]  # 获取列标题
data = mycursor.fetchall()

# 将数据和标题写入 CSV 文件
with open('/home/ec2-user/TV_eCommerce/US/extra/cohort418k_20221214.csv', 'w', newline='') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow(headers)
    writer.writerows(data)

如果您有其他需要,请告诉我。

英文:

You can include the headers in the CSV file by modifying your code as follows:

<!-- begin snippet: js hide: false console: true babel: false -->

<!-- language: lang-js -->

import csv
import mysql.connector

# establish database connection and cursor
mydb = mysql.connector.connect(
  host=&quot;your_host&quot;,
  user=&quot;your_username&quot;,
  password=&quot;your_password&quot;,
  database=&quot;your_database&quot;
)
mycursor = mydb.cursor()

# execute SQL query to fetch data and headers
sql = &quot;SELECT * FROM ETSY_seller WHERE Crawl_Date=&#39;2022-12-14&#39; and Cohort = &#39;418k&#39;&quot;
mycursor.execute(sql)
headers = [i[0] for i in mycursor.description]  # get column headers
data = mycursor.fetchall()

# write data and headers to CSV file
with open(&#39;/home/ec2-user/TV_eCommerce/US/extra/cohort418k_20221214.csv&#39;, &#39;w&#39;, newline=&#39;&#39;) as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow(headers)
    writer.writerows(data)

<!-- end snippet -->

huangapple
  • 本文由 发表于 2023年4月13日 19:18:07
  • 转载请务必保留本文链接:https://go.coder-hub.com/76004802.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定