insert into a mysql database timestamp

Question

I have a part in my python script that I need to insert some data into a table on a mysql database example below:

insert_data = "INSERT into test (test_date,test1,test2) values (%s,%s,%s)"
cur.execute(insert_data,(test_date,test1,test2))
db.commit()
db.close()

I have a couple of questions what is incorrect with this syntax and how is possible to change the VALUES to timestamp instead of %s for string? Note the column names in the database are the same as the data stored in the variables in my script.

THanks

Answer 1

Simply use the database NOW() function, e.g.

timestamp="NOW()"
insert_data = "INSERT into test (test_date,test1,test2) values (%s,%s,%s)"
cur.execute(insert_data,(test_date,test1,test2,timestamp))
db.commit()
db.close()

Answer 2

try this:

import MySQLdb
import time
import datetime

ts = time.time()
timestamp = datetime.datetime.fromtimestamp(ts).strftime('%Y-%m-%d %H:%M:%S')
conn = MySQLdb.connect(host= "localhost",
              user="root",
              passwd="newpassword",
              db="db1")
x = conn.cursor()

try:
   x.execute("""INSERT into test (test_date,test1,test2) values(%s,%s,%s)""",(timestamp,test1,test2))
   conn.commit()
except:
   conn.rollback()

conn.close()

Answer 3

Timestamp creating can be done in one line, no need to use time.time(), just:

from datetime import datetime

timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')