Inserting datetime into database using pyscopg2 and PostgreSQL

Question

I'm having trouble inserting a datetime stamp into a sql database using the insert statement with pyscopg2.

What the code below does is every time the button is pushed, it should insert a row into the database containing the buildingID(which is just text) and the date and time when the button was pressed.

I just can't figure out how to insert the current date and time.

# Inserts data into local database
def insertLocalDB():
    # Open a cursor to perform database operations
    cur = conn.cursor()
    cur.execute("INSERT INTO test_table (buildingID,datetime) VALUES(%s,%s)",
    ("01", datetime))  #HAS TO BE CURRENT DATE AND TIME
    # Make the changes to the database persistant
    conn.commit()
    # Close communication with the database
    cur.close()

Community · Accepted Answer · 2017-05-23 12:02:46Z

17

While you certainly could insert a Python datetime into a row via psycopg2 -- you would need to create a datetime object set to the current time, which can be done like this or via modules such as Delorean -- since you just want the current time, I would just leave that up to Postgres itself.

e.g.

def insertLocalDB():
    # Open a cursor to perform database operations
    cur = conn.cursor()
    cur.execute("INSERT INTO test_table (buildingID,datetime) VALUES(%s, now() )",
    ("01", ))
    # Make the changes to the database persistant
    conn.commit()
    # Close communication with the database
    cur.close()

now() returns the current time as a timestamp with time zone type, and will be run on the server side after the first %s is replaced by psycopg2 (via libpq) by 01.

Also note that the tuple of args must have a trailing comma since it has just one element, else it won't be an actual tuple.

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered May 15, 2015 at 2:07

khampson

15.5k4 gold badges45 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Inserting datetime into database using pyscopg2 and PostgreSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related