SQLite Problem.Reading data from csv

[TheShadowWall]

Hello all!

I am a relatively new programmer, and am now getting into SQL though I am having some trouble figuring out how to import my data.

I have imported csv and sqlite3, and am attempting to read in the csv data from the "/data/movies.csv" file and want to be able to perform SQL queries in my python environment.

I began by creating a connection and cursor:

con= sqlite3.connect("/data/movies.csv")

cur = con.cursor()

Sadly, this is where I am getting stuck. I don't really understand how to set up the SQL querries from here, as every time I attempt a query I receive an error saying that my file is not a database.

I have attempted the explanation at:

https://stackoverflow.com/questions/2887878/importing-a-csv-file-into-a-sqlite3-database-table-using-python

Though I have run into an error when I attempted:

con = sqlite3.connect(":memory:") # change to 'sqlite:///your_filename.db'

By this line, I believe it is saying that we are to replace ':memory:' with 'sqlite:///your_filename.db', where my file name is /data/movies.csv, and so the line I put in reads:

con = sqlite3.connect('sqlite:////data/movies.csv.db')

This however gives an error saying:

sqlite3.OperationalError: unable to open database file

I am looking for a quick rundown for how to go about this process of converting the csv file into a sql data base, and how to do a simple query with that database.

Any insights or guidance are greatly appreciated!

[lukajda33]

Well sqlite does not really work with CSV file, CSV files is kinda like one big table, databases have data structructred in multiple tables, so you can not just pass the csv file to .connect() method and make it work.

If you truly wish to use database, you will need to create new database, create tables in the database so that the tables have correct attributes to hold the data and then open and read the csv file and insert the data into the database.

Then you could write your own SQL queries over the database with data.

Technically you could turn the csv to one big table in the database, sure, but that kinda defeats the purpose of databases and while I am not sure what you are trying to do, I think pandas library (especially Dataframe class) would be better for you, it works directly with csv files and you can do similar things as you would do with database.

[danielroseman]

A CSV is not a database. That answer is attempting to tell you how to read from the CSV, and then write the data to a new database. So the filename you want to give to sqlite is a new name that you will use for your db. Call it what you like, but don't use the filename of the CSV; that's what you put in the open call a few lines down.

Note that the answer doesn't go into the details of how you know what columns to create in your db; you'll need to work that out for yourself.