Insert in Postgres with Python is not working

1

I need to include data and a database of mine, but I can not and am not sure how to solve the problem.

My code is as follows:

import psycopg2

conn=psycopg2.connect("dbname='teste' user='postgres' host='localhost' password=''")
c = conn.cursor()

data = list(myarraywhit128values)

c.execute("INSERT INTO faces (id, cadastro, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14, p15, p16, p17, p18, p19, p20, p21, p22, p23, p24, p25, p26, p27, p28, p29, p30, p31, p32, p33, p34, p35, p36, p37, p38, p39, p40, p41, p42, p43, p44, p45, p46, p47, p48, p49, p50, p51, p52, p53, p54, p55, p56, p57, p58, p59, p60, p61, p62, p63, p64, p65, p66, p67, p68, p69, p70, p71, p72, p73, p74, p75, p76, p77, p78, p79, p80, p81, p82, p83, p84, p85, p86, p87, p88, p89, p90, p91, p92, p93, p94, p95, p96, p97, p98, p99, p100, p101, p102, p103, p104, p105, p106, p107, p108, p109, p110, p111, p112, p113, p114, p115, p116, p117, p118, p119, p120, p121, p122, p123, p124, p125, p126, p127, p128) VALUES (DEFAULT, NOW()," + str(data[0]) + "," + str(data[1]) + "," + str(data[2]) + "," + str(data[3]) + "," + str(data[4]) + "," + str(data[5]) + "," + str(data[6]) + "," + str(data[7]) + "," + str(data[8]) + "," + str(data[9]) + "," + str(data[10]) + "," + str(data[11]) + "," + str(data[12]) + "," + str(data[13]) + "," + str(data[14]) + "," + str(data[15]) + "," + str(data[16]) + "," + str(data[17]) + "," + str(data[18]) + "," + str(data[19]) + "," + str(data[20]) + "," + str(data[21]) + "," + str(data[22]) + "," + str(data[23]) + "," + str(data[24]) + "," + str(data[25]) + "," + str(data[26]) + "," + str(data[27]) + "," + str(data[28]) + "," + str(data[29]) + "," + str(data[30]) + "," + str(data[31]) + "," + str(data[32]) + "," + str(data[33]) + "," + str(data[34]) + "," + str(data[35]) + "," + str(data[36]) + "," + str(data[37]) + "," + str(data[38]) + "," + str(data[39]) + "," + str(data[40]) + "," + str(data[41]) + "," + str(data[42]) + "," + str(data[43]) + "," + str(data[44]) + "," + str(data[45]) + "," + str(data[46]) + "," + str(data[47]) + "," + str(data[48]) + "," + str(data[49]) + "," + str(data[50]) + "," + str(data[51]) + "," + str(data[52]) + "," + str(data[53]) + "," + str(data[54]) + "," + str(data[55]) + "," + str(data[56]) + "," + str(data[57]) + "," + str(data[58]) + "," + str(data[59]) + "," + str(data[60]) + "," + str(data[61]) + "," + str(data[62]) + "," + str(data[63]) + "," + str(data[64]) + "," + str(data[65]) + "," + str(data[66]) + "," + str(data[67]) + "," + str(data[68]) + "," + str(data[69]) + "," + str(data[70]) + "," + str(data[71]) + "," + str(data[72]) + "," + str(data[73]) + "," + str(data[74]) + "," + str(data[75]) + "," + str(data[76]) + "," + str(data[77]) + "," + str(data[78]) + "," + str(data[79]) + "," + str(data[80]) + "," + str(data[81]) + "," + str(data[82]) + "," + str(data[83]) + "," + str(data[84]) + "," + str(data[85]) + "," + str(data[86]) + "," + str(data[87]) + "," + str(data[88]) + "," + str(data[89]) + "," + str(data[90]) + "," + str(data[91]) + "," + str(data[92]) + "," + str(data[93]) + "," + str(data[94]) + "," + str(data[95]) + "," + str(data[96]) + "," + str(data[97]) + "," + str(data[98]) + "," + str(data[99]) + "," + str(data[100]) + "," + str(data[101]) + "," + str(data[102]) + "," + str(data[103]) + "," + str(data[104]) + "," + str(data[105]) + "," + str(data[106]) + "," + str(data[107]) + "," + str(data[108]) + "," + str(data[109]) + "," + str(data[110]) + "," + str(data[111]) + "," + str(data[112]) + "," + str(data[113]) + "," + str(data[114]) + "," + str(data[115]) + "," + str(data[116]) + "," + str(data[117]) + "," + str(data[118]) + "," + str(data[119]) + "," + str(data[120]) + "," + str(data[121]) + "," + str(data[122]) + "," + str(data[123]) + "," + str(data[124]) + "," + str(data[125]) + "," + str(data[126]) + "," + str(data[127]) + ")")

Can anyone point me to where the error is? The variable myarraywhit128values is being received correctly with all values.

    
asked by anonymous 21.12.2018 / 17:42

2 answers

1

You were asked to call conn.commit() . By default, the psycopg2 library will start a transaction before executing the first SQL. If the commit function is not called, the transaction is ignored and the changes will not take effect in the database.

You can improve the structure of your code using the context managers . In this case it is even more interesting because the transactions are committed when the context is closed.

DNS = "dbname='teste' user='postgres' host='localhost' password=''"
SQL = '''
    INSERT INTO faces (id, cadastro, p1, p2, ...)
    VALUES (DEFAULT, NOW(), %s, %s, %s, ...)
'''

