Python中的MySQL抱怨占位符

Question

我一直试图使用python的MySQLdb在我的SSH主机上的MySQL数据库上执行MySQL数据库。我写的这个程序(在mac上)应该打印一个表，但它没有。

这是我的密码：

import feedparser
import time
import MySQLdb

topnews = []
politics = []
# tech = []
sports = []
world = []
mostread = []
business = []

feeds = [topnews, mostread, politics, world, sports, business]
d = feedparser.parse('http://feeds.reuters.com/reuters/topNews/.rss') #Just to keep other cells functioning.

def refresh():
    global d
    global topnews
    global politics
#     global tech
    global sports
    global world
    global mostread
    global business
    topnews = feedparser.parse('http://feeds.reuters.com/reuters/topNews/.rss')
    politics = feedparser.parse('http://feeds.reuters.com/reuters/PoliticsNews/.rss')
#     tech = feedparser.parse('http://feeds.reuters.com/reuters/technologyNews/.rss')
    sports = feedparser.parse('http://feeds.reuters.com/reuters/sportsNews/.rss')
    world = feedparser.parse('http://feeds.reuters.com/reuters/worldNews/.rss')
    mostread = feedparser.parse('http://feeds.reuters.com/reuters/mostRead/.rss')
    business = feedparser.parse('http://feeds.reuters.com/reuters/businessNews/.rss')
    global feeds
    global d
    feeds = [topnews, mostread, politics, world, sports, business]
    d = feedparser.parse('http://feeds.reuters.com/reuters/topNews/.rss') #Just to keep other cells functioning.

refresh()


def summarize(feed, num): #Define a method called "summarize"

    summary = feed['entries'][num]['summary_detail']['value'] #Make a variable equal to the summary

    newsummary = "" #The summary we are trying to make, which is empty so far.

    for char in summary: #Keep running the following code as many times as there are characters in summary.

        if char == "<": #If the current character is a less than sign,

            return newsummary #We can finally show our new summary!  Mission Accomplished!!!!!!!

        else: #Otherwise,

            newsummary = newsummary + char #Add the current character to our new summary.

    return newsummary.replace(firstword(summarize(topnews, 0)), "").replace("- ", "")


def identify(feed):
    term = feed['entries'][0]['tags'][0]['term']
    if term == mostread['entries'][0]['tags'][0]['term']:
        return "Most Read"
    elif term == topnews['entries'][0]['tags'][0]['term']:
        return "Top News"
    elif term == politics['entries'][0]['tags'][0]['term']:
        return "Politics"
#     elif term == tech['entries'][0]['tags'][0]['term']:
#         return "Tech"
    elif term == sports['entries'][0]['tags'][0]['term']:
        return "Sports"
    elif term == world['entries'][0]['tags'][0]['term']:
        return "World"
    elif term == business['entries'][0]['tags'][0]['term']:
        return "Business"

def firstword(string):
    word = ""
    for char in string:
        if char == "-":
            return word
        else:
            word = word + char

def cat(feed, num):
    spec = identify(feed)
    if firstword(summarize(feed, num)) != "(Reuters)":
        spec = spec + ", " + firstword(summarize(feed, num))
    return spec#.replace("(Reuters)")

def link(feed, num):
    return d['entries'][num]['link'] #Gives the link to the specified number article.

def date(feed):
    return d['entries'][0]['published']

#############################################################################################################################################  Coding Rocks!

# Open database connection
db = MySQLdb.connect("localhost","myusername","mypassword","databasename") # Of course, I included the actual values here.

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
cursor.execute('''
    DROP TABLE IF EXISTS news;
    ''')

cursor.execute('''
    CREATE TABLE news
    (
        id int unsigned NOT NULL auto_increment,
        headline varchar(250) NOT NULL,
        summary varchar(5000) NOT NULL,
        date varchar(50) NOT NULL,
        link varchar(2500) NOT NULL,
        imagelink varchar(2500) NOT NULL,
        category varchar(50) NOT NULL,

        PRIMARY KEY (id)
    );
''')



for numelem in range( 0, len(mostread['entries']) - 1):
    sqlstring = '''
    insert into news (headline, summary, date, link, imagelink, category)
    values ("NULLFORNOW", %s, %s, %s, "NULLFORNOW", %s);

         ''' % (   summarize(mostread, numelem), date(mostread), link(mostread, numelem), cat(mostread, numelem)   )
    cursor.execute(sqlstring)

# cursor.execute('''
#     SELECT * FROM news;
#   ''') 


# results = cursor.fetchall()

# disconnect from server
db.close()

print "Whoopdeedoo! Program done. :)\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n"

这会引发一个错误：

Traceback (most recent call last):
  File "feedparser.py", line 132, in <module>
    cursor.execute(sqlstring)
  File "/usr/lib64/python2.6/site-packages/MySQLdb/cursors.py", line 173, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.6/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Brazil (Reuters) - Brazilian presidential candidate Eduardo Campos was killed in' at line 2")

我真的很抱歉这个问题质量很差，我对这个错误太厌倦了，我真的不知道错误在哪里。

请告诉我问题在哪里，当然，如何解决。

谢谢你，CJ

编辑:我尝试了@metatoaster的建议，现在我得到了错误：

feedparser.py:137: Warning: Data truncated for column 'category' at row 1 cursor.execute(sqlstring, data)

Answer 1

如果您引用文档，您将看到execute方法调用一个单独的数据参数，而不是使用%格式化整个SQL语句，因为这会给SQL语句带来错误。通过打印生成的sqlstring并将其发送到MySQL，您可以自己尝试这一点，您将得到相同的错误。按照文档的规定，这样做是正确的。

    data = (
        summarize(mostread, numelem),
        date(mostread),
        link(mostread, numelem),
        cat(mostread, numelem),
    )
    cursor.execute(sqlstring, data)

至于第二个错误，这意味着输入数据超过了字段的长度(您定义的字段的最大长度为50个字符)。再次打印出您实际上试图作为类别输入的内容，以查看它可能是字符串的太长，甚至是错误的字符串。