I have a .sql file that I just want to read into a Pandas dataframe in Jupyter Notebook. With CSV files, I just had to upload the file and create a dataframe using something like this:
df = pd.read_csv('file.csv')
I gather that my SQL file needs to be served using a database application like MySQL. Perhaps there's some way to host the .sql file locally? I don't need to do any database manipulation - I just want to read the table values.
import pandas as pd
import pyodbc
from sqlalchemy import create_engine
engine = create_engine('mysql+pyodbc://<username>:<password>@<server>:<port>/<db_name>?driver=<driver_name>')
with open('Path/to/xxx.sql', 'r') as sql_file:
query = sql_file.read()
df = pd.read_sql(query, con=cnxn)
all the info in <> are specific to your setup
I'm guessing you're trying to import the table in pandas just from the .sql file without connecting to, or using any SQL servers. But .sql files just contain some queries, or even if you exported a table into a .sql file, it would require an active database connection to read into pandas dataframe. You can look into all the pandas functions for reading or writing SQL at IO Tools, and notice that all the available functions require an active connection.
I guess you should work with this web link which will directly take sql dump and creates csv and then upload it
http://www.convertcsv.com/sql-to-csv.htm
or you may use
import subprocess
proc = subprocess.Popen(["mysql", "--user=%s" % USER, "--password=%s" % PASS, "database"],
stdin=subprocess.PIPE,
stdout=subprocess.PIPE)
out, err = proc.communicate(file("/tmp/dump.sql").read())
