在命令行或批处理文件中将CSV文件导入Sqlite3数据库

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中select语句输出的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

5 个解决方案

#1

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 ATTACH command. 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;"

#2

For large CSV files it may be more efficient to use the sqlite3 shell's .import command, rather than parse the file in Python and insert rows with sqlite3 module. It can be done via os.system (on Linux, Unix or Mac OS X, or Cygwin on Windows):

对于大型CSV文件，使用sqlite3 shell的.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)

#3

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命令shell和命令行开关做很多事情......但我强烈建议您找到一种可以在Windows上运行并且您觉得舒服的SQLite感知脚本语言。

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.

两者都可以处理这个 - 以及许多其他类型的任务。

#4

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.

希望有所帮助。

#5

-1

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的第一行作为表的字段名称，从csv文件创建和加载sqlite表：

#!/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

心连心

#1