def insertIntoTable(self, tableName, valueList):
"""
Insert values from "valueList" into the table with name "tableName".
The inserted values have name list "nameList" and types specified in
"dataTypeStringList". For example:
tableName = "test"
valueList = [(1,), (2,), (3,)]
nameList = ["id"]
dataTypeStringList = ["int"]
The table has to be already created (not checked).
"""
# make valueList doubly nested
if not ListRNew.isIterable(valueList):
valueList = [valueList]
if not ListRNew.isIterable(valueList[0]):
valueList = [
valueList
] # nest the level 1 list to level 2 assuming we are given a single value list
# perform SQL
dataLength = len(valueList[0]) # get number of elements in a row
returnValue = self._executeSQL("insert into %s values (%s)" %
(tableName, ",".join("?" * dataLength)),
valueList,
many=True) # perform insertion
return returnValue
def selectFromTable(self,
tableName,
columnNameList="*",
whereClause="",
groupByClause="",
orderByClause=""):
"""
Return the specified columns with names given in columnNameList from
the table with name tableName. The columnNameList will be joined
with a space to be inserted into the SQL query command. The
whereClause string argument is appended to the query after the
keyword "where"; the orderByClause string argument is appended to
the query after the keyword "order by".
"""
if not ListRNew.isIterable(columnNameList):
columnNameList = [columnNameList]
columnNameListSQLString = ",".join(columnNameList) # make is SQL-like
# perform SQL
sqlCommand = "select %s from %s" % (columnNameListSQLString, tableName)
if whereClause:
sqlCommand += " where " + whereClause
if groupByClause:
sqlCommand += " group by " + groupByClause
if orderByClause:
sqlCommand += " order by " + orderByClause
returnValue = self._executeSQL(sqlCommand).fetchall()
return returnValue
def insertIntoTable(self, tableName, valueList):
"""
Insert values from "valueList" into the table with name "tableName".
The inserted values have name list "nameList" and types specified in
"dataTypeStringList". For example:
tableName = "test"
valueList = [(1,), (2,), (3,)]
nameList = ["id"]
dataTypeStringList = ["int"]
The table has to be already created (not checked).
"""
# make valueList doubly nested
if not ListRNew.isIterable(valueList):
valueList = [valueList]
if not ListRNew.isIterable(valueList[0]):
valueList = [valueList] # nest the level 1 list to level 2 assuming we are given a single value list
# perform SQL
dataLength = len(valueList[0]) # get number of elements in a row
returnValue = self._executeSQL("insert into %s values (%s)" % (tableName, ",".join("?"*dataLength)), valueList, many=True) # perform insertion
return returnValue
def createTableIfNotExists(self, tableName, nameAndTypeList):
"""
Create a table with name "tableName" if it does not exist already.
The argument "nameAndTypeList" is a list of pair of strings that
specifies the names and data type of the columns. For example:
(("id", "integer"), ("name", "text")).
Returns False if the table already exists.
"""
# check if table name is legal
tableName = tableName.strip()
if " " in tableName:
raise sqlite3.OperationalError("SQL table name cannot contain blanks")
# refine input arguments
if not ListRNew.isIterable(nameAndTypeList[0]):
nameAndTypeList = [nameAndTypeList]
# create the table
if not self.doesTableExist(tableName):
return self._executeSQL( "create table %s (%s)" % (tableName, ",".join(map(" ".join, nameAndTypeList))) )
else:
return False
def selectFromTable(self, tableName, columnNameList="*", whereClause="", groupByClause="", orderByClause=""):
"""
Return the specified columns with names given in columnNameList from
the table with name tableName. The columnNameList will be joined
with a space to be inserted into the SQL query command. The
whereClause string argument is appended to the query after the
keyword "where"; the orderByClause string argument is appended to
the query after the keyword "order by".
"""
if not ListRNew.isIterable(columnNameList):
columnNameList = [columnNameList]
columnNameListSQLString = ",".join(columnNameList) # make is SQL-like
# perform SQL
sqlCommand = "select %s from %s" % (columnNameListSQLString, tableName)
if whereClause:
sqlCommand += " where " + whereClause
if groupByClause:
sqlCommand += " group by " + groupByClause
if orderByClause:
sqlCommand += " order by " + orderByClause
returnValue = self._executeSQL(sqlCommand).fetchall()
return returnValue