<jupyter_start><jupyter_text>Convert CSV into DataFrame format<jupyter_code>#Convert CSV File W/ Data into dataframe, make sure you have the correct file and in the proper csv format
df = pd.read_csv("predict_df.csv")
# df = pd.read_csv("main_data_clean.csv")
#Data frame looks good to go
print(df.shape)
df.head()<jupyter_output>(1472, 7)<jupyter_text>Clean Columns If Necessary  Check Secrets<jupyter_code>from google.colab import files
uploaded = files.upload()
# from google.colab import drive
# drive.mount('/content/drive')
#Secrets are contained in a .envfile
load_dotenv()
db_name = os.getenv("DB_NAME")
db_user = os.getenv("DB_USER")
db_password = os.getenv("DB_PASSWORD")
db_host = os.getenv("DB_HOST")
db_port = os.getenv("DB_PORT")

# print(db_name)
# print(db_user)
# print(db_password)
# print(db_host)
# print(db_port)<jupyter_output><empty_output><jupyter_text>Verify which tables exist in DataBase<jupyter_code>import sqlalchemy  # Package for accessing SQL databases via Python
# Connect to database (Note: The package psychopg2 is required for Postgres to work with SQLAlchemy)
engine = sqlalchemy.create_engine(f"postgresql://{db_user}:{db_password}@{db_host}:{db_port}/{db_name}")
con = engine.connect()
# Verify that there are no existing tables
print(engine.table_names())
engine.dispose()<jupyter_output>['b2p_lab28d_sk_tab']<jupyter_text>Upload DataFrame to DataBase<jupyter_code>table_name = 'predict_sk_tab'
# table_name = 'b2p_lab28d_sk_tab'

df.to_sql(table_name, con)
def conn_curs():
    """
    makes a connection to the database
    """
    global db_name
    global db_user
    global db_password
    global db_host
    global db_port
    
    connection = psycopg2.connect(dbname=db_name, user= db_user,
                                  password=<PASSWORD>, host= db_host,port=db_port)
    cursor = connection.cursor()
    return connection, cursor
def fetch_query_records(query):
    global conn_curs
    conn, cursor = conn_curs()
    cursor.execute(query)
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    return result
fetch_query_records("""SELECT "bridge_name" from public."B2P_Merged_Final" LIMIT 1;""")
# Testing Querie to get Records based on Bridge Name
conn, cursor = conn_curs()
query  = f"""SELECT country from {table_name} where bridge_name = 'Bukinga' LIMIT 1;"""
cursor.execute(query)
result = cursor.fetchall()
cursor.close()
conn.close
print(result)
def fetch_all_records():
    global conn_curs
    conn,cursor = conn_curs()
    query = f""" SELECT * from public.{table_name} LIMIT 1""";
    cursor.execute(query)
    result = cursor.fetchall() #when it fetches it adds an extra column (indexes), make sure to remove it
    conn.close()
    df2 = pd.DataFrame(result, columns= ['index']+columns)
    df2 = df2.iloc[:,1:] # This code removes extra copied indexed column
    df_json = df.to_json(orient='records')
    parsed = json.loads(df_json)
    return df2

#Testing fetch_all_records function
df2 = fetch_all_records()
print(df2.shape)
df2.head()
df.columns<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11281382458428432, 'fraction_numerical': 0.008151287903488751, 'mean_word_length': 4.968871595330739, 'pattern_counts': {'":': 0, '<': 16, '<?xml version=': 0, '>': 16, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '294126', 'license': 'MIT', 'n_tokens_mistral': 1056, 'n_tokens_neox': 1040, 'n_words': 259, 'path': 'notebooks/b2p_d_DB.ipynb', 'repo_name': 'JonRivera/bridges-to-prosperity-ds-d', 'size': '3068'}