<jupyter_start><jupyter_text>Live Demo Code for Connecting to PostgreSQL from PythonNotebook for your helpful reference - but the assignment still requires writing .py files!<jupyter_code># We want to connect to our PostgreSQL database
# Step 1 - have the library psycopg2 installed
# Locally: pipenv install psycopg2-binary
!pip install psycopg2-binary
import psycopg2
dir(psycopg2)
help(psycopg2.connect)
# Looks similar to sqlite3, but needs auth/host info to connect
# Note - this is sensitive info (particularly password)
# and shouldn't be checked into git! More on how to handle next week

dbname = 'qaxlnjzu'
user = 'qaxlnjzu'  # ElephantSQL happens to use same name for db and user
password = '<PASSWORD>'  # Sensitive! Don't share/commit
host = 'isilo.db.elephantsql.com'
# If we make too many connections, the database complains! Be sure to close
# cursors and connections
pg_conn = psycopg2.connect(dbname=dbname, user=user,
                           password=password, host=host)
pg_conn
dir(pg_conn)
pg_curs = pg_conn.cursor()  # Works the same as SQLite!
help(pg_curs.execute)
help(pg_curs.executemany)  # And more functionality!
# We're connected, but db is empty
# Let's run a simple example to populate (from the tk)
create_table_statement = """
CREATE TABLE test_table (
  id SERIAL PRIMARY KEY,
  name varchar(40) NOT NULL,
  data JSONB
);
"""
# NOTE - these types are PostgreSQL specific. This won't work in SQLite!

pg_curs.execute(create_table_statement)
pg_conn.commit()  # "Save" by committing
# We're connected, let's see what is in the db
pg_curs.execute('SELECT * FROM test_table;')
pg_curs.fetchall()
insert_statement = """
INSERT INTO test_table (name, data) VALUES
(
  '<NAME>',
  '{"key": "value", "key2": true}'::JSONB
)
"""
pg_curs.execute(insert_statement)
pg_conn.commit()
pg_curs.execute('SELECT * FROM test_table;')
pg_curs.fetchall()
pg_curs.close()
# pg_conn.close()  # If we were really done
# Database constraints from the schema are enforced!
# This is good - helps ensure data quality
pg_curs = pg_conn.cursor()
pg_curs.execute('INSERT INTO test_table (name, data) VALUES (null, null);');<jupyter_output><empty_output><jupyter_text>ETL!Extract - Transform - LoadExtract: get the data out from a source (often the original "source of truth")Transform: tweak/change data as appropriate for use case, and to make it fit in...Load: Insert data into desired destinationWe'd like to get the RPG data out (extract) of SQLite and insert it into (load) PostgreSQL.We may have to tweak it a little (transform), but probably not too much, since both source and destination are SQL databases.We're making our first "cloud" ETL!<jupyter_code>!wget https://github.com/LambdaSchool/DS-Unit-3-Sprint-2-SQL-and-Databases/blob/master/module1-introduction-to-sql/rpg_db.sqlite3?raw=true
!mv 'rpg_db.sqlite3?raw=true' rpg_db.sqlite3
!ls
# Step 1 - Extract, get data out of SQLite3
# Let's focus on character data, i.e. charactercreator_character
import sqlite3
sl_conn = sqlite3.connect('rpg_db.sqlite3')
sl_curs = sl_conn.cursor()
get_characters = "SELECT * FROM charactercreator_character;"
sl_curs.execute(get_characters)
characters = sl_curs.fetchall()
len(characters)
characters[:5]
# Step 1 complete! We have a list of tuples with all our character data
# NOTE - this is *not* a pandas dataframe
# We don't know types - so, for "Transform" we need to figure that out
# Because our destination (PostgreSQL) needs a schema for this data

