0

I am currently having troubles inserting into MYSQL DB using python. The execute statement below dosen't run, which I found out using print stmts.

This is a mystery to me as while using the same codes, my friends are able to insert into their DB (Of course using the appropriate mysql host, password etc. and the same mysql and python version)

mycursor.execute("""
            INSERT INTO emailcontent (Subject, Date, Sender, Sendee, Message_body)
            VALUES (%(Subject)s, %(Date)s, %(Sender)s, %(To)s, %(Message_body)s)
            """, temp_dict)
            mydb.commit()

temp_dict example data:

{'Subject': 'Security alert', 'Date': '2020-12-28', 'Sender': 'Google <no-reply@accounts.google.com>', 'Snippet': 'Quickstart was granted access to your Google Account sometestemail@gmail.com If you did not grant access, you should check this activity and secure your account. Check activity You can also see security', 
'To': 'sometestemail@gmail.com'}

Other codes:

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="root",
  database="database"
)

mycursor = mydb.cursor()

Versions

Python 3.9.1

mysql 8.0.22

mysql-connector 2.2.9

Jerry
  • 27
  • 2
  • 1
    You state that the query "doesn't run", but did not post a traceback. Can you update your question with any and all exceptions encountered? It would also be good to note which mysql client python library you're using (with version). In the mean time, you mind find the answer from the user @furicle posted on this question helpful: https://stackoverflow.com/questions/9336270/using-a-python-dict-for-a-sql-insert-statement – mxmader Dec 28 '20 at 16:03
  • you are using python string format in wrong way please try `mycursor.execute(""" INSERT INTO emailcontent (Subject, Date, Sender, Sendee, Message_body) VALUES ({Subject}, {Date}, {Sender}, {Sendee}, {Message_body}) """.format(Subject: temp_dict["Subject"], Date: temp_dict["Date"])) mydb.commit()` and write for remaining values too in a similar way – Santhosh Reddy Dec 28 '20 at 16:06
  • 1
    @SanthoshReddy the OP is using the correct syntax. You should _never_ use string formatting for values in SQL queries. – snakecharmerb Dec 28 '20 at 16:15
  • got it we got to give list of values in execute query – Santhosh Reddy Dec 29 '20 at 06:27

2 Answers2

0

There's no Message_body item in your dictionary. You do have Snippet. Try that.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
0

Referring to an answer posted here Using a Python dict for a SQL INSERT statement by furicle

Managed to insert using the following:

table = 'emailcontent'
            
placeholders = ', '.join(['%s'] * len(temp_dict))
columns = ', '.join(temp_dict.keys())
sql = "INSERT INTO emailcontent (Subject, Date, Sender, Sendee, Message_body) VALUES ( %s )" % (placeholders)

mycursor.execute(sql, list(temp_dict.values()))
Jerry
  • 27
  • 2