python - csv import sqlite3 不指定列类型

Question

我有一个包含大约 280 列的 csv 文件，这些列可能会不时更改。有没有办法将 csv 文件导入 sqlite3 并让它“猜测”列类型？我正在使用 python 脚本导入它。

Answer 1

如果您能够在此项目中使用第 3 方库，我推荐 pandas .

使用 pandas，您可以分两步完成此操作:

1. Read CSV file into pandas DataFrame
2. Write pandas DataFrame to SQLite

例如:

import pandas, pandas.io.sql, sqlite3

# some sample csv data copied from: http://wesmckinney.com/blog/?p=635
csvfilepath = '/path/to/file.csv'

# `index_col` set to False ensures pandas doesn't use 1st col of data as index
df = pandas.io.parsers.read_csv(csvfilepath,index_col=False) 

# connect to in-memory database for testing; replace `:memory:` w/ file path
con = sqlite3.connect(':memory:')
pandas.io.sql.write_frame(df, 'test_tbl', con)
con.execute('select * from test_tbl').fetchone()
con.close()

查询结果:

(u'C00410118',
 u'P20002978',
 u'Bachmann, Michele',
 u'HARVEY, WILLIAM',
 u'MOBILE',
 u'AL',
 366010290,
 u'RETIRED',
 u'RETIRED',
 250,
 u'20-JUN-11',
 None,
 None,
 None,
 u'SA17A',
 736166,
 u'A1FDABC23D2D545A1B83',
 u'P2012')

通过内省(introspection)查询，您可以看到 pandas 已经完成了创建表的工作，甚至推断出数据类型:

con.execute("select * from sqlite_master where type='table';").fetchone()[4]

给予:

CREATE TABLE test_tbl (  [cmte_id] TEXT,  [cand_id] TEXT,  [cand_nm] TEXT,  [contbr_nm] TEXT,  [contbr_city] TEXT,  [contbr_st] TEXT,  [contbr_zip] INTEGER,  [contbr_employer] TEXT,  [contbr_occupation] TEXT,  [contb_receipt_amt] INTEGER,  [contb_receipt_dt] TEXT,  [receipt_desc] REAL,  [memo_cd] REAL,  [memo_text] REAL,  [form_tp] TEXT,  [file_num] INTEGER,  [tran_id] TEXT,  [election_tp] TEXT )