用python写mysql查询到csv，需要显示字段名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4613465/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using python to write mysql query to csv, need to show field names
提问by Mathnode
I have the following:
我有以下几点：
import MySQLdb as dbapi
import sys
import csv
dbServer='localhost'
dbPass='supersecretpassword'
dbSchema='dbTest'
dbUser='root'
dbQuery='SELECT * FROM pbTest.Orders;'
db=dbapi.connect(host=dbServer,user=dbUser,passwd=dbPass)
cur=db.cursor()
cur.execute(dbQuery)
result=cur.fetchall()
c = csv.writer(open("temp.csv","wb"))
c.writerow(result)
This produces a garbled mess. I am familiar with using printing record[0] etc. Not sure how I should be going about setting up the formatting. to produce something like what a query would in a console. I cannot do a simple INTO OUTFILE from the mysql server.
这会产生乱码。我熟悉使用打印记录 [0] 等。不知道我应该如何设置格式。生成类似于在控制台中查询的内容。我不能从 mysql 服务器做一个简单的 INTO OUTFILE。
Update
更新
It's been 8 years; I still get the occasional update or query about this question.
已经8年了；我仍然偶尔会收到有关此问题的更新或查询。
As stated in some of the comments, the cursor.descriptionfrom the DBAPI is what I was looking for.
正如一些评论中所述，来自DBAPI的 cursor.description 是我正在寻找的。
Here is a more modern example in Python 3 using the pymysqldriver to connect to MariaDB, which will select and fetch all rows into a tuple, the row headers/description into a list. I then merge these two data structures into a single list, to be written to a csv file.
这是 Python 3 中的一个更现代的示例，它使用pymysql驱动程序连接到MariaDB，它将选择所有行并将其提取到一个元组中，将行标题/描述提取到一个列表中。然后我将这两个数据结构合并到一个列表中，写入一个 csv 文件。
With the header names being the first entry in the result list; writing the result to a file in a linear fashion ensures the row header is the first line in the CSV file.
标题名称是结果列表中的第一个条目；以线性方式将结果写入文件可确保行标题是 CSV 文件中的第一行。
import pymysql
import csv
import sys
db_opts = {
'user': 'A',
'password': 'C',
'host': 'C',
'database': 'D'
}
db = pymysql.connect(**db_opts)
cur = db.cursor()
sql = 'SELECT * from schema_name.table_name where id=123'
csv_file_path = '/tmp/my_csv_file.csv'
try:
cur.execute(sql)
rows = cur.fetchall()
finally:
db.close()
# Continue only if there are rows returned.
if rows:
# New empty list called 'result'. This will be written to a file.
result = list()
# The row name is the first entry for each entity in the description tuple.
column_names = list()
for i in cur.description:
column_names.append(i[0])
result.append(column_names)
for row in rows:
result.append(row)
# Write result to file.
with open(csv_file_path, 'w', newline='') as csvfile:
csvwriter = csv.writer(csvfile, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
for row in result:
csvwriter.writerow(row)
else:
sys.exit("No rows found for query: {}".format(sql))
回答by Jaydee
I know little python, but after a bit of googling, should you be using writerows (with an S) which writes multiple rows, rather than writerow which expects a single row.
我知道很少的python，但是经过一些谷歌搜索后，您是否应该使用写入多行的writerows（带有S），而不是需要单行的writerow。
As I say this is a bit of a stab in the dark from me.
正如我所说，这对我来说有点暗中刺伤。
回答by Daniel Roseman
resultis a list of rows. So you'll need to iterate through that list and write each row:
result是一个行列表。因此，您需要遍历该列表并写入每一行：
for row in result:
c.writerow(row)
回答by jwhat
You can dump all results to the csv file without looping:
您可以将所有结果转储到 csv 文件而无需循环：
rows = cursor.fetchall()
fp = open('/tmp/file.csv', 'w')
myFile = csv.writer(fp)
myFile.writerows(rows)
fp.close()
回答by HappyBambi
Just add one more thing to @jwhat answer.
只需在@jwhat 答案中再添加一件事。
If you intend to open this csv file in windows, you will need to handle the extra empty lines.
如果您打算在 Windows 中打开此 csv 文件，则需要处理额外的空行。
just change the code to the following, as the default lineterminator in 2.7 is \r\n
只需将代码更改为以下内容，因为 2.7 中的默认换行符是 \r\n
myFile = csv.writer(fp, lineterminator='\n')
回答by Mwspencer
Answer with column header names for anyone who comes across this: (I am using Jwhat's method to write to csv)
为遇到此问题的任何人回答列标题名称：（我正在使用 Jwhat 的方法写入 csv）
result = cur.fetchall()
#Getting Field Header names
column_names = [i[0] for i in cur.description]
fp = open('Result_Set.csv' 'w')
myFile = csv.writer(fp, lineterminator = '\n') #use lineterminator for windows
myFile.writerow(column_names)
myFile.writerows(result)
fp.close()
回答by George B
The previous answer would give an error. The header must be written to the file first, then the rows added. The following worked for me.
以前的答案会出错。必须首先将标题写入文件，然后添加行。以下对我有用。
rows = cursor.fetchall()
headers = [col[0] for col in cursor.description] # get headers
rows = (tuple(headers),) + rows # add headers to rows
fp = open('/tmp/file.csv', 'w')
myFile = csv.writer(fp)
myFile.writerow(headers)
myFile.writerows(rows)
fp.close()
回答by MAES
import pymysql
import sys
import csv
connection = pymysql.connect(host='abcde',
user='admin',
password='secret',
db='databaseName',
port=12345)
dbQuery="SELECT field1, field2, fieldn FROM database.table;"
cur=connection.cursor()
cur.execute(dbQuery)
rows=cur.fetchall()
column_names = [i[0] for i in cur.description]
fp = open('DimDevice.csv', 'w')
myFile = csv.writer(fp, lineterminator = '\n')
myFile.writerow(column_names)
myFile.writerows(rows)
fp.close()
