python - Python 中的 MySQL 提示占位符

Question

我一直在尝试使用 python 的 MySQLdb 通过我的 web 主机上的 SSH 在 MySQL 数据库上执行 SQL。我(在 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: 警告:第 1 行游标.execute(sqlstring, data) 列“类别”的数据被截断

Answer 1

如果您引用documentation您将看到 execute 方法调用单独的数据参数，而不是使用 % 格式化整个 SQL 语句，因为这会在 SQL 语句中引入错误。您可以自己尝试一下，打印您生成的 sqlstring 并将其发送到 MySQL，您将会得到同样的错误。请按照文档执行此操作。

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

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