Saturday, July 26, 2014

Quick Guide to Using MySQL in Python

If you are using windows 7 32 bit, then download the python-mysql-connector msi installer and install first.

MySqlPython.py


import MySQLdb
import sys

try :
    db = MySQLdb.connect(host="localhost", port=3306, user="root", passwd="", db="python")
except Exception:
    pass

if db is None:
    print("The database could not connect");
    sys.exit(0)
else:
    print("The database connected");
    cursor = db.cursor();

def executeQuery(query, params = None):
    return cursor.execute(query, params);

def listQuery(query, params = None):
    cursor.execute(query, params);
    return cursor.fetchall();

def readQuery(query, params = None):
    cursor.execute(query, params);
    return cursor.fetchone();

print("Deleting all user from database");
executeQuery("DELETE FROM USER");

print("Inserting user to database...");
query = """Insert into user(name, roll) values(%s, %s)""";
params = ('Pritom', '001');
result = executeQuery(query, params);
print("Insert result: " + str(result));

params = ('Sumon', '002');
result = executeQuery(query, params);
print("Insert result: " + str(result));

params = ('Liton', '003');
result = executeQuery(query, params);
print("Insert result: " + str(result));

print("Reading total user count...");
result = readQuery("SELECT COUNT(*) FROM user");
print("Total user: " + str(result[0]));

print("Reading total user list...");
result = listQuery("SELECT * FROM user");
for row in result:
    print("Id: " + str(row[0]) + ", Name: " + str(row[1]) + ", Roll: " + str(row[2]));

cursor.close();
db.close();   
sys.exit();

Output be as follows:


The database connected
Deleting all user from database
Inserting user to database...
Insert result: 1
Insert result: 1
Insert result: 1
Reading total user count...
Total user: 3
Reading total user list...
Id: 66, Name: Pritom, Roll: 001
Id: 67, Name: Sumon, Roll: 002
Id: 68, Name: Liton, Roll: 003

No comments:

Post a Comment