JSON compiler for CRUD to access MySQL

Often time, it is hard to use SQL statement to manipulate database function CRUD (create, read, update, delete). To smooth the process of CRUD, I wrote a compiler to translate JSON command into SQL command. So you can just pass JSON object to access database. Of course, the return result is also JSON format.

Let's discuss CRUD a little more, basically "Read" and "Delete" are pretty straightforward, command "select * from tablename" or "delete from table where id =x". In my compiler just pass SQL command.

But "Update" and "Insert" are more complicated and I hate most, you need to write "update tableName set xxx=xx , xx=xx". So this part, I wrote a JSON compiler to do it.

The following codes provide you a JSON style to update database record.

client code [for query]

import sql.batchSql

jsonSQL={}

jsonSQL["sqlParameter"]={} jsonSQL["sqlParameter"]["ipName"]="your machine name" jsonSQL["sqlParameter"]["dbName"]="yourtableName" jsonSQL["sqlParameter"]["id"]="yourName" jsonSQL["sqlParameter"]["pw"]="yourPWName" jsonSQL["sqlParameter"]["sqlArray"]=["select paper_name,sno from MEDLINE" ] print jsonSQL result= sql.batchSql.executeQuery(jsonSQL)

//The above codes also can store multiple SQL command in the arrayList that you can execute multiple query in one time. You can take advantage to reduce SQL command. Of course, you can also use this to do "Delete"    

** PySQLPool  python library is required. I choose it, because it manage the mysql session more smartly.

"Insert function" is similar to "Update", so I didn't put an example here. The function ,"insertGetAutoID", would return the autoID after inserting a record. 

client code [for update]:

Created on 2009/6/1  @author: arbiter ''' 

import sql.batchSql

sno ='1'

updateTitle ='bbb'

pmid ='ff'

abstract ="ab"

inputJSON={}

inputJSON ["sqlParameter"]={"action":"update","tableName":"MEDLINE"}

inputJSON["sqlParameter"]["ipName"]="192.168.0.126"

inputJSON["sqlParameter"]["dbName"]="yourDBName"

inputJSON["sqlParameter"]["id"]="yourID"

inputJSON["sqlParameter"]["pw"]="yourPW"

inputJSON ["sqlParameter"]["columnNameForCondition"]="sno"  

inputJSON ["sqlParameter"]["valueForCondition"]=sno

inputJSON["data"]={}

inputJSON["data"]["updateTitle"]=updateTitle

inputJSON["data"]["pmid"]=pmid

inputJSON["data"]["fullAbstract"]=abstract

sql.batchSql.updateJson(inputJSON)

package file 

sql.batchSql

file name : batchSql.py

'''

Created on 2009/10/22

@author: arbiter

