PythonMySQL: Difference between revisions
No edit summary |
(→Gordo) |
||
(3 intermediate revisions by one other user not shown) | |||
Line 33: | Line 33: | ||
<source lang="python"> | <source lang="python"> | ||
import settings | import settings | ||
name = "foo" | |||
dbconn = settings.connect_db() | dbconn = settings.connect_db() | ||
cursor = dbconn.cursor() | cursor = dbconn.cursor() | ||
Line 46: | Line 46: | ||
cursor.close() | cursor.close() | ||
dbconn.close() | dbconn.close() | ||
</source> | |||
== Gordo == | |||
<source lang="python"> | |||
dbconn = settings.connect_db() | |||
cursor = dbconn.cursor(MySQLdb.cursorx.DictCursor) | |||
while 1: | |||
q = "SELECT * FROM commands ORDER BY t_create LIMIT 1" | |||
cursor.execute() | |||
row = cursor.fetchone() | |||
print row['command'] | |||
# | |||
if (row['command'] == "foo"): | |||
print "doing foo command" | |||
elif (row['command'] == "print"): | |||
pass | |||
# | |||
q = "DELETE FROM commands WHERE id=%s" | |||
cursor.execute(q, (row['id'],)) | |||
</source> | </source> |
Latest revision as of 17:34, 11 June 2008
You'll need the "MySQLdb" library installed.
Connecting to MySQL
If you put this function in a file (say "settings.py"), you can easily import if from different scripts... and also you only need to change the login data in one location (useful for local vs. server-based testing).
import MySQLdb
def connect_db():
host_name = "localhost"
db_name = "dbname"
user_name = "dbuser"
password = "dbpass"
try:
conn = MySQLdb.connect (db = db_name,
host = host_name,
user = user_name,
passwd = password)
return conn
except MySQLdb.Error, e:
print "Cannot connect to server"
print "Error code:", e.args[0]
print "Error message:", e.args[1]
sys.exit(1)
Executing a Query
Many rows
import settings
name = "foo"
dbconn = settings.connect_db()
cursor = dbconn.cursor()
q = "SELECT id FROM items WHERE status='active' AND name=%s ORDER BY id"
cursor.execute(q, (name,))
while 1:
row = cursor.fetchone()
if (row == None): break
print row[0]
# if you forget this, it happens automatically
cursor.close()
dbconn.close()
Gordo
dbconn = settings.connect_db()
cursor = dbconn.cursor(MySQLdb.cursorx.DictCursor)
while 1:
q = "SELECT * FROM commands ORDER BY t_create LIMIT 1"
cursor.execute()
row = cursor.fetchone()
print row['command']
#
if (row['command'] == "foo"):
print "doing foo command"
elif (row['command'] == "print"):
pass
#
q = "DELETE FROM commands WHERE id=%s"
cursor.execute(q, (row['id'],))