How to Import a SQL file to Python

Question

I'm attempting to import an sq file that already has tables into python. However, it doesn't seem to import what I had hoped. The only things I've seen so far are how to creata a new sq file with a table, but I'm looking to just have an already completed sq file imported into python. So far, I've written this.

# Python code to demonstrate SQL to fetch data.

# importing the module
import sqlite3

# connect withe the myTable database
connection = sqlite3.connect("CEM3_Slice_20180622.sql")

# cursor object
crsr = connection.cursor()

# execute the command to fetch all the data from the table emp
crsr.execute("SELECT * FROM 'Trade Details'") 

# store all the fetched data in the ans variable
ans= crsr.fetchall() 

# loop to print all the data
for i in ans:
    print(i)

However, it keeps claiming that the Trade Details table, which is a table inside the file I've connected it to, does not exist. Nowhere I've looked shows me how to do this with an already created file and table, so please don't just redirect me to an answer about that

Answer 1

There are two possibilities:

1. Your file is not in the correct format and therefore cannot be opened.
2. The SQLite file can exist anywhere on the disk e.g. /Users/Username/Desktop/my_db.sqlite , this means that you have to tell python exactly where your file is otherwise it will look inside the scripts directory, see that there is no file with the same name and therefore create a new file with the provided filename.

Answer 2

sqlite3.connect expects the full path to your database file or '::memory::' to create a database that exists in RAM. You don't pass it a SQL file. Eg.

connection = sqlite3.connect('example.db')

You can then read the contents of CEM3_Slice_20180622.sql as you would a normal file and execute the SQL commands against the database.

Answer 3

As suggested by Rakesh above, you create a connection to the DB, not to the .sql file. The .sql file contains SQL scripts to rebuild the DB from which it was generated. After creating the connection, you can implement the following:

cursor = connection.cursor() #cursor object
with open('CEM3_Slice_20180622.sql', 'r') as f: #Not sure if the 'r' is necessary, but recommended.
    cursor.executescript(f.read())

Documentation on executescript found here

To read the file into pandas DataFrame:

 import pandas as pd
 df = pd.read_sql('SELECT * FROM table LIMIT 10', connection)