[Python] SQLBuilder 範例程式碼

標籤：python   sql   sqlbuilder   

用Python寫一個SQLBuilder，Java版可以從 http://www.java2s.com/Code/Java/Database-SQL-JDBC/SQLBuilder.htm 看到。

附上代碼：

範例程式碼（一）：

class SQLDirector:  @classmethod  def buildSQL(cls, builder):    sql = ""    sql += builder.getCommand()    sql += builder.getTable()    sql += builder.getWhat()    sql += builder.getCriteria()    return sqlclass SQLBuilder:  def __init__(self): pass  def __del__(self): pass  def getCommand(self): pass  def getTable(self): pass  def getWhat(self): pass  def getCriteria(self): passclass InsertBuilder(SQLBuilder):  def __init__(self):    self._table = ""    self._columnsAndData = dict()    self._criteria = ""  def setTable(self, table):    self._table = table  def getCommand(self):    return "INSERT INTO "  def getTable(self):    return self._table  def getWhat(self):    columns = list()    values = list()    what = ""    for columnName in self._columnsAndData:      columns.append(columnName)      values.append(self._columnsAndData[columnName])    columns_str = string.join( map(lambda x: str(x), columns), "," )    values_str = string.join( map(lambda x: str(x), values), "," )    what += " ("    what += columns_str    what += ") VALUES ("    what += values_str    what += ") "    return what  def getCriteria(self):    return ""  def addColumnAndData(self, columnName, value):    if value:      self._columnsAndData[columnName] = valuedef main():  builder = InsertBuilder()  builder.setTable("employees")  builder.addColumnAndData("employee_id", int(221))  builder.addColumnAndData("first_name", "'Shane'")  builder.addColumnAndData("last_name", "'Grinnell'")  builder.addColumnAndData("email", "'[email protected]'")  sql = SQLDirector.buildSQL(builder)  print sqlif __name__ == '__main__':  main()

更有意思的玩法，

程式碼範例（二）：

#!/usr/bin/env pythonimport stringclass SQLDirector:  def __init__(self, builder):    self._builder = builder  def construct(self):    self._builder.buildSQL()    return self._builder.getSQL()class SQLBuilder:  def __init__(self): self._sql = ''  def buildSQL(self): return self  def getSQL(self): return self._sqlclass InsertBuilder(SQLBuilder):  def __init__(self):    self._columns = list()    self._values = list()  def buildSQL(self):    columns_str = string.join( map(lambda x: "`%s`" % str(x), self._columns), "," )    values_str  = string.join( map(lambda x: "'%s'" % str(x), self._values), "," )    self._sql  = "INSERT INTO "    self._sql += self._table    self._sql += " (%s) VALUES (%s) " % (columns_str, values_str)    return self  def table(self, table):    self._table = table    return self  def column(self, columnName, value):    self._columns.append(columnName)    self._values.append(value)    return selfdef main():  insertBuilder = InsertBuilder()  insertBuilder.table("dt_process")     .column("attr_key","foo").column("attr_val", "bar")  sqlDirector = SQLDirector(insertBuilder)  print sqlDirector.construct()if __name__ == "__main__":  main()

範例程式碼（三）：

#!/usr/bin/env pythonimport stringdef main():  insertBuilder = InsertBuilder()  insertBuilder.table("dt_process")     .column("attr_key","foo").column("attr_val", "bar")  sqlDirector = SQLDirector(insertBuilder)  print sqlDirector.construct()  createBuilder = CreateBuilder()  createBuilder.table("newTable").ifnotexists()     .column("id").col_type("INTEGER").col_autoincrement()     .column("name").col_type("VARCHAR(40)").col_nullable(False).col_default("empty")  sqlDirector = SQLDirector(createBuilder)  print sqlDirector.construct()# -----------------------------------------------------------------------------# Libraries# -----------------------------------------------------------------------------class SQLDirector:  def __init__(self, builder):    self._builder = builder  def construct(self):    self._builder.buildSQL()    return self._builder.getSQL()class SQLBuilder:  def __init__(self): self._sql = ''  def buildSQL(self): return self  def getSQL(self): return self._sqlclass CreateBuilder(SQLBuilder):  def __init__(self):    self._columns = list()  # item: dict => 'name', 'nullable', 'default'    self._column_context = None    self._table = ""    self._ifnotexists = ""    self._criteria = ""  def _map_column(self, column):    sql = ""    if column.has_key('name'): sql += " `%s`"%column['name']    if column.has_key('type'): sql += " %s"%column['type']    if column.has_key('nullable'): sql += "%s"%column['nullable']    if column.has_key('default'): sql += "%s"%column['default']    if column.has_key('auto_increment'): sql += "%s"%column['auto_increment']    return sql  def _reduce_column(self, left, right):    return left + ", " + right  def buildSQL(self):    columns_str = reduce(self._reduce_column, map( self._map_column, self._columns ))    self._sql  = "CREATE TABLE "    self._sql += self._ifnotexists    self._sql += self._table    self._sql += " (%s)" % columns_str    return self  def ifnotexists(self):    self._ifnotexists = " IF NOT EXISTS "    return self  def table(self, table):    self._table = table    return self  def column(self, columnName):    column = dict()    column['name'] = columnName    self._columns.append(column)    self._column_context = column    return self  def col_type(self, type):    self._column_context['type'] = type    return self  def col_nullable(self, nullable=True):    if nullable:      self._column_context['nullable'] = ""    else:      self._column_context['nullable'] = " NOT NULL"    return self  def col_default(self, default):    self._column_context['default'] = " DEFAULT %s" % default    return self  def col_autoincrement(self, autoincrement=True):    self._column_context['auto_increment'] = " AUTO_INCREMENT"    return self  def primary(self, primary=True):    self._primary_key.append(self._column_context['name'])    return self## INSERT INTO#class InsertBuilder(SQLBuilder):  def __init__(self):    self._columns = list()    self._values = list()    self._table = ""  def buildSQL(self):    columns_str = string.join( map(lambda x: "`%s`" % str(x), self._columns), "," )    values_str  = string.join( map(lambda x: "'%s'" % str(x), self._values), "," )    self._sql  = "INSERT INTO "    self._sql += self._table    self._sql += " (%s) VALUES (%s) " % (columns_str, values_str)    return self  def table(self, table):    self._table = table    return self  def column(self, columnName, value):    self._columns.append(columnName)    self._values.append(value)    return selfif __name__ == "__main__":  main()