I've been trying to use python's MySQLdb to execute SQL on a MySQL Database from SSH on my webhost. This program i wrote (on a mac) should print a table, but it doesn't.
Here's my code:
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"
This throws an error:
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")
I am sincerely sorry for the poor quality of this question; I am just too sick of this error, and I really don't know where the error is.
Please tell me where the problem is, and, of course, how to fix it.
Thank you, CJ
EDIT: I tried @metatoaster's suggestion, and now I am getting the error:
feedparser.py:137: Warning: Data truncated for column 'category' at row 1 cursor.execute(sqlstring, data)