with psycopg2.connect(DSN) as conn:
    with conn.cursor() as curs:
        curs.execute(SQL, data)

Data in data will be treated and replaced where there is %s in SQL.

As stated in the official documentation :

  

When the connection exits the block, if no exception has been raised by the block, the transaction is committed. In case of exception the transaction is rolled back.

Free Translation: When a connection closes the block with , if no exception was thrown by the block the transaction will be commitada . In case of an exception, the transaction will be undone.

However, if you need to run more queries or make sure that the connection is closed at a certain point in the code, you can do:

conn = psycopg2.connect(DSN)

with conn:
    with conn.cursor() as curs:
        curs.execute(SQL, data)

conn.close()

Because the context manager acts only on the current transaction, not on the connection itself.

    
21.12.2018 / 18:01
0

The problem was solved as follows

import psycopg2

conn=psycopg2.connect("dbname='teste' user='postgres' host='localhost' password=''")
c = conn.cursor()

data = list(myarraywhit128values)

c.execute("INSERT INTO faces (id, cadastro, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14, p15, p16, p17, p18, p19, p20, p21, p22, p23, p24, p25, p26, p27, p28, p29, p30, p31, p32, p33, p34, p35, p36, p37, p38, p39, p40, p41, p42, p43, p44, p45, p46, p47, p48, p49, p50, p51, p52, p53, p54, p55, p56, p57, p58, p59, p60, p61, p62, p63, p64, p65, p66, p67, p68, p69, p70, p71, p72, p73, p74, p75, p76, p77, p78, p79, p80, p81, p82, p83, p84, p85, p86, p87, p88, p89, p90, p91, p92, p93, p94, p95, p96, p97, p98, p99, p100, p101, p102, p103, p104, p105, p106, p107, p108, p109, p110, p111, p112, p113, p114, p115, p116, p117, p118, p119, p120, p121, p122, p123, p124, p125, p126, p127, p128) VALUES (DEFAULT, NOW()," + str(data[0]) + "," + str(data[1]) + "," + str(data[2]) + "," + str(data[3]) + "," + str(data[4]) + "," + str(data[5]) + "," + str(data[6]) + "," + str(data[7]) + "," + str(data[8]) + "," + str(data[9]) + "," + str(data[10]) + "," + str(data[11]) + "," + str(data[12]) + "," + str(data[13]) + "," + str(data[14]) + "," + str(data[15]) + "," + str(data[16]) + "," + str(data[17]) + "," + str(data[18]) + "," + str(data[19]) + "," + str(data[20]) + "," + str(data[21]) + "," + str(data[22]) + "," + str(data[23]) + "," + str(data[24]) + "," + str(data[25]) + "," + str(data[26]) + "," + str(data[27]) + "," + str(data[28]) + "," + str(data[29]) + "," + str(data[30]) + "," + str(data[31]) + "," + str(data[32]) + "," + str(data[33]) + "," + str(data[34]) + "," + str(data[35]) + "," + str(data[36]) + "," + str(data[37]) + "," + str(data[38]) + "," + str(data[39]) + "," + str(data[40]) + "," + str(data[41]) + "," + str(data[42]) + "," + str(data[43]) + "," + str(data[44]) + "," + str(data[45]) + "," + str(data[46]) + "," + str(data[47]) + "," + str(data[48]) + "," + str(data[49]) + "," + str(data[50]) + "," + str(data[51]) + "," + str(data[52]) + "," + str(data[53]) + "," + str(data[54]) + "," + str(data[55]) + "," + str(data[56]) + "," + str(data[57]) + "," + str(data[58]) + "," + str(data[59]) + "," + str(data[60]) + "," + str(data[61]) + "," + str(data[62]) + "," + str(data[63]) + "," + str(data[64]) + "," + str(data[65]) + "," + str(data[66]) + "," + str(data[67]) + "," + str(data[68]) + "," + str(data[69]) + "," + str(data[70]) + "," + str(data[71]) + "," + str(data[72]) + "," + str(data[73]) + "," + str(data[74]) + "," + str(data[75]) + "," + str(data[76]) + "," + str(data[77]) + "," + str(data[78]) + "," + str(data[79]) + "," + str(data[80]) + "," + str(data[81]) + "," + str(data[82]) + "," + str(data[83]) + "," + str(data[84]) + "," + str(data[85]) + "," + str(data[86]) + "," + str(data[87]) + "," + str(data[88]) + "," + str(data[89]) + "," + str(data[90]) + "," + str(data[91]) + "," + str(data[92]) + "," + str(data[93]) + "," + str(data[94]) + "," + str(data[95]) + "," + str(data[96]) + "," + str(data[97]) + "," + str(data[98]) + "," + str(data[99]) + "," + str(data[100]) + "," + str(data[101]) + "," + str(data[102]) + "," + str(data[103]) + "," + str(data[104]) + "," + str(data[105]) + "," + str(data[106]) + "," + str(data[107]) + "," + str(data[108]) + "," + str(data[109]) + "," + str(data[110]) + "," + str(data[111]) + "," + str(data[112]) + "," + str(data[113]) + "," + str(data[114]) + "," + str(data[115]) + "," + str(data[116]) + "," + str(data[117]) + "," + str(data[118]) + "," + str(data[119]) + "," + str(data[120]) + "," + str(data[121]) + "," + str(data[122]) + "," + str(data[123]) + "," + str(data[124]) + "," + str(data[125]) + "," + str(data[126]) + "," + str(data[127]) + ")")

conn.commit()
    
21.12.2018 / 17:57