4TueQq6D

1#grab and import data from the redshift cluster, this may be changed to grab a datanet job
2import psycopg2  ## this is  driver for interacting with PostgreSQL from the Python scripting language
3from getpass import getpass
4from pandas import read_sql
5
6#get_ipython().system(u'pip install psycopg2')
7
8config = { 'dbname': 'hr_finance_csvcs', 
9           'user':'ravitejr',
10           'pwd':'Mahimsd12#',
11           'host':'hroa-finance.ch519wphpjjd.us-east-1.redshift.amazonaws.com',
12           'port':'8192'
13         }
14
15def create_conn(*args,**kwargs):
16    config = kwargs['config']
17    try:
18        con=psycopg2.connect(dbname=config['dbname'], host=config['host'], 
19                              port=config['port'], user=config['user'], 
20                              password=config['pwd'])
21        return con
22    except Exception as err:
23        print(err)
24
25
26con = create_conn(config=config)
27df_test=read_sql("select * from hr_finance_csvcs.cs_skill_key limit 25",con=con)
28
29con2 = create_conn(config=config)
30df_test=read_sql("select * from hr_finance_csvcs.cs_skill_key limit 25",con=con2)
31
32
33
34
35##############################################################################################################################################################  Pushing to SQL
36
37from sqlalchemy.engine import create_engine
38engine = create_engine('postgresql+psycopg2://lizcook:pWD1@345@hroa-finance.ch519wphpjjd.us-east-1.redshift.amazonaws.com:8192/hr_finance_csvcs')
39connection = engine.connect()
40connection.execute(
41    """
42DELETE FROM hr_dev_econ.ff_text;
43commit;
44    """
45)
46
47conn = create_engine('postgresql+psycopg2://lizcook:pWD1@345@hroa-finance.ch519wphpjjd.us-east-1.redshift.amazonaws.com:8192/hr_finance_csvcs')
48
49df = ff_text
50
51df.to_sql('ff_text', conn, index = False, if_exists = 'append',schema='hr_dev_econ')
52
53#### 'ff_text' is the name of the table
54#### df is the table we are pushing
55#### if_exists : {‘fail’, ‘replace’, ‘append’}, default ‘fail’
56    #fail: If table exists, do nothing.
57    #replace: If table exists, drop it, recreate it, and insert data.
58    #append: If table exists, insert data. Create if does not exist.