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()

score 16 · Accepted Answer · edited May 23 '17 at 12:02

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.

Inserting datetime into database using pyscopg2 and PostgreSQL

1 Answers1

Linked