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, stackoverflow.com/questions/14947916/import-csv-to-sqlite — sushanth
– sushanth, Commented May 26, 2020 at 12:09
@Sushanth no, i don't think it works like that in python notebook — Ana
– Ana, Commented May 26, 2020 at 12:21
Since ur using pandas, load the CSV as dataframe and use to_sql and dump to db stackoverflow.com/questions/14431646/… — sushanth
– sushanth, Commented May 26, 2020 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
– Ana, Commented May 26, 2020 at 12:32

Kevin Kho · Accepted Answer · 2022-07-25 18:13:44Z

0

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.

answered Jul 25, 2022 at 18:13

Kevin Kho

7124 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating a SQL table from CSV file with Python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related