分析选项卡时出现意外的EOF

Question

我试图用python编写一个脚本，它将从表1获取数据并输入到另一个表中。有点像某种ETL。 但是我遇到了这个SyntaxError: unexpected EOF while parsing错误。 我有点东拉西扯，试着用别人用过的技巧，所以我不太了解自己。在

以下是我目前为止的代码：

import psycopg2
try:
    connectionone = psycopg2.connect(user = "postgres",
                      password = "xxxxxx",
                      host = "127.0.0.1",
                      port = "5432",
                      database = "xxxxxx")
    connectiontwo = psycopg2.connect(user = "postgres",
                      password = "xxxxxx",
                      host = "127.0.0.1",
                      port = "5432",
                      database = "xxxxxx")

    cursorsource = connectionone.cursor()
    cursordest = connectiontwo.cursor()
    #Truncating dest table
    print("Truncating Destination")
    cursordest.execute('delete from testarea.salepersons_2')
    connectiontwo.commit()

    #Fetch source data
    cursorsource.execute('SELECT sp_no, sp_name, sp_territory, sp_product, 
     active FROM testarea.salepersons_original;') 
    rows = cursorsource.fetchall()

    sql_insert = 'INSERT INTO testarea.salepersons_2 (sp_no, sp_name,  
      p_territory, sp_product, active) values '
    sql_values = ['(%s, %s, %s, %s, %s)'] 

    data_values = []

    batch_size = 1000 #customize for size of tables... 

    sql_stmt = sql_insert + ','.join(sql_values*batch_size) + ';'

    for i, row in enumerate(rows, 1):

                data_values += row[:5] #relates to number of columns (%s)
                if i % batch_size == 0:
                    cursordest.execute (sql_stmt , data_values )
                    cursordest.commit()
                    print("Inserting")
                    data_values = []

    if (i % batch_size != 0):
        sql_stmt = sql_insert + ','.join(sql_values*(i % batch_size)) + 
        ';'
        cursordest.execute (sql_stmt, data_values)
        print("Last Values ....")
        connectiontwo.commit()
except (Exception, psycopg2.Error) as error :
        print ("Error occured :-(", error)
finally:
    #closing database connection.
        if(connectionone):
            cursorsource.close()
            connectionone.close()
            print("PostgreSQL connection is closed")

    #closing database connection.
        if(connectiontwo):
            cursordest.close()
            connectiontwo.close()
            print("PostgreSQL connection is closed")
#close connections
cursorsource.close()
cursordest.close()
cursorsource.close()
cursordest.close()

Answer 1

第一个问题很简单，可以解决。多行字符串仅用单引号括起来：

cursorsource.execute('SELECT sp_no, sp_name, sp_territory, sp_product, 
    active FROM testarea.salepersons_original;') 

您应该用三个引号将其括起来，这不会影响SQL执行：

^{pr2}$

剩下的代码我很难理解。我怀疑您是否真的有一个包含5000列的表，所以我认为您试图插入1000个包含5个值的行。如果我的理解是正确的，我只能给出一个大致的方法：

import random
import string

# Create some fake data to visualise
fake_data = [random.choice(list(string.ascii_letters)) for x in range(50)]

# Chunk the data (https://stackoverflow.com/a/1751478/4799172)
# This reshapes it into sublists each of length 5.
# This can fail if your original list is not a multiple of 5, but I think your
# existing code will still throw the same issues.
def chunks(l, n):
    n = max(1, n)
    return (l[i:i+n] for i in range(0, len(l), n))


chunked_data = chunks(fake_data, 5)

sql_insert = """INSERT INTO testarea.salepersons_2 (sp_no, sp_name, 
                sp_territory, sp_product, active) values (?, ?, ?, ?, ?)"""

# Use executemany, not execute in a loop, to repeat for each sublist in 
# chunked_data
cursor.executemany(sql_insert, chunked_data)

注意，在本例中，我使用参数化查询来防止SQL注入（我使用?作为值的占位符）。不同的库有不同的占位符；例如，MySQL包装器期望%s，而SQLite期望?-在本例中，我使用了?来消除模糊性，即它不仅仅是常规的字符串格式，但是您可能必须改回%s。在