There is a post that tells me how to write data fom a csv-file into a sqlite database (link). Is there a way to simply copy the whole file into a database table instead of loading the data and then iterate through the rows appending them to the table as suggested in the link.
To do this in sqlite it says here to simply type
sqlite> create table test (id integer, datatype_id integer, level integer, meaning text);
sqlite> .separator ","
sqlite> .import no_yes.csv test
I am new to working with databases and sqlite3, but I thought doing something like this could work
import sqlite3
conn = sqlite3.connect('mydatabase.db')
c = conn.cursor()
def create_table(name):
c.execute("CREATE TABLE IF NOT EXISTS {} (time REAL, event INTEGER, id INTEGER, size INTERGER, direction INTEGER)".format(name))
def copy_data(file2copy,table_name):
c.executescript("""
.separator ","
.import {} {}""".format(file2copy,table_name))
conn.commit()
try:
create_table('abc')
copy_data(r'Y:\MYPATH\somedata.csv','abc')
except Exception as e:
print e
c.close()
conn.close()
But apparently it doesn't. I get the error
near ".": syntax error
EDIT: Thanks to the below suggestion to use the subprocess module, I came up with the follwing solution.
import subprocess
sqlshell = r'c:\sqlite-tools\sqlite3' # sqlite3.exe is a shell that can be obtained from the sqlite website
process = subprocess.Popen([sqlshell], stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
cmd = """.open {db}
.seperator ","
.import {csv} {tb}""".format(db='C:/Path/to/database.db', csv='C:/Path/to/file.csv', tb='table_name')
stdout, stderr = process.communicate(input=cmd)
if len(stderr):
print 'something went wrong'
Importantly, you should use '/' instead of '\' in your directory names. Also, you have to be carefull with blank spaces in your directory names.
