Importing a .sql file in python

Question

I have just started learning SQL and I'm having some difficulties to import my sql file in python.

The .sql file is in my desktop, as well is my .py file.

That's what I tried so far:

import codecs
from codecs import open
import pandas as pd
sqlfile = "countries.sql"
sql = open(sqlfile, mode='r', encoding='utf-8-sig').read()
pd.read_sql_query("SELECT name FROM countries")

But I got the following message error:

TypeError: read_sql_query() missing 1 required positional argument: 'con'

I think I have to create some kind of connection, but I can't find a way to do that. Converting my data to an ordinary pandas DataFrame would help me a lot.

Thank you

What sql variable used for in posted code? What is countries.sql file? Seems to be sqlite database, so it should be handled with sqlite-client. For more details dataquest.io/blog/python-pandas-databases — mrEvgenX
– mrEvgenX, Commented Jan 31, 2020 at 3:13

mrEvgenX · Accepted Answer · 2020-01-31 03:16:00Z

1

This is the code snippet taken from https://www.dataquest.io/blog/python-pandas-databases/ should help.

import pandas as pd
import sqlite3
conn = sqlite3.connect("flights.db")
df = pd.read_sql_query("select * from airlines limit 5;", conn)

Do not read database as an ordinary file. It has specific binary format and special client should be used.

With it you can create connection which will be able to handle SQL queries. And can be passed to read_sql_query.

Refer to documentation often https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql_query.html

answered Jan 31, 2020 at 3:16

mrEvgenX

7768 silver badges17 bronze badges

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

Comments

masat · Accepted Answer · 2020-01-31 03:32:58Z

1

You need a database connection. I don't know what SQL flavor are you using, but suppose you want to run your query in SQL server

import pyodbc
con = pyodbc.connect(driver='{SQL Server}', server='yourserverurl', database='yourdb', trusted_connection=yes)

then pass the connection instance to pandas

pd.read_sql_query("SELECT name FROM countries", con)

more about pyodbc here

And if you want to query an SQLite database

import sqlite3
con = sqlite3.connect('pathto/example.db')

More about sqlite here

edited Jan 31, 2020 at 3:32

answered Jan 31, 2020 at 3:21

masat

3501 silver badge10 bronze badges

Collectives™ on Stack Overflow

Importing a .sql file in python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related