<jupyter_start><jupyter_text>Test the SQLite Load - File is Uploaded Locally in Colab<jupyter_code>!pip install psycopg2-binary
# Imports
import sqlite3
# Queries
query1 = 'SELECT COUNT(character_id) \
          FROM charactercreator_character;'

# File is uploaded to Colab
conn = sqlite3.connect('rpg_db.sqlite3')

# Execute queries
curs1 = conn.cursor()
print('Total characters =', curs1.execute(query1).fetchall())
# Close cursors and commit
curs1.close()
conn.commit()<jupyter_output><empty_output><jupyter_text>Insert the RPG Data Into PostgreSQL<jupyter_code># Imports
import pandas as pd
from sqlalchemy import create_engine
import psycopg2
# Connect to sqlite RPG DB
sl_conn =sqlite3.connect('rpg_db.sqlite3')
sl_cur = sl_conn.cursor()
# Test the Connection
sl_cur.execute('SELECT * FROM charactercreator_character')
sl_cc_table = sl_cur.fetchall()
sl_cc_table[0]
# Create Postegres Engine
db_string = 'postgres://wsatpgnz:<EMAIL>:5432/wsatpgnz'
engine = create_engine(db_string)
# Connnect to Postgres DB
pg_conn = engine.connect()
# Convert charactercreator_character to Dataframe
df = pd.read_sql('SELECT * FROM charactercreator_character', sl_conn)
df
#pg_conn.execute('DROP TABLE "public"."charactercreator_character"')
# Store the Dataframe in the Postgres DB
table_names_dict = {'charactercreator_character': 'character_id'}

for table_name, primary_key in table_names_dict.items():
  df = pd.read_sql(f"SELECT * FROM {table_name}", sl_conn)
  df = df.set_index(primary_key, verify_integrity=True)
  df.to_sql(table_name, pg_conn)
# Close cursors and commit
sl_cur.close()
sl_conn.commit()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10336391437308869, 'fraction_numerical': 0.010397553516819572, 'mean_word_length': 6.827751196172249, 'pattern_counts': {'":': 0, '<': 10, '<?xml version=': 0, '>': 10, 'https://': 0, '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': '479902', 'license': 'MIT', 'n_tokens_mistral': 580, 'n_tokens_neox': 562, 'n_words': 132, 'path': 'module2-sql-for-analysis/Module2_Assignment.ipynb', 'repo_name': 'JimKing100/DS-Unit-3-Sprint-2-SQL-and-Databases', 'size': '1684'}