'''

import PySQLPool

connection = PySQLPool.getNewConnection(host = 'hostname', username='XXX', password='XXX', schema='dbName',charset = "utf-8")

 

def connectDB(ipName,dbName,id,pw):

    global connection,query

    connection = PySQLPool.getNewConnection(host = ipName, username=id, password=pw, schema=dbName, charset = "utf-8")

    query = PySQLPool.getNewQuery(connection = connection)

def executeQuery(jsonSQL):

    global connection

    ipName =jsonSQL["sqlParameter"]["ipName"]

    dbName = jsonSQL["sqlParameter"]["dbName"]

    id = jsonSQL["sqlParameter"]["id"]

    pw = jsonSQL["sqlParameter"]["pw"]

    sqlStatement = jsonSQL["sqlParameter"]["sqlArray"][0]

    try:

        connectDB(ipName,dbName,id,pw)

        query = PySQLPool.getNewQuery(connection = connection)

        query(sqlStatement)

        #cursor.execute(sql)

    except:

        connectDB(ipName,dbName,id,pw)

        query = PySQLPool.getNewQuery(connection = connection)

        query.Query(sqlStatement)

    result = query.record

    return result

def executeQueryMulti(jsonSQL):

    #sql = renderSQLforInsert(inputJSON)

    global connection

   

    ipName =jsonSQL["sqlParameter"]["ipName"]

    dbName = jsonSQL["sqlParameter"]["dbName"]

    id = jsonSQL["sqlParameter"]["id"]

    pw = jsonSQL["sqlParameter"]["pw"]

    sqlStatement = jsonSQL["sqlParameter"]["sqlArray"]

    #query.executeMulti(sqlStatement)

    try:

        connectDB(ipName,dbName,id,pw)

        query = PySQLPool.getNewQuery(connection = connection)

        result =query.executeMulti(sqlStatement)

        #cursor.execute(sql)

    except:

        connectDB(ipName,dbName,id,pw)

        query = PySQLPool.getNewQuery(connection = connection)

        result= query.executeMulti(sqlStatement)

    return "done"

    

def insertGetAutoID(jsonSQL):

    global connection

    ipName =jsonSQL["sqlParameter"]["ipName"]

    dbName = jsonSQL["sqlParameter"]["dbName"]

    id = jsonSQL["sqlParameter"]["id"]

    pw = jsonSQL["sqlParameter"]["pw"]

    connectDB(ipName,dbName,id,pw)

    #insert = PySQLPool.getNewQuery(connection = connection)

    sqlStatement = renderSQLforInsert(jsonSQL)

    print sqlStatement

    try:

        connectDB(ipName,dbName,id,pw)

        insert = PySQLPool.getNewQuery(connection = connection)

        insert.Query(sqlStatement)

        #cursor.execute(sql)

    except:

        connectDB(ipName,dbName,id,pw)

        insert = PySQLPool.getNewQuery(connection = connection)

        insert.Query(sqlStatement)

    autokey= insert.lastInsertID

    return autokey

    

def updateJson(jsonSQL):

    #global connection,query

    ipName =jsonSQL["sqlParameter"]["ipName"]

    dbName = jsonSQL["sqlParameter"]["dbName"]

    id = jsonSQL["sqlParameter"]["id"]

    pw = jsonSQL["sqlParameter"]["pw"]

    #connectDB(ipName,dbName,id,pw)

    print ipName,dbName,id,pw

    sqlStatement = renderSQLforUpdate(jsonSQL)

    #global db,cursor

    try:

        connectDB(ipName,dbName,id,pw)

        connection = PySQLPool.getNewConnection(host = ipName, username=id, password=pw, schema=dbName,charset = "utf-8")

        query = PySQLPool.getNewQuery(connection = connection)

        #update = PySQLPool.getNewQuery(connection = connection)

        print sqlStatement

        result = query.Query(sqlStatement)

        print "1",result

    except:

        connectDB(ipName,dbName,id,pw)

        update = PySQLPool.getNewQuery(connection = connection)

        update.Query(sqlStatement)

        print "2"

    return sqlStatement+";"

      

def renderSQLforInsert(inputJSON):

    firstRaw ={}

    firstRaw=inputJSON["data"]

    #print firstRaw

    columnNames = firstRaw.keys()

    tableName =inputJSON["sqlParameter"]["tableName"]

    columnArray =""

    valueArray =""

    for e in columnNames:

        columnArray=columnArray+str(e)+","

    for e in inputJSON["data"]:

        valueArray= valueArray+"'"+str(firstRaw[e])+"'"+","

    columnArray=columnArray[:len(columnArray)-1]

    valueArray=valueArray[:len(valueArray)-1]

    sql = "insert into "+tableName+" ("+columnArray+") values ("+valueArray+")"

    return sql

def renderSQLforUpdate(inputJSON):

    firstRaw ={}

    firstRaw=inputJSON["data"]

    #print firstRaw

    columnNames = firstRaw.keys()

    tableName =inputJSON["sqlParameter"]["tableName"]

    columnNameForCondition =inputJSON["sqlParameter"]["columnNameForCondition"]

    valueForCondition = inputJSON["sqlParameter"]["valueForCondition"]

    columnArray =""

    valueArray =""

    sqlStatement = "update "+tableName+" set "

    for e in columnNames:

        sqlStatement= sqlStatement +"" + str(e) +" ='"+str(firstRaw[e])+"',"

    sqlStatement=sqlStatement[:len(sqlStatement)-1]

    sqlStatement = sqlStatement+ " where " + columnNameForCondition+"='"+str(valueForCondition)+"'"

    return sqlStatement