[Twisted-Python] multiple mysql results in function
I am using xmlrpc to send and recieve messages to a "thought board" kind of thing, and being COMPLETELY new to python and twisted, I am kind of stuck on something. I have everything working except for the fact that I want to return multiple rows when I run the mysql query. Here is the code I have: from twisted.web import xmlrpc, server import MySQLdb from twisted.enterprise import adbapi class itr(xmlrpc.XMLRPC): def xmlrpc_thought(self, uname, pwrd, arg, msg): """Return the most recent thought""" if arg == "get": db=adbapi.ConnectionPool("MySQLdb",host='192.168.0.220',user='redbeardmcg',passwd='xxxxx',db='itr') db.start() return db.runQuery(""" SELECT * FROM thoughts WHERE tid > (SELECT curtid FROM users WHERE uid = (SELECT uid FROM users WHERE username='%s' AND password='%s')) """ % (uname, pwrd)).addCallback(lambda results: results[0][2]) db.stop() if arg == "post": db=adbapi.ConnectionPool("MySQLdb",host='192.168.0.220',user='redbeardmcg',passwd='xxxxx',db='itr') db.start() db.runQuery(""" INSERT INTO thoughts(uid,thought) VALUES((SELECT uid FROM users WHERE username='%s' AND password='%s'), '%s') """ % (uname, pwrd, msg)) x='Thought added' return x if __name__ == '__main__': from twisted.internet import reactor r = itr() reactor.listenTCP(8899, server.Site(r)) reactor.run() I believe something needs to be changed in the callback, but I very well may be wrong. Thanks in advance! RDMcGuire01@indianatech.net
On Wed, 2007-12-12 at 12:26 -0500, Ryan McGuire wrote:
I am using xmlrpc to send and recieve messages to a "thought board" kind of thing, and being COMPLETELY new to python and twisted, I am kind of stuck on something.
I have everything working except for the fact that I want to return multiple rows when I run the mysql query.
Here is the code I have:
return db.runQuery(""" SELECT * FROM thoughts WHERE tid > (SELECT curtid FROM users WHERE uid = (SELECT uid FROM users WHERE username='%s' AND password='%s')) """ % (uname, pwrd)).addCallback(lambda results: results[0][2]) db.stop()
1. Notice you stop the database after its returned from function; i.e. it won't be. 2. Better to keep around a single database pool instance (e.g. as attribute of xmlrpc resource). 3. Perhaps you should, once you've familiarized yourself with DB-API and mysqldb's implementation, look at runInteraction method of the dbpool that lets you run a whole function instead of just a single SQL query in the context of a transaction. The adbapi HOWTO covers this.
On Wednesday 12 December 2007, Ryan McGuire wrote:
def xmlrpc_thought(self, uname, pwrd, arg, msg): """Return the most recent thought""" if arg == "get": db=adbapi.ConnectionPool("MySQLdb",host='192.168.0.220',user='redbeardmcg ',passwd='xxxxx',db='itr') db.start() return db.runQuery(""" SELECT * FROM thoughts WHERE tid > (SELECT curtid FROM users WHERE uid = (SELECT uid FROM users WHERE username='%s' AND password='%s')) """ % (uname, pwrd)).addCallback(lambda results: results[0][2])
Unrelated to your problem, but are you aware that this code is vulnerable to SQL injection? The values of "uname" and "pwrd" are set by the XMLRPC client and are not escaped when they are inserted into the query. This might not be a problem if you trust everyone on your network, but if you expose this to the internet or mischievous local users, you're asking for trouble. Bye, Maarten
participants (3)
-
Itamar Shtull-Trauring -
Maarten ter Huurne -
Ryan McGuire