Introduction to SQL Part 2¶
In this notebook we will connect to a remote SQL database and examine more advanced SQL queries. As with the first notebook, we will be using the core sqlalchemy library as an abstraction layer on-top of the underlying database management system. We will not be using the sophisticated object relational management features available in sqlalchemy.
import sqlalchemy
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt
import numpy as p
Connecting to the remote database¶
Often the database will be hosted on a remote server. In data100 we will use a hosted database. Because many students will be connecting to the same database we have created a list of databases and ask that you use the following script to generate the db_uri. This script will pick a random database each time you connect to try and balance the load across machines.
def get_db_url(password="dataisgreat"):
import pandas as pd
# Download the (potentially changing) list of databases
db_list = pd.read_csv("http://ds100.org/sp18/assets/sql/db_list.csv")
# Pick a random databse from the list
db_info = db_list.sample().iloc[0]
# Get it's info
username = db_info['username']
server = db_info['host']
dbname = db_info['dbname']
# Construct the database connection string
db_url = 'postgres+psycopg2://{}:{}@{}/{}'.format(
username, password, server, dbname)
return db_url
db_url = get_db_url()
print(db_url)
engine = sqlalchemy.create_engine(db_url, connect_args={'sslmode':'require'})
Connecting to A Local Postgres Database¶
You can also try to setup your own local Postgres Database. You will need:
- The FEC (and lecture) SQL data
- Install postres mac, windows, or linux.
- Install the FEC data into a Postgres database (instructions). On mac and linux it looks like:
bunzip2 -c filename.gz | psql ds100
To connect to your local database you create an engine with the following command:
engine = sqlalchemy.create_engine("postgres://postgres:@localhost:5432/ds100")
#engine = sqlalchemy.create_engine("postgres://postgres:@localhost:5432/ds100")
Postgres Command Line Tool¶
For those that are interested in exploring the Postgres command line tool, you can connect to the database using the following command in a terminal.
def get_db_psql_command(password="dataisgreat"):
import pandas as pd
# Download the (potentially changing) list of databases
db_list = pd.read_csv("http://ds100.org/sp18/assets/sql/db_list.csv")
# Pick a random databse from the list
db_info = db_list.sample().iloc[0]
# Get it's info
username = db_info['username']
server = db_info['host']
dbname = db_info['dbname']
# Construct the database connection string
command = 'PGPASSWORD={} psql -h {} -U {} --dbname {}'.format(
password, server, username, dbname)
return command
get_db_psql_command()
Note that we will not be needing to use this tool in lecture.
Listing the Tables in the Database¶
This database contains quite a few more tables. We will explore these tables in this lecture.
engine.table_names()
Data Generation¶
It is often assumed that when working with a database all relations (tables) must come from outside or be derived from other sources of data. It is possible to construct tables in SQL.
Sometimes it's useful to auto-generate data in queries, rather than examine data in the database. This is nice for testing, but also can be useful to play some computational tricks as you'll see in your homework.
SQL has a simple scalar function called random that returns a random value between 0.0 and 1.0. You can use this if you need to generate a column of random numbers. (The PostgreSQL manual doesn't promise much about the statistical properties of this random number generator.)
Let's roll a 6-sided die for each of the students
sql_expr = """
SELECT *, ROUND(RANDOM() * 6) as roll_dice
FROM students;
"""
pd.read_sql(sql_expr, engine)
Is this a good implementation of a fair 6 sided die?
SELECT *, ROUND(RANDOM() * 6) as roll_dice
FROM students;
Suppose we want to generate a whole bunch of random numbers, not related to any particular stored table -- can we do that in SQL?
SQL has a notion of table-valued functions: functions that return tables, and hence can be used in a FROM clause of a query. The standard table-valued function is called generate_series, and it's much like numpy's arange:
sql_expr = """
SELECT *
FROM GENERATE_SERIES(1, 5);
"""
pd.read_sql(sql_expr, engine)
sql_expr = """
SELECT *
FROM GENERATE_SERIES(1, 10, 2);
"""
pd.read_sql(sql_expr, engine)
Let's test the distribution of our earlier generator:
sql_expr = """
SELECT ROUND(6*RANDOM()) AS rando, COUNT(*)
FROM GENERATE_SERIES(1, 100000) AS flip(trial)
GROUP BY rando
ORDER BY count
"""
pd.read_sql(sql_expr, engine)
And if we want integers, we can use a PostgreSQL typecast operator (postfix ::<type>):
sql_expr = """
-- NOTE WE ALSO TAKE THE CEIL
-- What would happen if we did ot?
SELECT CEIL(6*RANDOM())::INTEGER AS rando, COUNT(*)
FROM generate_series(1, 100000) AS flip(trial)
GROUP BY rando
ORDER BY count
"""
pd.read_sql(sql_expr, engine)
Making a Random Matrix in SQL?!¶
Now suppose we want to populate a "matrix" relation my_matrix(r, c, v) full of random values. Consider the following numpy code
import numpy as np
np.random.seed(43)
# normally distributed random numbers, mean 0 variance 1
my_matrix = np.random.randint(1,6, (3,2)).astype('float')
my_matrix
Saving a Matrix as a table¶
Question: How could we store the above matrix as a table?
Answers:
- A table with 3 rows and 2 columns
- A table with 9 rows and 3 columns (how?)
Building the table in Numpy
my_matrix.flatten()
# Advanced numpy (you don't need to know this ...)
(col_id, row_id) = np.meshgrid(np.arange(2), np.arange(3))
mat_a = pd.DataFrame(
np.vstack([row_id.flatten().T, col_id.flatten(), my_matrix.flatten()]).T,
columns=['r', 'c', 'v'])
mat_a
In this relational version we need to explicitly generate the r and c values. We can do this via SQL's built-in Cartesian product!
sql_expr = """
SELECT rows.r, columns.c, CEIL(6*RANDOM())::INTEGER AS v
FROM generate_series(0,2) AS rows(r),
generate_series(0,1) AS columns(c);
"""
pd.read_sql(sql_expr, engine)
A few take-aways from the previous cell:
- Notice the schema of
my_matrixreflects the fact thatvalis a function of the row (x) and column (y) IDs. - We've said before that the order of rows in a table isn't defined in SQL. Is this relational representation of a "matrix" faithful to the mathematical definition of a matrix? Why or why not?
[Bonus Material] User-defined functions (UDFs)¶
Sometimes we may want a custom scalar function that isn't built into SQL. Some database systems allow you to register your own user-defined functions (UDFs) in one or more programming languages. Conveniently, PostgreSQL allows us to register user-defined functions written in Python. Be aware of two things:
Calling Python for each row in a query is quite a bit slower than using the pre-compiled built-in functions in SQL ... this is akin to the use of Python loops instead of
numpycalls. If you can avoid using Python UDFs you should do so to get better performance.Python is a full-feature programming language with access to your operating system's functionality, which means it can reach outside of the scope of the query and wreak havoc, including running arbitrary UNIX commands. (PostgreSQL refers to this as an
untrustedlanguage.) Be very careful with the Python UDFs you use in your Postgres queries! If you want to be safer write UDFs in a trusted language. PostgreSQL has a number of other languages to choose from, including Java and even R!.
First we tell PostgreSQL we want to use the plpythonu package (so named because of "pl" for "programming language", "u" for "untrusted"):
try:
engine.execute("""
CREATE EXTENSION IF NOT EXISTS plpythonu;
""")
udf_support = True
except Exception as e:
print(e)
udf_support = False
Now let's write some trivial Python code and register it as a UDF using the create function command. Since SQL is a typed language, we need to specify the SQL types for the input and output to our function, in addition to the code (within $$ delimiters) and the language:
if udf_support:
engine.execute("""
DROP FUNCTION IF EXISTS fib(x INTEGER);
""")
engine.execute("""
CREATE FUNCTION fib(x INTEGER) RETURNS INTEGER
AS $$
def fib(x):
if x < 2:
return x
else:
return fib(x-1) + fib(x-2)
return fib(x)
$$ LANGUAGE plpythonu;
""")
if udf_support:
sql_expr = """
SELECT x, fib(x)
FROM generate_series(1,10) AS row(x);
"""
display(pd.read_sql(sql_expr, engine))
[Bonus Material] A Discussion on Transactions¶
It is possible to create transactions that isolate changes. This is done by starting a transaction with BEGIN. We can then proceed to make changes to the database. During this time others will not be able to see our changes. Until we end the transactions by saying ROLLBACK or COMMIT:
BEGIN;
UPDATE students SET gpa = 3.0 WHERE name = 'Bill Gates';
SELECT * FROM students;
ROLLBACK;
SELECT * FROM students;
Try running this in the postgres shell...
Descriptive Statistics in SQL¶
Statistics doesn't deal with individuals, it deals with groups: distributions, populations, samples and the like. As such, computing statistics in SQL focuses heavily on aggregation functions.
All SQL systems have simple descriptive statistics built in as aggregation functions:
min, maxcountsumavgstddevandvariance, the sample standard deviation and variance.
PostgreSQL offers many more. Some handy ones include
stddev_popandvar_pop: the population standard deviation and variance, which you should use rather thanstddevandvarianceif you know your data is the full population, not a sample.covar_sampandcovar_pop: sample and population covariancecorr, Pearson's correlation coefficient
Order Statistics: Aggregates requiring ordered input¶
You'll notice that a number of handy statistics are missing from this list, including the median and quartiles. That's because those are order statistics: they are defined based on an ordering of the values in a column.
SQL provides for this by allowing what it calls "ordered set functions", which require a WITHIN GROUP (ORDER BY <columns>) clause to accompany the order-statistic aggregate. For example, to compute the 25th percentile, 50th percentile (median) and 75th percentile in SQL, we can use the following:
sql_expr = """
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY x)
FROM generate_series(1,10) AS data(x);
"""
pd.read_sql(sql_expr, engine)
There are two versions of the percentile function:
percentile_continuous : interpolatespercentile_discrete : returns an entry from the table
What will the following expressions return?
sql_expr = """
SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY x)
FROM generate_series(1,10) AS data(x);
"""
pd.read_sql(sql_expr, engine)
We can compute the edges and middle of the box in a box plot:
sql_expr = """
SELECT
percentile_disc(0.25) WITHIN GROUP (ORDER BY x) as lower_quartile,
percentile_disc(0.5) WITHIN GROUP (ORDER BY x) as median,
percentile_disc(0.75) WITHIN GROUP (ORDER BY x) as upper_quartile
FROM generate_series(1,10) AS data(x);
"""
pd.read_sql(sql_expr, engine)
Working with Real Data¶
To begin our exploration of real data we will work with the publicly-available campaign finance data from the Federal Election Commission in a PostgreSQL database.
And let's have a look at the individual table's schema:
inspector = sqlalchemy.inspect(engine)
pd.DataFrame(inspector.get_columns("individual")).set_index('name')
If you are curious about the meaning of these columns check out the FEC data description
How big is this table?
sql_expr = """
SELECT COUNT(*)
FROM individual
"""
pd.read_sql(sql_expr, engine)
Browsing Tables: LIMIT and sampling¶
This is not the first topic usually taught in SQL, but it's extremely useful for exploration.
OK, now we have some serious data loaded and we're ready to explore it.
Database tables are often big--hence the use of a database system. When browsing them at first, we may want to look at exemplary rows: e.g., an arbitrary number of rows, or a random sample of the rows.
To look at all of the data in the individual table, we would simply write:
select * from individual;
But that would return 20,347,829 rows into our Jupyter notebook's memory, and perhaps overflow the RAM in your computer. Instead, we could limit the size of the output to the first 3 rows as follows:
sql_expr = """
SELECT *
FROM individual
LIMIT 4;
"""
pd.read_sql(sql_expr, engine)
Some notes on the limit clause:¶
- Not only does it produce a small output, it's quite efficient: the database system stops iterating over the table after producing the first three rows, saving the work of examining the other nearly 40 million rows.
- Recall that relations have no intrinsic order, so this is some arbitrary choice of 3 rows. Two issues to keep in mind:
- This is a biased choice of rows. Very likely these are the first 3 rows stored in some disk file managed by the database, which may (for example) be the first 3 rows that were entered into the database, so they may not be representative of rows entered later.
- The result is non-deterministic. Given that tables are not guaranteed to have an intrinsic order, it is considered correct for an SQL engine to return any 3 rows that satisfy this query, and return a different 3 rows each time depending on the cached data.
Constructing a Bernoulli Sample¶
As data scientists, we should be concerned about spending much time looking at a biased subset of our data. Instead, we might want an i.i.d. random sample of the rows in the table. There are various methods for sampling from a table. A simple one built into many database systems including PostgreSQL is Bernoulli sampling, in which the decision to return each row is made randomly and independently. As a metaphor, the database engine "flips a coin" for each row to decide whether to return it. We can influence the sampling rate by choosing the probability of a "true" result of the coinflip.
This is done on a per-table basis in the FROM clause of the query like so:
sql_expr = """
SELECT *
FROM individual TABLESAMPLE BERNOULLI(.00001);
"""
pd.read_sql(sql_expr, engine)
To learn more about the TABLESAMPLE clause checkout out the select docs. Note that there is a second sampling method called block sampling which is a lot like cluster sampling at the level of pages on disk!
sql_expr = """
SELECT *
FROM individual TABLESAMPLE BERNOULLI(.00001) REPEATABLE(42);
"""
pd.read_sql(sql_expr, engine)
Three things to note relative to our previous limit construct:
- Bernoulli sampling is slow: it scales linearly with the table size by iterating through every row in the table.
- The number of rows returned by Bernoulli sampling is probabilistic. For a table with $n$ rows and a sampling probability $p$, the output size comes from a binomial distribution with mean $np$ and variance ($np(1-p)$). For a very small $p$, the variance means we could easily get 0 rows back when trying our query!
- If we don't know the size of the table, it's hard to choose a practical sampling probability. First we want to count up the number of rows $n$ (see the discussion of aggregation queries below), to inform us of a good $p$ to choose to get our desired output size. That means yet another full pass of the table to compute the count before we compute the sample!
For these reasons, if we want a proper i.i.d sample, it's a good idea to compute a nice-sized sample and store it, keeping it reasonably large for more general use. Since we will not be updating and rows in our individual table, we can do this without worrying that the sample will get "out of date" with respect to the context of individual.
We can use the CREATE TABLE AS SELECT ... (a.k.a. CTAS) pattern to do create a table that saves the output of a query:
DROP TABLE IF EXISTS indiv_sample;
CREATE TABLE indiv_sample AS
SELECT *
FROM individual TABLESAMPLE BERNOULLI(.1) REPEATABLE(42);
However, because we do not have write access to the database we will instead just select a few entires:
sql_expr = """
SELECT *
FROM individual TABLESAMPLE BERNOULLI(.1) REPEATABLE(42)
LIMIT 5;
"""
pd.read_sql(sql_expr, engine)
sql_expr = """
SELECT count(*)
FROM individual TABLESAMPLE BERNOULLI(.1) REPEATABLE(42);
"""
pd.read_sql(sql_expr, engine)
A SRS of Fixed Size¶
Here is an alternative method to construct a random sample of a fixed size. Note that this is not as efficient and will take several minutes to complete.
CREATE TABLE indiv_sample2 AS
SELECT *, RANDOM() AS u
FROM individual
ORDER BY u
LIMIT 20000;
sql_expr = """
SELECT *, RANDOM() AS u
FROM individual
ORDER BY u
LIMIT 5;
"""
pd.read_sql(sql_expr, engine)
Selecting rows and columns, and calling scalar (per-row) functions.¶
OK, we already had a peek at the individual table. Now let's look at specific attributes (columns) relates to who is donating how much.
In addition to referencing the columns of individual in the select clause, we can also derive new columns by writing field-level (so-called "scalar") functions. Typically we reference some table columns in those functions.
In our case, let's compute the log of transaction_amt for subsequent plotting. SQL comes with many typical functions you can use in this way, and PostgreSQL is particularly rich on this front; see the PostgreSQL manual for details.
We'll look at indiv_sample rather than individual while we're just exploring.
sql_expr = """
SELECT name, state, cmte_id,
transaction_amt, log(transaction_amt)
FROM indiv_sample
LIMIT 10;
"""
pd.read_sql(sql_expr, engine)
We can combine SQL with python in the following way:
sql_expr = """
SELECT transaction_amt AS amt
FROM indiv_sample
WHERE transaction_amt > 0;
"""
result = pd.read_sql(sql_expr, engine)
_ = sns.distplot(result['amt'])
sql_expr = """
SELECT LOG(transaction_amt) AS log_amt
FROM indiv_sample
WHERE transaction_amt > 0;
"""
result = pd.read_sql(sql_expr, engine)
df = result['log_amt']
sns.distplot(df.astype('float'))
scales = np.array([1, 10, 20, 100, 500, 1000, 5000])
_ = plt.xticks(np.log10(scales), scales)
Examining the Tail¶
sql_expr = """
SELECT transaction_amt AS amt
FROM indiv_sample
WHERE transaction_amt > 5000;
"""
result = pd.read_sql(sql_expr, engine)
_ = sns.distplot(result['amt'], rug=True)
sql_expr = """
SELECT transaction_amt AS amt
FROM individual
WHERE transaction_amt > 5000;
"""
result = pd.read_sql(sql_expr, engine)
_ = sns.distplot(result['amt'])
sql_expr = """
SELECT log(transaction_amt) AS log_amt
FROM individual
WHERE transaction_amt > 5000;
"""
result = pd.read_sql(sql_expr, engine)
sns.distplot(result['log_amt'])
scales = np.array([5000, 20000, 100000])
_ = plt.xticks(np.log10(scales), scales)
sql_expr = """
SELECT log(transaction_amt) AS log_amt
FROM individual
WHERE transaction_amt > 1000000;
"""
result = pd.read_sql(sql_expr, engine)
sns.distplot(result['log_amt'], rug=True)
scales = np.array([1000000, 5000000, 50000000])
_ = plt.xticks(np.log10(scales), scales)
CASE statements: SQL conditionals in the FROM clause¶
What about smaller donations?
sql_expr = """
SELECT name, state, cmte_id,
transaction_amt, LOG(transaction_amt)
FROM indiv_sample
WHERE transaction_amt < 10
LIMIT 10;
"""
try:
display(pd.read_sql(sql_expr, engine))
except Exception as e:
print(e)
Uh oh, log is not defined for numbers <= 0! We need a conditional statement in the select clause to decide what function to call. We can use SQL's case construct for that.
sql_expr = """
SELECT transaction_amt,
CASE WHEN transaction_amt > 0 THEN log(transaction_amt)
WHEN transaction_amt = 0 THEN 0
ELSE -1*(log(abs(transaction_amt)))
END AS log_amt
FROM indiv_sample
"""
try:
result = pd.read_sql(sql_expr, engine)
sns.distplot(result['log_amt'])
except Exception as e:
print(e)
Who donated the most?¶
sql_expr = """
SELECT transaction_amt, cmte_id, transaction_dt, name, city, state, memo_text, occupation
FROM individual
ORDER BY transaction_amt DESC
LIMIT 10
"""
pd.read_sql(sql_expr, engine)
Grouping Contributions by Name¶
sql_expr = """
SELECT name, SUM(transaction_amt) AS total_amt
FROM individual
GROUP BY name
ORDER BY total_amt DESC
LIMIT 10
"""
pd.read_sql(sql_expr, engine)
Going Local with WHERE¶
sql_expr = """
SELECT name, SUM(transaction_amt) AS total_amt
FROM individual
WHERE city = 'SAN FRANCISCO'
GROUP BY name
ORDER BY total_amt DESC
LIMIT 20;
"""
pd.read_sql(sql_expr, engine)
sql_expr = """
SELECT name, SUM(transaction_amt) AS total_amt
FROM individual
WHERE city = 'BERKELEY'
GROUP BY name
ORDER BY total_amt DESC
LIMIT 20;
"""
pd.read_sql(sql_expr, engine)
Named Queries: Views and CTEs¶
Up to now we've looked at a single query at a time. SQL also allows us to nest queries in various ways. In this section we look at the cleaner examples of how to do this in SQL: views and Common Table Expressions (CTEs).
Views¶
In earlier examples, we created new tables and populated them from the result of queries over stored tables. There are two main drawbacks of that approach that may concern us in some cases:
- The new table uses up storage, even though it is recomputable from other tables.
- Out of date. The stored output will not reflect changes in the input.
For this reason, SQL provides a notion of logical views: these are basically named queries that are re-evaluated upon each reference.
The syntax is straightforward:
CREATE VIEW <name> AS
<SELECT statement>;
The resulting view <name> can be used in an SELECT query, but not in an INSERT, DELETE or UPDATE query!
As an example, we might want a view that stores just some summary statistics of transaction_amts for each date:
try:
engine.execute("""
DROP VIEW IF EXISTS date_stats;
""")
engine.execute("""
CREATE VIEW date_stats AS
SELECT
transaction_dt AS day,
min(transaction_amt),
avg(transaction_amt),
stddev(transaction_amt),
max(transaction_amt)
FROM individual
GROUP BY transaction_dt
ORDER BY day;
""")
sql_expr = """
SELECT * from date_stats limit 5;
"""
display(pd.read_sql(sql_expr, engine))
except Exception as e:
print("In the shared Data 100 Postgres server you not allowed to create views.\n\n")
print(e)
Notice that this did not create a table:
inspector.get_table_names()
Instead it created a view: (if you are allowed to create views)
inspector.get_view_names()
Views are not materialized¶
Let's create a random table and we will even seed the random number generator.
DROP VIEW IF EXISTS rando;
SELECT setseed(0.3);
CREATE VIEW rando(rownum, rnd) AS
SELECT rownum, round(random())::INTEGER
FROM generate_series(1,50) AS ind(rownum)
What is the sum of the rows in Random:
sql_expr = """
SELECT SUM(rnd) FROM rando;
"""
pd.read_sql(sql_expr, engine)
What was that value again?
sql_expr = """
SELECT SUM(rnd) FROM rando;
"""
pd.read_sql(sql_expr, engine)
</br></br></br>
The value changes with each invocation.
Too Many Views¶
Views can help:
- Simplify queries
- Make complex queries more readable
- Share "sql programs" with others
Problem:
- Creating a new view for each (exploratory) query will result in a lot of views!
- views like:
temp1,temp1_joey,temp1_joey_fixed, ... - Often you will not be allowed to create views.
We need a mechanism to decompose query into views for the scope of a single query.
Common Table Expressions (WITH)¶
Think of these as a view that exists only during the query.
If we're only going to use a view within a single query, it is a little inelegant to CREATE it, and then have to DROP it later to recycle the view name.
Common Table Expressions (CTEs) are like views that we use on-the-fly. (If you know about lambdas in Python, you can think of CTEs as lambda views.) The syntax for CTEs is to use a WITH clause in front of the query:
WITH <name> [(renamed columns)] AS
(<SELECT statement>)
[, <name2> AS (<SELECT statement>)...]
If you need multiple CTEs, you separate them with commas. We can rewrite our query above without a view as follows:
sql_expr = """
WITH per_day_stats AS (
SELECT
to_date(transaction_dt, 'MMDDYYYY') as day, -- Date Parsing
min(transaction_amt),
avg(transaction_amt),
stddev(transaction_amt),
max(transaction_amt)
FROM indiv_sample
GROUP BY transaction_dt
)
SELECT day, stddev, max - min AS spread
FROM per_day_stats
WHERE stddev IS NOT NULL
ORDER by stddev DESC
LIMIT 5
"""
pd.read_sql(sql_expr, engine)
Joins¶
Suppose now we want to determine which committees received the most money
sql_expr = """
SELECT cmte_id, SUM(transaction_amt) AS total_amt
FROM individual
GROUP BY cmte_id
ORDER BY total_amt DESC
LIMIT 10
"""
pd.read_sql(sql_expr, engine)
What are the names of these Committees?¶
pd.DataFrame(inspector.get_columns("individual")).set_index('name')
pd.DataFrame(inspector.get_columns("cm")).set_index('name')
We can join the committee description to get the names of the committees that received the most funds.
sql_expr = """
WITH indv2cm AS
(
SELECT cmte_id, SUM(transaction_amt) AS total_amt
FROM individual
GROUP BY cmte_id
ORDER BY total_amt DESC
)
SELECT cm.cmte_nm, indv2cm.total_amt
FROM cm, indv2cm
WHERE cm.cmte_id = indv2cm.cmte_id
ORDER BY indv2cm.total_amt DESC
LIMIT 10
"""
pd.read_sql(sql_expr, engine)
Which candidate received the most¶
pd.DataFrame(inspector.get_columns("cn")).set_index('name')
Candidate Committee Linkage Table¶
pd.DataFrame(inspector.get_columns("ccl")).set_index('name')
Joining Aggregated Indiv - CCL - CN¶
sql_expr = """
SELECT cn.cand_name, SUM(indiv.transaction_amt) AS total_amt
FROM individual AS indiv, ccl, cn
WHERE indiv.cmte_id = ccl.cmte_id AND
ccl.cand_id = cn.cand_id
GROUP BY cn.cand_name
ORDER BY total_amt DESC
LIMIT 10
"""
pd.read_sql(sql_expr, engine)
Localized Join for CA¶
sql_expr = """
SELECT cn.cand_name, SUM(indiv.transaction_amt) AS total_amt
FROM individual AS indiv, ccl, cn
WHERE indiv.cmte_id = ccl.cmte_id AND
ccl.cand_id = cn.cand_id AND
indiv.state = 'CA'
GROUP BY cn.cand_name
ORDER BY total_amt DESC
LIMIT 10
"""
pd.read_sql(sql_expr, engine)
Localized Join for FL¶
sql_expr = """
SELECT cn.cand_name, SUM(indiv.transaction_amt) AS total_amt
FROM individual AS indiv, ccl, cn
WHERE indiv.cmte_id = ccl.cmte_id AND
ccl.cand_id = cn.cand_id AND
indiv.state = 'FL'
GROUP BY cn.cand_name
ORDER BY total_amt DESC
LIMIT 10
"""
pd.read_sql(sql_expr, engine)
Localized Join for TX¶
sql_expr = """
SELECT cn.cand_name, SUM(indiv.transaction_amt) AS total_amt
FROM individual AS indiv, ccl, cn
WHERE indiv.cmte_id = ccl.cmte_id AND
ccl.cand_id = cn.cand_id AND
indiv.state = 'TX'
GROUP BY cn.cand_name
ORDER BY total_amt DESC
LIMIT 10
"""
pd.read_sql(sql_expr, engine)
Tracking Direct Committee Contributions¶
sql_expr = """
SELECT cm.cmte_nm, SUM(transaction_amt) AS total_amt
FROM pas, cm
WHERE pas.cmte_id = cm.cmte_id
GROUP BY cm.cmte_nm
ORDER BY total_amt DESC
LIMIT 5
"""
pd.read_sql(sql_expr, engine)
sql_expr = """
SELECT cn.cand_name, SUM(transaction_amt) AS total_amt
FROM pas, cn
WHERE pas.cand_id = cn.cand_id
GROUP BY cn.cand_name
ORDER BY total_amt DESC
LIMIT 5
"""
pd.read_sql(sql_expr, engine)