windows 在命令行或通过批处理文件将 CSV 文件导入 Sqlite3 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6977497/
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
Import CSV file into Sqlite3 Database in command-line or via Batch File
提问by misctp asdas
I would like to inquire on whether is there anyway to import a csv file that contains output of my select statements in SQLite3 into a new database? Following are the codes i have done thus far:
我想询问是否有将包含我在 SQLite3 中选择语句的输出的 csv 文件导入新数据库的方法？以下是我迄今为止所做的代码：
sqlite3.exe -csv logsql.sqlite "SELECT local_port AS port, COUNT(local_port) AS hitcount FROM connections WHERE connection_type = 'accept' GROUP BY local_port ORDER BY hitcount DESC;" > output.csv
sqlite3.exe -csv test.sqlite "CREATE TABLE test (name varchar(255) not null, blah varchar(255) not null);" .import ./output.csv test
as you can see my first code was to dump out the queries made.
如您所见，我的第一个代码是转储所做的查询。
the second line of code i'm attempting to make a new database and attemptign to import the csv file into the table "test"
第二行代码我正在尝试创建一个新数据库并尝试将 csv 文件导入表“test”
thanks for any help made in advance! :D
感谢您提前提供的任何帮助！:D
回答by MPelletier
I'd recommend doing your importation from a flat file, which will create your schema followed with the actual importation:
我建议您从平面文件导入，这将创建您的架构，然后是实际导入：
Like so:
像这样：
sqlite3.exe test.sqlite < import.sql
Where the content of import.sql is:
其中import.sql的内容是：
CREATE TABLE test (name varchar(255) not null, blah varchar(255) not null);
.separator ,
.import output.csv test
One other approach which you might not have considered is the ATTACHcommand. You can attach a new database, create the table in it, and import to its table, so you don't have the extra step of exporting to CSV then reparsing. It can be from a CREATE TABLE ... AS SELECT ...query or just an INSERT.
您可能没有考虑过的另一种方法是ATTACH命令。您可以附加一个新数据库，在其中创建表，然后导入到它的表中，这样您就没有导出到 CSV 然后重新解析的额外步骤。它可以来自CREATE TABLE ... AS SELECT ...查询或只是一个 INSERT。
So basically, you'd run (from your PHP Page):
所以基本上，你会运行（从你的 PHP 页面）：
"ATTACH 'c:\directory\to\database\test.db' as TESTDB;"
"CREATE TABLE TESTDB.test AS SELECT local_port AS port, COUNT(local_port) AS hitcount FROM connections WHERE connection_type = 'accept' GROUP BY local_port ORDER BY hitcount DESC;"
Or:
或者：
"ATTACH 'c:\directory\to\database\test.db' as TESTDB;"
"CREATE TABLE TESTDB.test (name varchar(255) not null, blah varchar(255) not null);"
"IMPORT INTO TESTDB.test SELECT local_port AS port, COUNT(local_port) AS hitcount FROM connections WHERE connection_type = 'accept' GROUP BY local_port ORDER BY hitcount DESC;"
回答by piokuc
For large CSV files it may be more efficient to use the sqlite3shell's .importcommand, rather than parse the file in Python and insert rows with sqlite3module. It can be done via os.system(on Linux, Unix or Mac OS X, or Cygwin on Windows):
对于大型 CSV 文件，使用sqlite3shell 的.import命令可能更有效，而不是在 Python 中解析文件并使用sqlite3模块插入行。它可以通过os.system（在 Linux、Unix 或 Mac OS X 上，或在 Windows 上的 Cygwin 上）完成：
cmd = '(echo .separator ,; echo .import ' + csv_file + ' ' + table + ')'
cmd += '| sqlite3 ' + db_name
os.system(cmd)
回答by Pauli
A single-file command to import a file via bash that worked for me:
通过 bash 导入文件的单文件命令对我有用：
sqlite3 inventory.sqlite.db << EOF
delete from audit;
.separator "\t"
.import audit-sorted-uniq.tsv audit
EOF
Hope that helps.
希望有帮助。
回答by user163193
First you need correct real csv file with comma separator and header line. I used the dump tool from phpstorm for this.
首先，您需要带有逗号分隔符和标题行的正确真实 csv 文件。为此，我使用了 phpstorm 的转储工具。
put all files into a folder.
将所有文件放入一个文件夹中。
open cmd.exe
打开cmd.exe
create import statements where %~na is the filename without extension as tablename
创建导入语句，其中 %~na 是没有扩展名的文件名作为表名
for /R %a in (*.csv) do @echo .import %~na.csv %~na >> import.txt
start sqlite with a dummy db
使用虚拟数据库启动 sqlite
sqlite test.sqlite
set import mode to csv
将导入模式设置为 csv
.mode csv
run commands from file
从文件运行命令
.read import.txt
list all tables
列出所有表
.tables
quit sqlite
退出sqlite
.q
回答by paulsm4
You can do a lot with the SQLite command shell and command-line switches... ... but I'd strongly urge you to find a SQLite-aware scripting language that'll work on Windows and that you feel comfortable with.
您可以使用 SQLite 命令外壳和命令行开关做很多事情……但我强烈建议您找到一种 SQLite 感知脚本语言，它可以在 Windows 上运行并且您感觉很舒服。
Perl and Python are two excellent choices. Both support SqlLite, both are freely available for Windows.
Perl 和 Python 是两个很好的选择。两者都支持 SqlLite，两者都可免费用于 Windows。
And both can handle this - and many other kinds of tasks.
两者都可以处理这个 - 以及许多其他类型的任务。
回答by Bandiera
I nedded to import many csv files, so I wrote the following python script that does the job of creating and loading sqlite tables from csv files, using the first line of the csv as the field names for the table:
我需要导入许多 csv 文件，因此我编写了以下 python 脚本，该脚本执行从 csv 文件创建和加载 sqlite 表的工作，使用 csv 的第一行作为表的字段名称：
#!/usr/bin/env python
import sqlite3
from csv import DictReader
class SQLiteDB():
def __init__(self, dbname=':memory:'):
self.db=sqlite3.connect(dbname)
def importFromCSV(self, csvfilename, tablename, separator=","):
with open(csvfilename, 'r') as fh:
dr = DictReader(fh, delimiter=separator)
fieldlist=",".join(dr.fieldnames)
ph=("?,"*len(dr.fieldnames))[:-1]
self.db.execute("DROP TABLE IF EXISTS %s"%tablename)
self.db.execute("CREATE TABLE %s(%s)"%(tablename, fieldlist))
ins="insert into %s (%s) values (%s)"%(tablename, fieldlist, ph)
for line in dr:
v=[]
for k in dr.fieldnames: v.append(line[k])
self.db.execute(ins, v)
self.db.commit()
if __name__ == '__main__':
db=SQLiteDB("mydatabase.sqlite")
db.importFromCSV("mydata.csv", "mytable")
For importing a large amount of data, you should implement transactions.
对于导入大量数据，您应该实现事务。
hth
第
