尝试使用Python和psycopg2创建Redshift表，但未创建该表且未报告任何错误 [英] Trying to create a Redshift table using Python and psycopg2 but the table does not get created with no errors reported

问题描述

我的代码没有返回任何错误，但是在Redshift中没有看到一个表...如果我输入"if table exist"并尝试创建一个表，我知道该表不起作用也不返回任何错误.取出它，它会返回可重复的错误，这很奇怪.

My codes return no error but I don't see a table in Redshift...if I put "if table exist" and try to create a table I know exists it does nothing and returns no error. Take that out and it will return duplicatetable error which is odd.

import boto3
import psycopg2
import sys

#Assign global variables data needed to make connection to Redshift
DB_NAME = '<database>'
CLUSTER_IDENTIFIER = '<clusterName>'
DB_USER = '<user>'
ENDPOINT = '<clustername>.<randomkey>.us-east-1.redshift.amazonaws.com'
REGION = 'us-east-1'

sql = "CREATE TABLE if not exists " + "<schema>.<tablename> " + \
      "( vendorid varchar(4), pickup_datetime TIMESTAMP, " + \
      "dropoff_datetime TIMESTAMP, store_and_fwd_flag varchar(1), " + \
      "ratecode int, pickup_longitude float(4), pickup_latitude float(4)," + \
      "dropoff_logitude float(4), dropoff_latitude float(4), " + \
      "passenger_count int, trip_distance float(40), fare_amount float(4), " + \
      "extra float(4), mta_tax float(4), tip_amount float(4), " + \
      "tolls_amount float(4), ehail_fee float(4), improvement_surcharge float(4), " + \
      "total_amount float(4), payment_type varchar(4), trip_type varchar(4))  " + \
      "DISTSTYLE EVEN SORTKEY (passenger_count, pickup_datetime);"

try:
    #make redshift connection
    client = boto3.client('redshift', region_name='us-east-1')

    #get temporary username and password
    cluster_creds = client.get_cluster_credentials(DbUser=DB_USER, DbName=DB_NAME, ClusterIdentifier=CLUSTER_IDENTIFIER, AutoCreate=False)
    temp_user = cluster_creds['DbUser']
    temp_pswd = cluster_creds['DbPassword']

    #create connection string to database
    conn = psycopg2.connect(f"host='{ENDPOINT}' port='5439' user={temp_user} password={temp_pswd} dbname='{DB_NAME}'")

    #Attempt to create table
    cursor = conn.cursor()
    cursor.execute(sql)
    conn.commit
    cursor.close()
    conn.close()

    #report any errors
except Exception as ex:
    print("Exception name : " + ex.__class__.__name__)
    print(str(ex))
    print("Failed to open connection to Redshift database")
    sys.exit(1)

推荐答案

我的代码没有返回任何错误，但是在Redshift中没有看到一个表...如果我输入"if table exist"并尝试创建一个表，我知道该表不起作用也不返回任何错误.取出它，它将返回可重复的错误，这很奇怪.

My codes return no error but I don't see a table in Redshift...if I put "if table exist" and try to create a table I know exists it does nothing and returns no error. Take that out and it will return duplicatetable error which is odd.

code或redshift没有问题.无论发生什么事情，都是可以预期的.

There is no problem with code or redshift. Whatever is happening is exactly expected.

如果我放置如果表存在"并尝试创建一个我知道存在的表，则它什么都不做，并且不返回错误

if I put "if table exist" and try to create a table I know exists it does nothing and returns no error

这是Redshift 文档所预期的.没错.以下是if not exist的文档摘录.

This is expected as per Redshift documentation. Nothing wrong with it. Below goes documentation excerpt for if not exist.

如果不存在

条款，指示如果指定的表已存在，则该命令不应进行任何更改并返回一条消息，指出该表已存在，而不是终止并返回错误.请注意，现有表可能与将要创建的表完全不同.仅将表名用于比较.

此子句在编写脚本时很有用，因此，如果CREATE TABLE尝试创建一个已经存在的表，则脚本不会失败.

This clause is useful when scripting, so the script doesn’t fail if CREATE TABLE tries to create a table that already exists.

将其取出，将返回可重复的错误，这很奇怪.

Take that out and it will return duplicatetable error which is odd.

它应该是预期的，表已经存在，因此是duplicate table error.

Its expected, table already exist, hence duplicate table error.

我在Redshift中没有看到表格

I don't see a table in Redshift

这应该是您用来查看表的用户无权查看该表或错误地查看某个其他数据库的问题. 为了证明我的观点，该表存在，请尝试使用您的程序在表中插入一些记录，然后尝试选择这些记录(如果发生)，然后证明该表存在并具有数据.您可能用来查看表格的其他用户可能没有查看该表格的权限.

This should be an issue that either the user you are using to view table is not having rights to view that table or looking into some-other database by mistake. To prove my point that table exist, try inserting some records in table using your program and try selecting those records, if it happens, then its proved that table exists and having the data. Other user that you might be using to see table may not have rights to view it.

希望对您有帮助.

这篇关于尝试使用Python和psycopg2创建Redshift表，但未创建该表且未报告任何错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！