python - 如何在卸载中转义单引号

Question

    conn_string = "dbname='{}' port='{}' user='{}' password='{}' host='{}'"\
            .format(dbname,port,user,password,host_url) 

    sql="""UNLOAD ('select col1,col2 from %s.visitation_hourly_summary_us where col4= '2018-07-10' and col5= '1';') TO 's3://%s/%s/%s.csv' \
            credentials 'aws_access_key_id=%s;aws_secret_access_key=%s' \
            MANIFEST GZIP ALLOWOVERWRITE;Commit;""" \
            % (schema_name,s3_bucket_name, schema,table,aws_access_key_id,\
            aws_secret_access_key)

con = psycopg2.connect(conn_string)
cur = con.cursor()
cur.execute(sql)

我正在尝试执行上述脚本以读取表，然后在S3中创建文件

由于我的列是字符串，所以我无法跳过单引号，并且在语法错误的附近获取错误

另外，我尝试在条件仍然显示相同错误的地方给\。

任何帮助将不胜感激。

谢谢

Answer 1

正如Sarang所说，只需在查询的col4和col5值中用双引号替换单引号即可解决问题。

但是，我建议您将字符串分解为较小的块，以便于阅读和维护。这样，您应该能够按照chepner的建议使用execute(和MySQL documentation):

# Create the inner SQL statement. Notice the single quotes for the general
# string and the double quotes for the col4 and col5 values
sql_stmt = ('SELECT col1, col2 '
            'FROM %s.visitation_hourly_summary_us '
            'WHERE col4 = "2018-07-10" AND col5= "1";' % schema_name)

# Format the s3 path
s3_target = 's3://%s/%s/%s.csv' % (s3_bucket_name, schema, table)

# Format credentials string
s3_credentials = 'aws_access_key_id=%s;aws_secret_access_key=%s' % (
    aws_access_key_id, aws_secret_access_key)

# Create a tuple with all preformatted strings
data = (sql_stmt, s3_target, s3_credentials)

# Format the s3 query skeleton
s3_stmt = ("UNLOAD ('%s') TO '%s' "
           "CREDENTIALS '%s' "
           "MANIFEST GZIP ALLOWOVERWRITE;Commit;")

con = psycopg2.connect(conn_string)
cur = con.cursor()
cur.execute(s3_stmt, data)