-1

I'd like the field I am inserting the data into to be a variable. And though there are plenty of questions on passing VALUES through variables there isn't a lot on passing column names.

ppg = [22.6]
import mysql.connector


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


    
command = (
"INSERT INTO table1({t_dot}) where id = 2"
"VALUES(%s)"
)

data = (float(ppg))

mycursor.execute(command.format(t_dot = 'Stats 1 Column'),data)

mydb.commit()

mycursor.close()

mydb.close()

The database has an id column which also serves as the primary key.

I'm currently getting

ProgrammingError: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

nbk
  • 45,398
  • 8
  • 30
  • 47
E. Artemis
  • 69
  • 1
  • 8
  • What @don'ttalkjustcode is saying, is that you do not format the string inside the `command()` function properly. You have to precede the string by `f` or follow it by `.format` with arguments if you want it to formatted correctly. – TanguyH Sep 17 '21 at 10:00

2 Answers2

1

MYsql and other rdms have no insert Where

An insert only insert a row, it has no condition as long all the rules that are involved are complied

What you can do

ppg = [22.6]
import mysql.connector


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


    
command = (
"INSERT INTO table1(`{t_dot}`) VALUES (%s)"
)

data = (float(ppg),)

mycursor.execute(command.format(t_dot = 'Stats 1 Column'),data)

mydb.commit()

mycursor.close()

mydb.close()

This will only work if all other columns of your table have a default values or are auto_increment, or else mysql will through an error

For updates, you need also to replacae the column like before

command = (
"UPDATE table1 SET `{t_dot}` =  %s WHERE id =  %s"
)

data = (float(ppg),2)

mycursor.execute(command.format(t_dot = 'Stats 1 Column'),data)
nbk
  • 45,398
  • 8
  • 30
  • 47
  • Thank you. This made me realize I had to use an update statement even though the fields are empty. I'll attempt that now. – E. Artemis Sep 17 '21 at 10:51
  • I've used this insert statement `command = ("update table1 set %s= %s where id=%s;" )`. The other variables work fine but I can't get the column name to work because it has a space in the name (stats 1 column). I tried using commas, double commas, and backticks but couldn't get the syntax right. `data = ('`Stats column`', ppg, 2) mycursor.execute(command,data)` – E. Artemis Sep 17 '21 at 11:13
  • colum snames can't be replace you need to make the same String format as in the insert command – nbk Sep 17 '21 at 11:45
  • Thank you. I was able to get it to work. – E. Artemis Sep 17 '21 at 12:22
0

Your insert query statement is wrong.

INSERT INTO SELECT: https://dev.mysql.com/doc/refman/8.0/en/insert-select.html

If you want to select the value/values you can make it:

INSERT INTO table1(t_dot)  ( select t_dot 
                             from table1 
                             where id = 2) ;

Or if you are trying to insert only the record 2:

INSERT INTO table1(t_dot) values(2);
Ergest Basha
  • 7,870
  • 4
  • 8
  • 28
  • t_dot is a variable which represents the column name, while 2 is the primary key. The value being inserted is data. I'm not getting the right syntax to input the column name as a variable. – E. Artemis Sep 17 '21 at 10:22
  • Are you looking for something like this: https://stackoverflow.com/questions/37405287/using-python-variables-in-mysql-insert-statement ? Or you need a select before the insert statement ? – Ergest Basha Sep 17 '21 at 10:33
  • @E.Artemis check this too : https://stackoverflow.com/questions/20559965/python-mysql-select-statement-with-variable – Ergest Basha Sep 17 '21 at 10:34