使用python在mysql中添加数据

lnlaulya  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(329)

我想在centos上使用Python2.7在mysql数据库中添加数据。
这是我的密码,

  1. # Sample data
  2. clusterList =['a', 'w', 'e', 'r']
  3. versionList = [1,2,3,4]
  4. portalDomain = ['aa', 'bb', 'cc', 'dd']
  5. portalDomains = ['p1', 'p2', 'p3', 'p4']
  6. db = mysql.connect(host="mydb.com", user="nod", passwd="", db="noc")
  7. cur = db.cursor()
  8. for i in range(len(portalDomains)):
  9. print("Adding data...")
  10. query = "INSERT INTO test_score (portalCluster,portalVersion,portalDomain) values
  11. ('%s', %s, '%s')"
  12. % (clusterList[i], versionList[i], portalDomains[i])
  13. cur.execute(query)
  14. db.commit()

我得到下面的错误,

  1. _mysql_exceptions.ProgrammingError: (1064, "You have an error in your
  2. SQL syntax; check the manual that corresponds to your MySQL server version for
  3. the right syntax to use near 'release-20180822-16, 'portal.xyv.com')' at line 1")

我可以使用,

  1. cur.execute ("INSERT INTO test_release_score
  2. (portalCluster,portalVersion,portalDomain) values ('%s', %s, '%s')"
  3. % ("1122q", 7, "2wq2w"))

但是,当我把它改成下面这样的东西

  1. # change 7 to 'qa22'
  2. cur.execute ("INSERT INTO test_release_score
  3. (portalCluster,portalVersion,portalDomain) values ('%s', %s, '%s')" %
  4. ("1122q", "qa12", "2wq2w"))

我得到一个错误:

  1. _mysql_exceptions.OperationalError: (1054, "Unknown column 'qa12' in 'field list'")

任何帮助都将不胜感激。

5lwkijsr

5lwkijsr1#

而不是使用 % 运算符,使用占位符和查询参数:

  1. query = ("INSERT INTO test_score (portalCluster,portalVersion,portalDomain) "
  2. "VALUES (%s, %s, %s)")
  3. cur.execute(query, (clusterList[i], versionList[i], portalDomains[i]))

这样,数据库驱动程序将为您执行必要的转义/引用,这也可以防止sql注入漏洞。

相关问题