Query to insert data in mysql table contains foreign key

Question

I am very new to Mysql and dumping a file in db using python. I'm having 2 tables The file format is:

id name sports
1 john baseball
2 mary Football

like student & Sports Student table

id    name
 1     John
 2     Mary

here id is primary key

& in sports table

stu_id sports_title

1      Baseball
2      Football

and here stu_id is foreign key reference with student table

and my problem is

query="insert into sports (stu_id,name)VALUES (%d,%s)"
            ("select id from student where id=%d,%s")
#words[0]=1 ,words[2]=Baseball

args=(words[0],words[2])
cursor.execute(query,args)

upon executing this code, I'm facing

  "Not all parameters were used in the SQL statement")
ProgrammingError: Not all parameters were used in the SQL statement

@IljaEverilä I guess `words` is the fields of the lines from the file shown at the top. — Barmar, Jul 04 '17 at 08:04
@Barmar if you have to guess, the question is incomplete. It could've as well been the rows, but the edit made it clear that it is a row. — Ilja Everilä, Jul 04 '17 at 08:45
At least with the mysql.connector DB-API driver [all placeholders should be either %s or %(name)s](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html), depending on how you pass the args. There's no distinction between numbers, strings, etc. See also: https://stackoverflow.com/questions/5785154/python-mysqldb-issues-typeerror-d-format-a-number-is-required-not-str — Ilja Everilä, Jul 04 '17 at 08:52

score 0 · Accepted Answer · answered Jul 04 '17 at 08:08

You can't use both VALUES and SELECT as the source of the data in INSERT. You use VALUES if the data is literals, you use SELECT if you're getting it from another table. If it's a mix of both, you use SELECT and put the literals into the SELECT list.

query = """
    INSERT INTO sports (stu_id, sports_title)
    SELECT id, %s
    FROM student
    WHERE name = %s
"""
args = (words[2], words[1])
cursor.execute(args)

Or, since the file contains the student ID, you don't need SELECT at all.

query = "INSERT INTO sports(stu_id, sports_title) VALUES (%d, %s)"
args = (words[0], words[1])
cursor.execute(args)

Query to insert data in mysql table contains foreign key

1 Answers1