如何从 Python 中的数据库创建 CSV 文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3710263/
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
How do I create a CSV file from database in Python?
提问by Merlin
I have a Sqlite 3 and/or MySQL table named "clients"..
我有一个名为“clients”的 Sqlite 3 和/或 MySQL 表。
Using python 2.6, How do I create a csv file named Clients100914.csv with headers? excel dialect...
使用 python 2.6，如何创建一个名为 Clients100914.csv 的带有标题的 csv 文件？优秀的方言...
The Sql execute: select * only gives table data, but I would like complete table with headers.
Sql execute: select * 只提供表数据，但我想要带有标题的完整表。
How do I create a record set to get table headers. The table headers should come directly from sql not written in python.
如何创建记录集以获取表头。表头应该直接来自不是用 python 编写的 sql。
w = csv.writer(open(Fn,'wb'),dialect='excel')
#w.writelines("header_row")
#Fetch into sqld
w.writerows(sqld)
This code leaves me with file open and no headers. Also cant get figure out how to use file as log.
这段代码让我打开文件并且没有标题。也无法弄清楚如何使用文件作为日志。
采纳答案by Cristian Ciupitu
import csv
import sqlite3
from glob import glob; from os.path import expanduser
conn = sqlite3.connect( # open "places.sqlite" from one of the Firefox profiles
glob(expanduser('~/.mozilla/firefox/*/places.sqlite'))[0]
)
cursor = conn.cursor()
cursor.execute("select * from moz_places;")
#with open("out.csv", "w", newline='') as csv_file: # Python 3 version
with open("out.csv", "wb") as csv_file: # Python 2 version
csv_writer = csv.writer(csv_file)
csv_writer.writerow([i[0] for i in cursor.description]) # write headers
csv_writer.writerows(cursor)
PEP 249 (DB API 2.0)has more information about cursor.description.
PEP 249 (DB API 2.0)有更多关于cursor.description.
回答by Guillaume Lebourgeois
You can easily create it manually, writing a file with a chosen separator. You can also use csv module.
您可以轻松地手动创建它，使用选定的分隔符编写文件。您还可以使用csv 模块。
If it's from database you can alo just use a query from your sqlite client :
如果它来自数据库，您也可以使用来自您的 sqlite 客户端的查询：
sqlite <db params> < queryfile.sql > output.csv
Which will create a csv file with tab separator.
这将创建一个带有制表符分隔符的 csv 文件。
回答by Guillaume Lebourgeois
unless i'm missing something, you just want to do something like so...
除非我遗漏了什么，否则你只想做这样的事情......
f = open("somefile.csv")
f.writelines("header_row")
logic to write lines to file (you may need to organize values and add comms or pipes etc...)
将行写入文件的逻辑（您可能需要组织值并添加通信或管道等...）
f.close()
回答by Stephen Johnson
Using the csv moduleis very straight forward and made for this task.
使用csv 模块非常简单，专为这项任务而设计。
import csv
writer = csv.writer(open("out.csv", 'w'))
writer.writerow(['name', 'address', 'phone', 'etc'])
writer.writerow(['bob', '2 main st', '703', 'yada'])
writer.writerow(['mary', '3 main st', '704', 'yada'])
Creates exactly the format you're expecting.
创建完全符合您期望的格式。
回答by John Machin
How to extract the column headings from an existing table:
如何从现有表中提取列标题：
You don't need to parse an SQL "create table" statement.This is fortunate, as the "create table" syntax is neither nice nor clean, it is warthog-ugly.
您不需要解析 SQL“创建表”语句。这是幸运的，因为“创建表”语法既不好也不干净，它是疣猪丑陋的。
You can use the table_infopragma. It gives you useful information about each column in a table, including the name of the column.
您可以使用table_info编译指示。它为您提供有关表中每一列的有用信息，包括列的名称。
Example:
例子：
>>> #coding: ascii
... import sqlite3
>>>
>>> def get_col_names(cursor, table_name):
... results = cursor.execute("PRAGMA table_info(%s);" % table_name)
... return [row[1] for row in results]
...
>>> def wrong_way(cur, table):
... import re
... cur.execute("SELECT sql FROM sqlite_master WHERE name=?;", (table, ))
... sql = cur.fetchone()[0]
... column_defs = re.findall("[(](.*)[)]", sql)[0]
... first_words = (line.split()[0].strip() for line in column_defs.split(','))
... columns = [word for word in first_words if word.upper() != "CONSTRAINT"]
... return columns
...
>>> conn = sqlite3.connect(":memory:")
>>> curs = conn.cursor()
>>> _ignored = curs.execute(
... "create table foo (id integer, name text, [haha gotcha] text);"
... )
>>> print get_col_names(curs, "foo")
[u'id', u'name', u'haha gotcha']
>>> print wrong_way(curs, "foo")
[u'id', u'name', u'[haha'] <<<<<===== WHOOPS!
>>>
Other problems with the now-deleted "parse the create table SQL" answer:
现已删除的“解析创建表 SQL”答案的其他问题：
Stuffs up with e.g.
create table test (id1 text, id2 int, msg text, primary key(id1, id2))... needs to ignore not onlyCONSTRAINT, but also keywordsPRIMARY,UNIQUE,CHECKandFOREIGN(see thecreate tabledocs).Needs to specify
re.DOTALLin case there are newlines in the SQL.In
line.split()[0].strip()thestripis redundant.
塞满了 eg
create table test (id1 text, id2 int, msg text, primary key(id1, id2))... 不仅需要忽略CONSTRAINT，还需要忽略关键字PRIMARY,UNIQUE,CHECK和FOREIGN（参见create table文档）。需要指定
re.DOTALL以防 SQL 中有换行符。在
line.split()[0].strip()中strip是多余的。
回答by inspectorG4dget
You seem to be familiar with excel and want to stay close to it. Might I suggest trying PyExcelerator?
您似乎对 excel 很熟悉，并希望接近它。我可以建议尝试PyExcelerator吗？
回答by Francis Kessie
This is simple and works fine for me.
这很简单，对我来说效果很好。
Lets say you have already connected to your database table and also got a cursor object. So following on on from that point.
假设您已经连接到您的数据库表并且还获得了一个游标对象。所以从那一点开始。
import csv
curs = conn.cursor()
curs.execute("select * from oders")
m_dict = list(curs.fetchall())
with open("mycsvfile.csv", "wb") as f:
w = csv.DictWriter(f, m_dict[0].keys())
w.writerow(dict((fn,fn) for fn in m_dict[0].keys()))
w.writerows(m_dict)
回答by goyuiitv
It can be easily done using pandas and sqlite3. In extension to the answer from Cristian Ciupitu.
使用 pandas 和 sqlite3 可以轻松完成。延伸到 Cristian Ciupitu 的回答。
import sqlite3
from glob import glob; from os.path import expanduser
conn = sqlite3.connect(glob(expanduser('data/clients_data.sqlite'))[0])
cursor = conn.cursor()
Now use pandas to read the table and write to csv.
现在使用 Pandas 读取表格并写入 csv。
clients = pd.read_sql('SELECT * FROM clients' ,conn)
clients.to_csv('data/Clients100914.csv', index=False)
This is more direct and works all the time.
这更直接并且一直有效。
