<jupyter_start><jupyter_text>PostgreSQL & WRDSby <NAME>- Part of Mini Python Sessions: [github.com/drliangjin/minipy](https://github.com/drliangjin/minipy)- Official Guide/Documentation of PostgreSQL: [www.postgresql.org/docs](https://www.postgresql.org/docs/current/tutorial.html)- WRDS Python Connection: [wrds-www.wharton.upenn.edu](https://wrds-www.wharton.upenn.edu/pages/support/programming-wrds/programming-python/python-from-your-computer/)  Connect WRDS  Initial Setup<jupyter_code># Install WRDS and associated modules necessary using pip
try:
    import wrds
except ImportError:
    !pip install wrds
# Create a connection to WRDS server
# You will be asked for your username and passwords

conn = wrds.Connection()

# Optionally, you can create pgpass file to store your passwords locally
# conn.create_pgpass_file()<jupyter_output>/usr/local/miniconda3/envs/minipy/lib/python3.6/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.
  """)<jupyter_text>WRDS data Workflow  Datasets Overview<jupyter_code>import random
# get all WRDS databases/libraries/Schema
libraries = conn.list_libraries()

random.sample(libraries, 10)
# get all tables stores in a specific library
library = "crsp"

tables = conn.list_tables(library = library)

random.sample(tables, 10)
# determine the column headers within a given dataset
library = "crsp"

table = "dse"

conn.describe_table(library = library, table  = table).head()<jupyter_output>Approximately 11216100 rows in crsp.dse.<jupyter_text>Peek into a dataset<jupyter_code># get_table approach
# limit our queries
library = "crsp"
table = "dsf"
columns = ["cusip, permno, permco, date, prc, openprc, bid, bidlo, ask, askhi, vol, ret, retx"]
obs = 20

# retrieve the whole dataset, without modification/manipulation
conn.get_table(library = library, table = table, columns = columns, obs = obs)<jupyter_output><empty_output><jupyter_text>SQL & PostgreSQL SQL, or Structured Query Language, is a language designed to allow both technical and non-technical users:- query data- manipulate data- transform datafrom relational databases. PostgreSQL is arguably the best open-source relational database, others include MySQL, Oracle and Microsoft SQL server.  Basic syntax (1):- `SELECT`: retrieve data- `FROM`: from a table using a format of **library.table**- `LIMIT`: limit observations, or we will stuck with a large data table for a very very long time...<jupyter_code># SQL quary statment
stmt = """
SELECT * 
FROM crsp.dsf 
LIMIT 10
"""

# Connect to WRDS PostgreSQL databases
conn.raw_sql(stmt)<jupyter_output><empty_output><jupyter_text>Basic syntax (2):- `*`: all columns in a table- alternative: we can pick the columns we want<jupyter_code># SQL quary statment
stmt = """
SELECT cusip, permno, permco, date, prc, openprc, bid, bidlo, ask, askhi, vol, ret, retx
FROM crsp.dsf
LIMIT 10
"""
# Connect to WRDS PostgreSQL databases
data = conn.raw_sql(stmt)
# data overview
data.info()
# head()
data.head()<jupyter_output><empty_output><jupyter_text>Basic syntax (3):- `WHERE`: we can send queries with constrains- `AND/OR`: more conditions- `NULL`: select query with null or non-null value- `=, !=, , >=`: standard numerical operators- `BETWEEN...AND...`: number is within range of two values (inclusive)- `IN`: number exists in a list- `NOT`: not...<jupyter_code># SQL quary statment
stmt = """
SELECT cusip, permno, permco, date, prc, openprc, bid, bidlo, ask, askhi, vol, ret, retx
FROM crsp.dsf
WHERE permno = 14593
"""
# Connect to WRDS PostgreSQL databases
conn.raw_sql(stmt).head()<jupyter_output><empty_output><jupyter_text>Task Set 1:1. Find the data with permno: permno: 14593 and 10107 (Apple and Microsoft)2. Find the above data with date between 01/01/2000 and 31/12/2018, 3. Find the above data with returns higher than 10%<jupyter_code># Your codes start HERE #

# Your codes end HERE #<jupyter_output><empty_output><jupyter_text>Basic syntax (4):- `ORDER BY`: select query with ordered results- `LIMIT` and `OFFSET`(optional): select query with limited results<jupyter_code># SQL quary statment
stmt = """
SELECT cusip, permno, permco, date, prc, openprc, bid, bidlo, ask, askhi, vol, ret, retx
FROM crsp.dsf
ORDER BY cusip DESC
LIMIT 100 OFFSET 10
"""

# Connect to WRDS PostgreSQL databases
conn.raw_sql(stmt).head()<jupyter_output><empty_output><jupyter_text>Intermedia Syntax (1)- `INNER JOIN`: generates only records matched on both/all tables- `LEFT JOIN` and `RIGHT JOIN`: retains all records from **left** or **right** table and merge with new columns- `FULL JOIN`: results in a large data, combining both/all tables<jupyter_code># SQL quary statment
dsf = """
SELECT permno, date, prc, ret
FROM crsp.dsf
WHERE permno = 14593
ORDER BY date DESC
LIMIT 10
"""

# Connect to WRDS PostgreSQL databases
conn.raw_sql(dsf).head()
# SQL quary statment
dse = """
SELECT permno, date, event
FROM crsp.dse
WHERE permno = 14593
ORDER BY date DESC
LIMIT 10
"""

# Connect to WRDS PostgreSQL databases
conn.raw_sql(dse).head()
# SQL quary statment
join = """
SELECT crsp.dsf.permno, crsp.dsf.date, crsp.dsf.prc, crsp.dsf.ret, crsp.dse.event
FROM crsp.dsf
INNER JOIN crsp.dse
ON crsp.dsf.permno = crsp.dse.permno
AND crsp.dsf.date =  crsp.dse.date
WHERE crsp.dsf.permno = 14593
ORDER BY date DESC
LIMIT 10
"""

# Connect to WRDS PostgreSQL databases
conn.raw_sql(join).head()<jupyter_output><empty_output><jupyter_text>Task Set 2:1. Try `LEFT JOIN`2. Try `RIGHT JOIN`3. Try `FULL JOIN`  Intermedia Syntax (2):- `AS`: regular columns and even tables can have aliases to make them easier to reference<jupyter_code># SQL quary statment
join = """
SELECT a.permno, a.date, a.prc AS price, a.ret * 100 AS return, b.event
FROM crsp.dsf AS a
INNER JOIN crsp.dse AS b
ON a.permno = b.permno
AND a.date =  b.date
WHERE a.permno = 14593
ORDER BY a.date DESC
LIMIT 10
"""

# Connect to WRDS PostgreSQL databases
conn.raw_sql(join).head()<jupyter_output><empty_output><jupyter_text>Intermedia Syntax (3):- Aggregation Functions (e.g., `AVG`, `MAX` and so on): aggregate expressions that allow us to summarize information about a group of rows of data- `DISTINCT`: remove duplicates- `GROUP BY`: specify individual groups- `HAVING`: a `WHERE` condition for the `GROUP BY` clause to filter grouped rows from the result set<jupyter_code># Demo

# SQL quary statment
stmt = """
SELECT permno, MAX(prc) AS max_prc, AVG(ret) AS avg_ret
FROM crsp.dsf
WHERE permno IN (14593, 10107)
GROUP BY permno
"""

# Connect to WRDS PostgreSQL databases
conn.raw_sql(stmt)<jupyter_output><empty_output><jupyter_text>Advanced Features:- Sub-queries: nest queries to allow complicated dataset transformation (for example, `FROM (SELECT * FROM...)`)- Window Functions: perform a comprehensive calculation across a set of table rows- Control Structures: perform complicated data manipulations (`CASE...WHEN...END CASE`, `IF...THEN...ELSE...END IF`)<jupyter_code># SQL quary statment
stmt = """
SELECT permno, ret, 
AVG(ret) OVER (PARTITION BY permno) AS avg_ret, 
ret - AVG(ret) OVER (PARTITION BY permno) AS abnorm_ret
FROM crsp.dsf
WHERE permno IN (14593, 10107)
"""

# Connect to WRDS PostgreSQL databases
conn.raw_sql(stmt).head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11120086160473883, 'fraction_numerical': 0.017905223478729134, 'mean_word_length': 5.195996663886572, 'pattern_counts': {'":': 0, '<': 49, '<?xml version=': 0, '>': 50, 'https://': 3, 'lorem ipsum': 0, 'www.': 4, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '165036', 'license': 'MIT', 'n_tokens_mistral': 2636, 'n_tokens_neox': 2453, 'n_words': 837, 'path': 'notebooks/Session_3/Episode_2/minipy_sql.ipynb', 'repo_name': 'drliangjin/little-python-book', 'size': '7434'}