python - 从csv复制时，如果在“ id”列中将错误抛出为空值，则复制命令不会更新Id_sequence列，这违反了非空约束(Copy command doesnt update the Id_sequence column when copying from csv throws error to null value in column “id” violates not-null constraint)

Question

i have csv file and have to copy the data to the postgre table, if in my csv if i dont input data for Id and Updated_at will throw error , but it should not as id is marked as default and increment .

(我有csv文件，并且必须将数据复制到postgre表，如果在我的csv中，如果我不输入Id的数据，则Updated_at会抛出错误，但是它不应该将id标记为default和增量。)

i am doing this copy from python

(我正在从python复制此文件)

Error:

(错误：)

2019-12-01T14:05:16.57+0530 [APP/PROC/WEB/0] OUT Error Code: 23502. Error ERROR: null value in column "id" violates not-null constraint 2019-12-01T14:05:16.57+0530 [APP/PROC/WEB/0] OUT DETAIL: Failing row contains (null, street_address, null).

(2019-12-01T14：05：16.57 + 0530 [APP / PROC / WEB / 0] OUT错误代码：23502。错误错误：“ id”列中的空值违反了非空约束2019-12-01T14：05：16.57 +0530 [APP / PROC / WEB / 0]输出详细信息：失败行包含（空，街道地址，空）。)
2019-12-01T14:05:16.57+0530 [APP/PROC/WEB/0] OUT CONTEXT: COPY demographic_types, line 2: ",street_address,"

(2019-12-01T14：05：16.57 + 0530 [APP / PROC / WEB / 0] OUT上下文：COPY demographic_types，第2行：“，street_address，”)

CREATE TABLE IF NOT EXISTS public.demographic_types (
    id bigint DEFAULT nextval('public.demographic_types_id_seq'::regclass) NOT NULL,
    demographic_type text NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);

Python code

(Python代码)

def load_data(conn):
    """
    Load seeded data
    """
    db = os.environ['DATABASE_URL']
    dbname = db.replace("hsdp_pg","harbinger")
    try:
        with psycopg2.connect(dbname) as conn1:

            #migrate_db(conn, dbname, mirth_pw, harbinger_pw, psql_path, init_db)
            conn1.commit()
    except psycopg2.Error as exp1:
        print(exp1)
        print ('Error Code: %s. Error %s' % (exp1.pgcode, exp1.pgerror))


    print(conn1)
    path = os.path.dirname(os.path.realpath(__file__))    
    print (os.path.join(path,"database/data/*.csv"))
    for fle in sorted(glob.glob(os.path.join(path,"database/data/*.csv"))):
        print ('>>>Migrating data %s' % fle)
        table_name = os.path.basename(fle).replace('.csv', '')

        try:
            #silent_query(conn, sql, None)
            with conn1.cursor() as cur:
                #delete data first
                print('Deleting data from table  %s' % table_name)
                cur.execute('TRUNCATE %s CASCADE' % table_name)
                print('i am done and waiting')
                conn1.commit()


                with open(fle, 'r') as f:
                    #headers = ", ".join(table_column_mapping_data[table_name])
                    print("i am here ")
                    #cur.copy_from(f, table_name, sep=',')
                    #sql = "INSERT INTO %s (ID, demographic_type, updated_at) VALUES (%s,%s,%s)" % table_name
                    #record_insert = ('1', 'phone', '')
                    #cur.execute(sql, record_insert)
                    sql = "COPY %s from STDIN WITH CSV HEADER DELIMITER ','" % table_name
                    #print(sql)
                    cur.copy_expert(sql, f)
                    conn1.commit()
        except psycopg2.Error as exp2:
            print ('Error Code: %s. Error %s' % (exp2.pgcode, exp2.pgerror))

  ask by Manoj Kasa translate from so

Answer 1

If I understand correctly you want to import some data from a CSV file, but allow the database to automatically fill some columns with default values (either nextval of a sequence for id column or now() for updated_at column).

(如果我理解正确，则想从CSV文件导入一些数据，但允许数据库自动使用默认值填充某些列（id列的序列的nextval或updated_at列的now（））。)

For this you have to tell the copy command which columns are in the CSV file, like this:

(为此，您必须告诉copy命令CSV文件中的哪些列，如下所示：)

for fle in sorted(pathlib.path(path,"database/data/").glob("*.csv")):
    logging.info('>>>Migrating data %s', fle)
    table_name = fle.stem
    try:
        with conn1.cursor() as cur:
            logging.info('Deleting data from table %s', psycopg2.extensions.quote_ident(table_name))
            cur.execute('TRUNCATE %s CASCADE' % psycopg2.extensions.quote_ident(table_name, cur))
            logging.info('i am done and waiting')

            with open(fle, 'r') as f:
                cur.copy_from(
                    f,
                    table_name,
                    sep=',',
                    columns=[
                        'demographic_type',
                        'updated_at',
                        'street_address',
                        'city',
                        'state_or_province',
                        'postal_code',
                        'secondary_phone',
                        # more columns, but without id or created_at
                    ]
                )
        conn1.commit()
    except psycopg2.Error as exp2:
        print ('Error Code: %s. Error %s' % (exp2.pgcode, exp2.pgerror))