Sql Examples¶

Note: if you are interested in this kind of thing, definitely have a look at how these two libraries implement it...

ibis - see here
dbplyr

In [1]:

from sqlalchemy import sql
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:', echo=False)


metadata = MetaData()
users = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('fullname', String),
)


addresses = Table('addresses', metadata,
  Column('id', Integer, primary_key=True),
  Column('user_id', None, ForeignKey('users.id')),
  Column('email_address', String, nullable=False)
 )

metadata.create_all(engine)

conn = engine.connect()

ins = users.insert().values(name='jack', fullname='Jack Jones')
result = conn.execute(ins)


ins = users.insert()
conn.execute(ins, id=2, name='wendy', fullname='Wendy Williams')


res = conn.execute(addresses.insert(), [
   {'user_id': 1, 'email_address' : 'jack@yahoo.com'},
   {'user_id': 1, 'email_address' : 'jack@msn.com'},
   {'user_id': 2, 'email_address' : 'www@www.org'},
   {'user_id': 2, 'email_address' : 'wendy@aol.com'},
])

In [2]:

from siuba.sql import filter, mutate, select, LazyTbl, arrange, lift_inner_cols
from siuba.siu import _

tbl_users = LazyTbl(conn, users)

print(tbl_users.last_op)

SELECT users.id, users.name, users.fullname 
FROM users

Select¶

In [3]:

tbl = select(tbl_users, _.fullname, -_.id)
print(tbl.last_op)

SELECT users.fullname, users.name 
FROM users

Filter¶

In [4]:

tbl = filter(tbl_users, _.fullname == "michael")
print(tbl.last_op)

SELECT anon_1.id, anon_1.name, anon_1.fullname 
FROM (SELECT anon_2.id AS id, anon_2.name AS name, anon_2.fullname AS fullname 
FROM (SELECT users.id AS id, users.name AS name, users.fullname AS fullname 
FROM users) AS anon_2) AS anon_1 
WHERE anon_1.fullname = :fullname_1

In [5]:

# currently, you can use any method exposed by sqlalchemy ColumnElement class
# but I plan to implement a standard set of functions like in dplyr, so pandas
# or sql queries psshh no matter.
tbl = filter(tbl_users, _.fullname.startswith("m"))
print(tbl.last_op)

SELECT anon_1.id, anon_1.name, anon_1.fullname 
FROM (SELECT anon_2.id AS id, anon_2.name AS name, anon_2.fullname AS fullname 
FROM (SELECT users.id AS id, users.name AS name, users.fullname AS fullname 
FROM users) AS anon_2) AS anon_1 
WHERE (anon_1.fullname LIKE :fullname_1 || '%')

Mutate¶

In [6]:

# simple
tbl = mutate(tbl_users, wow = _.id + _.name)
print(tbl.last_op)

SELECT users.id, users.name, users.fullname, users.id + users.name AS wow 
FROM users

In [7]:

# using previous col created in mutate
tbl = mutate(tbl_users, wow = _.id + 1, wow2 = _.wow + 2)
print(tbl.last_op)

SELECT anon_1.id, anon_1.name, anon_1.fullname, anon_1.wow, anon_1.wow + :wow_1 AS wow2 
FROM (SELECT users.id AS id, users.name AS name, users.fullname AS fullname, users.id + :id_1 AS wow 
FROM users) AS anon_1

In [8]:

# replacing column
tbl = mutate(tbl_users, id = _.id + 1)
print(tbl.last_op)

SELECT users.name, users.fullname, users.id + :id_1 AS id 
FROM users

In [9]:

# replacing column, then referring to replacement
tbl = mutate(tbl_users, id = _.id + 1, id2 = _.id + 2)
print(tbl.last_op)

SELECT anon_1.name, anon_1.fullname, anon_1.id, anon_1.id + :id_1 AS id2 
FROM (SELECT users.name AS name, users.fullname AS fullname, users.id + :id_2 AS id 
FROM users) AS anon_1

Arrange¶

In [10]:

tbl = arrange(tbl_users, _.id + _.name)
print(tbl.last_op)

SELECT users.id, users.name, users.fullname 
FROM users ORDER BY users.id + users.name