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

Aman Gupta · Accepted Answer · 2015-12-03 06:57:01Z

31

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

edited Dec 3, 2015 at 6:57

answered Dec 2, 2015 at 16:01

Aman Gupta

1,9144 gold badges33 silver badges60 bronze badges

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

2 Comments

Steven Wilson Over a year ago

Thanks what about the date? I need it to be inserted in this format yyyy-mm-dd hh:mm:ss

Aman Gupta Over a year ago

@StevenWilson please accept the answer if it helped you.

Artem · Accepted Answer · 2018-09-06 08:07:04Z

15

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

edited Sep 6, 2018 at 8:07

answered Aug 2, 2017 at 12:17

Artem

1481 silver badge6 bronze badges

1 Comment

User Over a year ago

You forgot to import some stuff

jtlz2 · Accepted Answer · 2021-04-27 19:37:49Z

0

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

edited Apr 27, 2021 at 19:37

jtlz2

8,55511 gold badges74 silver badges128 bronze badges

answered Apr 27, 2021 at 18:24

yonilobo

1832 silver badges7 bronze badges

1 Comment

delphi1 Over a year ago

it doesn't work with %s placeholders, because it will throw the error "Incorrect datetime value", it's better to use datetime module as mentioned above.

VLAZ · Accepted Answer · 2024-10-03 15:14:44Z

0

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

edited Oct 3, 2024 at 15:14

VLAZ

29.6k9 gold badges65 silver badges88 bronze badges

answered Oct 3, 2024 at 15:11

Test User

1

Collectives™ on Stack Overflow

insert into a mysql database timestamp

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related