Creating a SQL table from CSV file with Python

Question

I need to import a csv file into a database in order to query it in a Python notebook and I tried several commands but every time I get a syntax error. I created a new empty database using

conn_british = sqlite3.connect('path\db name')

This is the code I tried

query='''BULK INSERT 
         {table_name}
         FROM '\Documents\file.csv'
         [WITH
         (
         [FORMAT = 'CSV'], 
         [FIELDQUOTE = '"'],
         [FIRSTROW = 2],
         [FIELDTERMINATOR = ','],  
         [ROWTERMINATOR = '\n'],   
         [TABLOCK]
         )]  '''

pd.read_sql_query(query,conn_british)

and the error : near "BULK": syntax error

I also tried

     '''COPY table_name 
     FROM '\Documents\file.csv' 
     DELIMITER ','
     '''

but I get the same error

Does this answer your question, https://stackoverflow.com/questions/14947916/import-csv-to-sqlite — sushanth, May 26 '20 at 12:09
Or this: https://stackoverflow.com/questions/18219779/bulk-insert-huge-data-into-sqlite-using-python — mechanical_meat, May 26 '20 at 12:15
@Sushanth no, i don't think it works like that in python notebook — Ana, May 26 '20 at 12:21
Since ur using pandas, load the CSV as dataframe and use ```to_sql``` and dump to db https://stackoverflow.com/questions/14431646/how-to-write-pandas-dataframe-to-sqlite-with-index — sushanth, May 26 '20 at 12:27
@Sushanth I tried it and get another error: _Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': Error binding parameter 0 - probably unsupported type._ — Ana, May 26 '20 at 12:32

score 0 · Answer 1 · answered Jul 25 '22 at 18:13

I think the question is you just want to run SQL queries on a CSV. You can use FugueSQL to do that.

A sample Python snippet would be:

from fugue_sql import fsql

query = """
df = LOAD "/path/to/myfile.csv"

SELECT * 
  FROM df
 WHERE col > 1
 PRINT
"""
fsql(query).run()

and this will use Pandas to run the query by default. There is also a SAVE keyword so you can save the output somewhere.

This pushes down the operations to Pandas, so you don't need this intermediate SQLite persistence.

Creating a SQL table from CSV file with Python

1 Answers1