CODEWITHSUNDEEP
pythonmysql
Apricot
Apricot

Reputation: 3021

Insert into MySQL Python not writing any data

I have created the following table in MySQL

create table DT (Date varchar(20), Time varchar(20))

I am using the following Python code to insert into MySQL. I am not getting any error nor is the date and time is getting written into MySQL.

import mysql.connector
from datetime import datetime

conn = mysql.connector.connect(host = 'localhost', database = 'mydatabase', user = 'root', password = '')
cursor = conn.cursor()
i = datetime.now()
xdate = i.strftime('%Y-%m-%d')
xtime = i.strftime('%H-%M-%S')
sqql = 'insert into DT (Date, Time) values (%s,%s);'
cursor.execute(sqql, xdate, xtime)
conn.commit()
conn.close()

However when I directly do the insert in MySQL it works..

insert into DT (Date, Time) values ('2017-02-06', '19-54-36');

How can I resolve this?

Upvotes: 1

Views: 984

Answers (2)

lmiguelvargasf
lmiguelvargasf

Reputation: 70003

According to this example and the documentation, the method cursor.execute() takes a string query as first parameter, and the second parameter is a tuple or a dictionary that contains the values of that query. So you should call the method this way:

cursor.execute(sqql, (xdate, xtime))

In case you are using xdate and xtime just in this invocation, you can even simplify this call by:

cursor.execute(sqql, (i.strftime('%Y-%m-%d'), i.strftime('%H-%M-%S')))

In addition, you can also use a dictionary in order to improve the readability of the code:

sqql = 'insert into DT (Date, Time) values (%(xdate)s, %(xtime)s);'
cursor.execute(sqql, {'xdate': `xdate`, 'xtime`: xtime})

Upvotes: 0

alecxe
alecxe

Reputation: 474221

You are not passing the query parameters correctly, put them into a tuple, replace:

cursor.execute(sqql, xdate, xtime)

with:

cursor.execute(sqql, (xdate, xtime))

Upvotes: 4

Related Questions