Queries

SQLSorcery is designed to simplify data analysis and script based ETL. A common need in both is the ability to run queries against database tables or views.

Connect to the database

If using a .env file:

1
2
3
from sqlsorcery import PostgreSQL

sql = PostgreSQL()

If specifying at object instantiation:

1
2
3
from sqlsorcery import PostgreSQL

sql = PostgreSQL(server="ip or url", port="port number", db="database name", schema="schema name", user="username", pwd="password")

Warning

It is generally inadvisable to specify connection variables directly in your code.

Query from a string

Reads a database table into a pandas dataframe and prints to console:

1
2
3
4
5
from sqlsorcery import PostgreSQL

sql = PostgreSQL()
df = sql.query("SELECT * FROM tablename")
print(df)

Query from a .sql file

If you had a .sql file with the following query named user_location.sql:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
SELECT
      u.id
    , u.username
    , l.latitude
    , l.longitude
    , l.ip_address
    , u.is_staff
FROM users u
INNER JOIN location l
    ON u.id = l.user_id
WHERE u.is_staff = false

You could query with it like so:

1
2
3
4
5
from sqlsorcery import PostgreSQL

sql = PostgreSQL()
df = sql.query_from_file("user_location.sql")
print(df)

Query a view

If that previous .sql file was a view in the database called vw_user_location you could query it like so:

1
2
3
4
5
from sqlsorcery import PostgreSQL

sql = PostgreSQL()
df = sql.query("SELECT * FROM vw_user_location")
print(df)