# Step 2 - Transform
# Our goal is to make a schema to define a table that fits this data in Postgres
# We can check the old schema!
# This is an internal meta sort of query, will vary by database flavor
sl_curs.execute('PRAGMA table_info(charactercreator_character);')
sl_curs.fetchall()
# A bunch of integers, and a varchar
# We need to make a create statement for PostgreSQL that captures this
create_character_table = """
CREATE TABLE charactercreator_character (
  character_id SERIAL PRIMARY KEY,
  name VARCHAR(30),
  level INT,
  exp INT,
  hp INT,
  strength INT,
  intelligence INT,
  dexterity INT,
  wisdom INT
);
"""
# Defining a function to refresh connection and cursor
def refresh_connection_and_cursor(conn, curs):
  curs.close()
  conn.close()
  pg_conn = psycopg2.connect(dbname=dbname, user=user,
                             password=password, host=host)
  pg_curs = pg_conn.cursor()
  return pg_conn, pg_curs
pg_conn, pg_curs = refresh_connection_and_cursor(pg_conn, pg_curs)
# Execute the create table
pg_curs.execute(create_character_table)
pg_conn.commit()
# PostgreSQL comparison to the SQLite pragma
# We can query tables if we want to check
# This is a clever optional thing, showing postgresql internals
show_tables = """
SELECT
   *
FROM
   pg_catalog.pg_tables
WHERE
   schemaname != 'pg_catalog'
AND schemaname != 'information_schema';
"""
pg_curs.execute(show_tables)
pg_curs.fetchall()
# Done with step 2 (transform)
# We didn't really change the data, just made sure we could fit it in our target
# Step 3 - Load!
characters[0]
# We want to put this tuple in a string w/INSERT INTO...
# But we don't want the first field (id) - PostgreSQL generates that
characters[0][1:]
example_insert = """
INSERT INTO charactercreator_character
(name, level, exp, hp, strength, intelligence, dexterity, wisdom)
VALUES """ + str(characters[0][1:]) + ";"

print(example_insert)  # Not running, just inspecting
# If we ran this, we'd insert the first character
# But we want them all - loops!
for character in characters:
  insert_character = """
    INSERT INTO charactercreator_character
    (name, level, exp, hp, strength, intelligence, dexterity, wisdom)
    VALUES """ + str(character[1:]) + ";"
  pg_curs.execute(insert_character)

# Note - we're executing each character one at a time
# That works, and is simple, but inefficient (lots of roundtrips to database)
# Stretch/afternoon goal - see if you can combine into a single
# insert that does them all at once
pg_conn.commit()
# Let's look at what we've done
pg_curs.execute('SELECT * FROM charactercreator_character LIMIT 5;')
pg_curs.fetchall()
# Ids are different (on first run, now fixed)!
# That's because we had an aborted run
# Let's fix this by deleting the data and DROPping the table
# Other tables are fine, but we'll dump the data *and* schema to rerun
# pg_curs.execute('DROP TABLE charactercreator_character;')
# pg_conn.commit()
# Now we need to rerun the above... scrolling up and down, because notebooks
# Specifically rerunning character table create statement and data inserts
# Now the data looks the same! But let's check it systematically
pg_curs.execute('SELECT * FROM charactercreator_character;')
pg_characters = pg_curs.fetchall()
# We could do more spot checks, but let's loop and check them all
# TODO/afternoon task - consider making this a more formal test
for character, pg_character in zip(characters, pg_characters):
  assert character == pg_character
# No complaints - which means they're all the same!
# Closing out cursor/connection to wrap up
pg_curs.close()
pg_conn.close()
sl_curs.close()
sl_conn.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09016853932584269, 'fraction_numerical': 0.0053370786516853935, 'mean_word_length': 4.880264244426094, 'pattern_counts': {'":': 2, '<': 11, '<?xml version=': 0, '>': 11, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, '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': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '228904', 'license': 'MIT', 'n_tokens_mistral': 2243, 'n_tokens_neox': 2158, 'n_words': 831, 'path': 'module2-sql-for-analysis/Unit_3_Sprint_2_Module_2_PostgreSQL_Demo.ipynb', 'repo_name': 'Malven2019/DS-Unit-3-Sprint-2-SQL-and-Databases', 'size': '7153'}