Question 20500 in Mysql Question posted in
The official MySQL documentation can be found here.

Python function to insert into MySQL

I cannot see why my defined function does not work. No error message, just no INSERT into the Table… after much help from the community, here is an edit of the intial question with more info:

EDIT —————

TEST 1 >> works

import mysql.connector
db = mysql.connector.connect(host="localhost",
                             user="root",
                             passwd="",
                             database="test_base")
mycursor = db.cursor()

def testa():
    mycursor.execute("INSERT INTO test_table (Time, trade_ID, Price, Quantity) VALUES (%s,%s,%s,%s)", 
                     (678228251973, 239938314,"19.97000000","25.03000000"))
    db.commit()

testa()

TEST 2 >> does not work

def testb(tablename):
    mycursor.execute("INSERT INTO '{0}' (Time, trade_ID, Price, Quantity) VALUES (%s,%s,%s,%s)".format(tablename),
                     (678228251973, 239938314,"19.97000000","25.03000000"))
    db.commit()

testb('test_table')

error =

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an
error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near ”test_table’
(Time, trade_ID, Price, Quantity) VALUES (678228251973,239938314,’1′ at line 1"

TEST 3 >> does not work

def testb(tablename):
    mycursor.execute(f"INSERT INTO '{0}' (Time, trade_ID, Price, Quantity) VALUES (%s,%s,%s,%s)".format(tablename),
                     (678228251973, 239938314,"19.97000000","25.03000000"))
    db.commit()

testb('test_table')

error =(note the ‘19.9700000’ at line 1 in this one vs ‘1’ at line 1 in TEST 2 (???)

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an
error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near ”0′
(Time, trade_ID, Price, Quantity) VALUES (678228251973,239938314,’19.9700000′ at line 1

TEST 4 >> new method all together / does not work either

import pymysql
conn=pymysql.connect(host="localhost", user="root", passwd="", database="test_base")
cur  = conn.cursor()

def insertData(Tablename,Time,trade_ID,Price,Quantity):
    try :
        cur.execute(f"INSERT INTO '{Tablename}' (Time, trade_ID, Price, Quantity) VALUES ({Time},{trade_ID}, {Price}, {Quantity})")
        conn.commit()
    except Exception as e:
        conn.rollback()

insertData('test_table', 1678440087935, 240165118, 16.73000000, 88)
conn.close()

error = no error message but does not work…

Tags:

2

Answers


  1. 0

    The final SQL command that will get executed (in test 4) is:

    INSERT INTO 'test_table' (Time, trade_ID, Price, Quantity) VALUES (1678440087935,240165118, 16.73, 88)

    Try to execute the line within your MySQL-Servers CLI. Maybe also try to test it with a database client like heidisql or dbeaver. There is very limited information of how you database structure looks like.

    Edit:
    Also add a print to your exception or you won’t see any error.

    Login or Signup to reply.
  2. 0

    I think the error is most likely due to you trying to insert values into the table name but the formatting of the query itself is not correct. So that’s causing the parameter of tablename will be passed as string literal (it will be interpreted as '{0}') instead of actual variables that you inputted (such as test_db)

    You can modified it your code with separated the argument for execute the query and values like

    def test_database(tablename: str):
    query = "INSERT INTO {0} (Time, trade_ID, Price, Quantity) VALUES (%s,%s,%s,%s)".format(tablename)
    values = (1678440087935, 240165118, 16.73, 88)
    mycursor.execute(query, values)
    db.commit()
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search