Python 向外键属性插入值(MYSQL)

Question

我在MySQL数据库中创建了两个表

员工表:

+----+-----------+-------------
| ID |           NAME         |
+----+-----------+-------------
| No data available in table   |         
+----+-----------+-------------

资格表:

+----+----------+--------------+
| ID | title    |  staff_ID    |   
+----+----------+--------------+
|  No data available in table  | 
+----+--------------+-----------

两个表的ID都是主键，资格表的staff_ID是外键。我的Python代码是这样的

con=pymysql.connect(host='localhost',port=3306,user='root',password='admin',db='mydb')
cursor=con.cursor()
cursor.execute("INSERT INTO Staff (ID,Name)VALUES (%s,%s)",(1001,"MR Tan"))
cursor.execute("INSERT INTO Qualification VALUES (%s,%s,%s)",(2001,"Professor","select ID from Staff where ID=1001"))
con.commit()
cursor.close()
con.close()

我尝试将值插入外键属性，但收到“TypeError:并非所有参数在字符串格式化期间都已转换”

Answer 1

两种方法:

你可以试试

cursor.execute("INSERT INTO Staff (ID,Name)VALUES (%d,%s)",(1001,"MR Tan"))
cursor.execute("INSERT INTO Qualification VALUES (%d,%s,%s)",(2001,"Professor","select ID from Staff where ID=1001"))

或

cursor.execute("INSERT INTO Staff (ID,Name)VALUES (%s,%s)",("1001","MR Tan"))
cursor.execute("INSERT INTO Qualification VALUES (%s,%s,%s)",("2001","Professor","select ID from Staff where ID=1001"))