Posted on: January 22nd, 2012 by Famous Phil
Its now time to become serious with my blog again after such a long lapse in real content.
When building new applications, it always seems like I start with the grand picture requiring no massive data storage and shortly after I begin, I find myself needing a database connection. I’ve made the mistake several times now of not starting with a dedicated database class that connects to MySQL, and I always find myself googling for pre-made classes that don’t do exactly what I need, so I’ve decided to post my own for future reference. As a result, I’m going to make sure that when I Google, I find my own reference before someone else’s 
Hopefully this is useful to someone else.
PHP’s MySQL singleton class:
<?php
// Copyright (c) 2012 Philip Matuskiewicz www.famousphil.com
// To use:
// require_once("Mysql.php");
// $db = new Mysql();
class Mysql{
private $server = "localhost";
private $username = "";
private $password = "";
private $database_table = "";
private static $instance;
private function __construct(){
$this->connect();
}
public function connect(){
mysql_connect($this->server, $this->username, $this->password);
mysql_select_db($this->database_table);
$this->q("set names 'utf8'");
}
//query the database
public function q($query){
$r = mysql_query($query);
return $r;
}
//returns an array containing all the rows that were returned
public function qr($query){
$r = $this->q($query);
if (mysql_num_rows($r) > 0) {
$res = array();
while ($arr = mysql_fetch_array($r)) {
array_push($res, $arr);
}
return $res;
} else {
return null;
}
}
//number of rows returned
public function nr($query){
return mysql_num_rows($this->q($query));
}
//last inserted row id is returned
public function lid(){
return mysql_insert_id();
}
//close the database connection
public function c(){
mysql_close();
}
public static function singleton(){
if (!isset(self::$instance)) {
$c = __class__;
self::$instance = new $c;
}
return self::$instance;
}
public function __clone(){
trigger_error('no clone', E_USER_ERROR);
}
}
?>
The Python MySQL singleton implementation is similar, but includes an external file named config.py in this example
--> config.py (configuration information for the MySQL class)
dbhost = "localhost";
dblogin = "";
dbpassword = "";
dbname = "";
--> MySQL.py (The MySQL class)
#!/usr/bin/env python
# Copyright (c) 2012 Philip Matuskiewicz www.famousphil.com
#to include / use, insert the following lines in the code
#import imp;
#mysql = imp.load_source("MySQLConnector", "PATH_TO_PYTHON_FILE/mysql.py").MySQLConnector();
#result = mysql.tryquery("Mysql Query Here");
import sys;
import os;
import string;
import base64;
import MySQLdb;#mysql library (you will need to install this on the system)
#MySQL Singleton Class
class MySQLConnector(object):
_connection = None;
_instance = None;
def __init__(self):
try:
if MySQLConnector._instance == None:
MySQLConnector._instance = self;
MySQLConnector._instance.connect();
except Exception, e:
print "MySQL Error "+str(e);
def instance(self):
return MySQLConnector._instance;
def get_connection(self):
return MySQLConnector._connection;
def connect(self, debug=False):
try:
for line in open('includes/config.py'):
#this can be dangerous, but sources / executes lines in config.py, which contains the db info
#alternatively, you can just set the variables here manually
exec('%s = %s' % tuple(line.split('=', 1)));
MySQLConnector._connection = MySQLdb.connect(dbhost, dblogin, dbpassword, dbname);
if debug:
print "INFO: Database connection successfully established";
except Exception, e:
print "ERROR: MySQL Connection Couldn't be created... Fatal Error! "+str(e);
sys.exit();
def disconnect(self):
try:
MySQLConnector._connection.close();
except:
pass;#connection not open
#returns escaped data for insertion into mysql
def esc(self, esc):
return MySQLdb.escape_string(str(esc));
#query with no result returned
def query(self, sql):
cur = MySQLConnector._connection.cursor();
return cur.execute(sql);
def tryquery(self, sql):
try:
cur = MySQLConnector._connection.cursor();
return cur.execute(sql);
except:
return False;
#inserts and returns the inserted row id (last row id in PHP version)
def insert(self, sql):
cur = MySQLConnector._connection.cursor();
cur.execute(sql);
return self._connection.insert_id();
def tryinsert(self, sql):
try:
cur = MySQLConnector._connection.cursor();
cur.execute(sql);
return self._connection.insert_id();
except:
return -1;
#returns the first item of data
def queryrow(self, sql):
cur = MySQLConnector._connection.cursor();
cur.execute(sql);
return cur.fetchone();
#returns a list of data (array)
def queryrows(self, sql):
cur = MySQLConnector._connection.cursor();
cur.execute(sql);
return cur.fetchmany();
#end class MySQLConnector
Tags: mysql, php, Python, Singleton, Source Code
Posted in Programming, Technology
|| 2 Comments »