Anaconda and MySQL

I already wrote that since a few months ago I am working with #Anaconda, a scientific Python version, because this version includes several tools for working with data. However, I ws a little bit confuse because there is no MySQL package to work with.

So, if you need to connect with a MySQL database, you need to install a package. On a previous post, I already explained how you can install MySQLdb, but, even doing this, it doesn’t work:

So, I read on this forum post that it’s recommended to use PyMySQL as a MySQL connectos, so I download it and I install it (from source code):

Let’s try it’s works:

Class to work with PyMySQL

to work with PyMySQL, I have modify a class that I can use on several projects. The code is not main, but you can have it here, on GitHub.

# coding: utf-8
__author__ = 'dtrillo'

import sys;
import pymysql; #mysql library (you will need to install this on the system)

#MySQL Singleton Class with pymysql
class MySQLConnector(object):
#    
_connection = None;
_instance = None;

def __init__(self, host="localhost", user="root", passwd="forever", database="", debug=False):    # Versión 1.0.1
try:
if MySQLConnector._instance == None:
MySQLConnector._instance = self;
self.dbhost = host
self.dbuser = user
self.dbpassword = passwd
self.dbname = database
MySQLConnector._instance.connect(debug);    # Versión 1.0.1
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:
MySQLConnector._connection = pymysql.connect(self.dbhost, self.dbuser, self.dbpassword, self.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();

The source of this class is here, although I have introduce a few modifications, instead of working with config files inside the class.

Now, a script

And now, let’s try a small script to work with the class and with PyMySQL:

We instantiate to MySQLConnector object with the required parameters, we have the conection and … that’s it!

Have a nice day!.