测试Python到MySQL

Question

我创建了一个小脚本，可以将数据保存到MySQLdb。一开始我用的时候效果很好：

cursor.execute('INSERT INTO people (name, text) VALUES ("dan", "test2")')

以上内容将把“dan”保存到标题中，“test2”保存到文本中。我想测试一下，看看我是否能够定义一些东西，并以这种方式填充它。例如，如果我要刮取一个站点并说（dan = soup.title.string）或类似的话，它将能够将这些数据填充到数据库中。我想四处看看，但似乎什么也找不到。你知道吗

import MySQLdb
import sys

try:
    db = MySQLdb.connect(
        host = 'localhost',
        user = 'root',
        passwd = '',
        db = 'python',
        )
except:
    print "db not found"

dan = "dandandan"
test2 = "testing101"

cursor = db.cursor()
cursor.execute('INSERT INTO people (name, text) VALUES (dan, test2)')
cursor.execute('SELECT * FROM people')
result = cursor.fetchall()
db.commit()
db.close()

我收到的错误是：

C:\Users\********\Desktop>python mysqltest.py
Traceback (most recent call last):
  File "mysqltest.py", line 18, in <module>
    cursor.execute('INSERT INTO people (name) VALUES (dan)')
  File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defau
lterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1054, "Champ 'dan' inconnu dans field list"
)

Answer 1

您需要使用参数。你知道吗

cursor.execute('INSERT INTO people (name, text) VALUES (%s,%s)', (dan, test2))

Answer 2

使用准备好的语句：

cursor.execute("INSERT INTO people (name, text) VALUES (%s,%s)", (dan, test2))

从documentation ：

paramstyle

String constant stating the type of parameter marker formatting expected by the interface. Set to 'format' = ANSI C printf format codes, e.g. '...WHERE name=%s'. If a mapping object is used for conn.execute(), then the interface actually uses 'pyformat' = Python extended format codes, e.g. '...WHERE name=%(name)s'. However, the API does not presently allow the specification of more than one style in paramstyle.

Note that any literal percent signs in the query string passed to execute() must be escaped, i.e. %%.

Parameter placeholders can only be used to insert column values. They can not be used for other parts of SQL, such as table names, statements, etc.