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

Are you sure CEM3_Slice_20180622.sql file is in SQLite db format? Or is it just a list of SQL commands as extension suggest? sqlite3.connect method connects to SQLite database on disk or in memory, but it has to be in correct format. — running.t
– running.t, Commented Jun 27, 2018 at 14:36
It depends what do you mean by turn the sql into a db. If *.sql is an export copy of a database i.e. list of SQL commands required to create copy of that database, then what you have to do is to run SQLite prompt, execute all commands from that copy and save database in SQLite format. Then you can connect to this newly created file using python sqlite lib. On the other hand, just changing the extension of file is definatelly not enough. — running.t
– running.t, Commented Jun 27, 2018 at 14:46

teddy waweru · Accepted Answer · 2022-01-18 06:45:28Z

1

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)

edited Jan 18, 2022 at 6:45

answered Jan 18, 2022 at 6:30

teddy waweru

811 silver badge4 bronze badges

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

Comments

djacobsdev · Accepted Answer · 2018-06-27 14:38:58Z

0

There are two possibilities:

Your file is not in the correct format and therefore cannot be opened.
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.

answered Jun 27, 2018 at 14:38

djacobsdev

716 bronze badges

Comments

rakesh_singh · Accepted Answer · 2018-06-27 14:48:31Z

0

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.

answered Jun 27, 2018 at 14:48

rakesh_singh

867 bronze badges

Collectives™ on Stack Overflow

How to Import a SQL file to Python

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related