mysql12.py

#!/usr/bin/python
# ==================================================================
# test MySQLdb
# ------------------------------------------------------------------
# sudo apt-get install python-mysqldb
# sudo apt-get install python3-mysqldb
# ------------------------------------------------------------------
# from: www.mikusa.com/python-mysql-docs/query.html
# ==================================================================

import MySQLdb as mydb

songs = ('Purple Haze','All Along the Watch Tower','Foxy Lady')

# -- function

def DropTable(db,cur,table):
    ##print 'DropTable(%s)' % table
    sql = 'DROP TABLE IF EXISTS %s' % table
    cur.execute(sql)
    db.commit()

try:
    print('--- connecting to database')

    db = mydb.connect('localhost','root','root','phonebook')

    cur = db.cursor()

    print('--- create table')

    DropTable(db,cur,'songs')

    sql = 'CREATE TABLE songs ( \
            id     INT UNSIGNED AUTO_INCREMENT, \
            title  TEXT NOT NULL, \
            PRIMARY KEY (id))'
    cur.execute(sql)
    db.commit()

    print('--- Insert songs')

    for song in songs:
        sql = 'INSERT INTO songs (title) VALUES ("%s")' % song
        ##print sql
        cur.execute(sql)
        db.commit()
        print('Auto increment ID: %s' % cur.lastrowid)

except Exception as e:
    db.rollback()
    print(e)

finally:
    cur.close()          # close all cursors
    db.close()           # close all databases