forked from CodeSmithDSMLProjects/SteamRecommender
-
Notifications
You must be signed in to change notification settings - Fork 0
/
sqlalchemy-RDS.py
54 lines (41 loc) · 1.31 KB
/
sqlalchemy-RDS.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
#!/usr/bin/env python
# coding: utf-8
from keys import secrets
import pandas as pd
from sqlalchemy import URL, create_engine
# Create postgres url_object for engine
url_object = URL.create(
"postgresql+psycopg2",
host='steam-db.c5m99euxia00.us-east-1.rds.amazonaws.com',
username=secrets.get('user'),
port=5432,
password=secrets.get('password'),
database='steam_db')
engine = create_engine(url_object)
engine.connect()
steam_path = 'data/steam.csv'
tags_path = 'data/tags.csv'
# Read in csv files
steam = pd.read_csv(steam_path).set_index('title')
tags = pd.read_csv(tags_path, names= ['id', 'genre']).set_index('id')
# Drop existing table if needed
connection = engine.raw_connection()
cursor = connection.cursor()
command = "DROP TABLE IF EXISTS {};".format('steam')
cursor.execute(command)
connection.commit()
cursor.close()
# Convert steam df to sql db
steam.to_sql(name='steam', con=engine, if_exists='replace')
# Convert tags df to sql db
tags.to_sql(name='tags', con=engine, if_exists='replace')
# Read from table steam
sql = """
SELECT * FROM steam
"""
pd.read_sql(sql, con=connection)
# Read from table tags
sql = """
SELECT * FROM tags
"""
pd.read_sql(sql, con=connection)