<jupyter_start><jupyter_text>Postgres Demo Notes<jupyter_code>!pip install psycopg2-binary
import psycopg2
dir(psycopg2)
help(psycopg2.connect)
dbname = 'mptmpvfs'

user = 'mptmpvfs'

password = '<PASSWORD>'

host = 'salt.db.elephantsql.com'
pg_conn = psycopg2.connect(dbname = dbname, user = user, password = password, host = host )
pg_curs = pg_conn.cursor()
pg_curs.execute('SELECT * FROM test_table;')
pg_curs.fetchall()
!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
import sqlite3
sl_conn = sqlite3.connect('rpg_db.sqlite3')
sl_curs = sl_conn.cursor()
count = sl_curs.execute('SELECT COUNT(*) FROM charactercreator_character;').fetchall()
count
characters = sl_curs.execute('SELECT * from charactercreator_character;').fetchall()
characters[-1]
len(characters)
pg_curs
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
);
'''


pg_curs.execute(create_character_table)
show_tables = '''
SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname != 'pg_catalog'
AND schemaname != 'information_schema';
'''
pg_curs.execute(show_tables)
pg_curs.fetchall()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12697274031563846, 'fraction_numerical': 0.012195121951219513, 'mean_word_length': 6.459893048128342, 'pattern_counts': {'":': 0, '<': 6, '<?xml version=': 0, '>': 6, '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': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '215455', 'license': 'MIT', 'n_tokens_mistral': 528, 'n_tokens_neox': 512, 'n_words': 93, 'path': 'module2-sql-for-analysis/322_Notes.ipynb', 'repo_name': 'mikedcurry/DS-Unit-3-Sprint-2-SQL-and-Databases', 'size': '1416'}