MySQL en Python en 5 Minutes

EditionHistorique

Importer le module : 

import MySQLdb 

Connexion :

conn = MySQLdb.connect (host = "localhost",
                           user = "testuser",
                           passwd = "testpass",
                           db = "test")

 conn.close ()

Executer une requete :

cursor = conn.cursor ()
cursor.execute ("SELECT VERSION()")
row = cursor.fetchone ()
print "server version:", row[0]
cursor.close ()

Gestion des erreurs :

   try:
     conn = MySQLdb.connect (host = "localhost",
                             user = "testuser",
                             passwd = "testpass",
                             db = "test")
   except MySQLdb.Error, e:
     print "Error %d: %s" % (e.args[0], e.args[1])
     sys.exit (1)

 Nombre de rangées affectées :

   cursor.execute ("""
       INSERT INTO animal (name, category)
       VALUES
         ('snake', 'reptile'),
         ('frog', 'amphibian'),
         ('tuna', 'fish'),
         ('racoon', 'mammal')
     """)
   print "Number of rows inserted: %d" % cursor.rowcount

 Requètes SELECT :

   cursor.execute ("SELECT name, category FROM animal")
   while (1):
     row = cursor.fetchone ()
     if row == None:
       break
     print "%s, %s" % (row[0], row[1])
   print "Number of rows returned: %d" % cursor.rowcount

 ou

   cursor.execute ("SELECT name, category FROM animal")
   rows = cursor.fetchall ()
   for row in rows:
     print "%s, %s" % (row[0], row[1])
   print "Number of rows returned: %d" % cursor.rowcount

 ou (pour avoir le nom des colonnes)

   cursor = conn.cursor (MySQLdb.cursors.DictCursor)
   cursor.execute ("SELECT name, category FROM animal")
   result_set = cursor.fetchall ()
   for row in result_set:
     print "%s, %s" % (row["name"], row["category"])
   print "Number of rows returned: %d" % cursor.rowcount

 Paramètres :

   cursor.execute ("""
         UPDATE animal SET name = %s
         WHERE name = %s
       """, ("snake", "turtle"))
   print "Number of rows updated: %d" % cursor.rowcount

Effectuer les changements :

  conn.commit ()