Insert in Postgres with Python is not working

Asked

Viewed 65 times

0

I need to include data and a database of my own, but I can’t and I’m not able to solve the problem.

My code is this::

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 where the error is? The variable myarraywhit128values is being received correctly with all values.

python postgresql

  • And what error appears?

  • at the command line

  • 1

    you already tried to put the conn.commit() after the insert?

  • I won’t do it yet and I warn you.

  • fucnionou thank you very much

2 answers

1

Missed you calling conn.commit(). By default, the library psycopg2 will start a transaction before executing the first SQL. If the function commitis not called, the transaction will be ignored and the changes will have no effect on the database.

You can improve the structure of your code by 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)

The data in data will be treated and replaced where there are jokers %s in SQL.

As said in official documentation:

When a Connection exits the with block, if no Exception has been Raised by the block, the transaction is Committed. In case of Exception the transaction is Rolled back.

In free translation: when a connection closes the block with, if no exception has been posted by block the transaction will be committed. In case of exception, the transaction will be undone.

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

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.

0

The resolution of the problem was 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()

Browser other questions tagged python postgresql

You are not signed in. Login or sign up in order to post.