Unable to create table in Amazon redshift using Psycopg2

Question

I am trying to make a simple script in python, which will fetch data from an endpoint convert it into a dataframe and write it to an Amazon redshift cluster and then automate the script using a cronjob from aws. I am using psycopg2 for connecting to the redshift cluster and the script executes the commands pretty well (creates table in redshift and writes the data as well). But when I try to see the table from a sql client the table doesnt show up

from pandas.io.json import json_normalize
import json
import pandas as pd
import requests
import psycopg2
mm_get = requests.get('endpoint link'})
mm_json=mm_get.json()
data_1 = json_normalize(data         = mm_json['data'],
                       record_path   = ['courses','modules'],
                       record_prefix = 'courses.modules.',
                       meta          = [['courses', 'id'], 
                                        ['courses', 'title'],
                                        'activated', 
                                        'createdAt', 
                                        'email', 
                                        'employeeId', 
                                        'firstName', 
                                        'group', 
                                        'id', 
                                        'lastName', 
                                        'phone', 
                                        'teams'
                                       ]
                       )
data_2 = json_normalize(data         = mm_json['data'],
                       record_path   = 'lessons',
                       record_prefix = 'lessons.',
                       meta          = 'id',
                       meta_prefix   = 'user.'
                       )
data_3 = data_1.merge(
    data_2,
    how = 'outer', 
    left_on = ['courses.modules.id', 'id'],
    right_on = ['lessons.moduleId', 'user.id']
)

cols = data_3.columns
cols = cols.tolist()
cols = pd.DataFrame(cols)
re_cols = pd.DataFrame(cols.loc[:,0].str.replace('.','_').tolist(),index=cols.index)
data_3.teams = data_3.teams.astype(str)
data_3.teams = data_3.teams.str.replace('[','')
data_3.teams = data_3.teams.str.replace(']','')
data_3.teams = data_3.teams.str.replace("'","")

con=psycopg2.connect(dbname='name',
                     host='hostname',
                     port='xxxx',user='username',password='password')
cur = con.cursor()
cur.execute('create table testing_learn.test (courses_modules_completionDate DATE, courses_modules_id int,  courses_modules_status TEXT,courses_modules_title TEXT, courses_id int,courses_title TEXT, activated bool, createdAt TIMESTAMP, email TEXT, employeeId TEXT, firstName TEXT, group_name TEXT, id TEXT, lastname TEXT, phone int8, teams TEXT, lessons_courseId int, lessons_date DATE, lessons_id int, lessons_lessonNumber int, lessons_moduleId int,lessons_score TEXT, lessons_title TEXT,user_id int);')
cur.close()
data_mat = data_3.as_matrix()
str_mat = b','.join(cur.mogrify('(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',x) for x in tuple(map(tuple,data_mat)))
cur = con.cursor()
cur.execute('insert into testing_learn.test VALUES '+str_mat.decode('utf-8'))

I am able to see the data when I query the same table from python using psycopg2, but the same table doesnt show up. It would be of great help if anyone could help with what I am doing wrong here. Thank in advance.

Answer 1

According to Psycopg2-2.7.5 official documentation, the main entry points of Psycopg2 includes:

• The class connection encapsulates a database session. It allows to:
  • create new cursor instances using the cursor() method to execute database commands and queries,
  • terminate transactions using the methods commit() or rollback().

Therefore, you need to call con.commit() every time after you call cur.execute() to make the changes to the database persistent. Otherwise your table won't show up in the database.