CODEWITHSUNDEEP
pythonmysqlpython-2.7insert
Steven Wilson
Steven Wilson

Reputation: 157

insert into a mysql database timestamp

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

Upvotes: 12

Views: 57544

Answers (4)

Test User
Test User

Reputation: 1

insert_data = "INSERT into test (test_date,test1,test2) values (NOW(),%s,%s)"

Upvotes: 0

yonilobo
yonilobo

Reputation: 163

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

Upvotes: 0

Artem
Artem

Reputation: 148

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

Upvotes: 15

Aman Gupta
Aman Gupta

Reputation: 1874

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

Upvotes: 30

Related Questions