convert_dictionary_to_mysql_table (function) ∞
-
convert_dictionary_to_mysql_table
(log, dictionary, dbTableName, uniqueKeyList=[], dbConn=False, createHelperTables=False, dateModified=False, returnInsertOnly=False, replace=False, batchInserts=True, reDatetime=False, skipChecks=False, dateCreated=True)[source] ∞ convert dictionary to mysql table
Key Arguments
log
– loggerdictionary
– python dictionarydbConn
– the db connectiondbTableName
– name of the table you wish to add the data to (or create if it does not exist)uniqueKeyList
- a lists column names that need combined to create the primary keycreateHelperTables
– create some helper tables with the main table, detailing original keywords etcreturnInsertOnly
– returns only the insert command (does not execute it)dateModified
– add a modification date and updated flag to the mysql tablereplace
– use replace instead of mysql insert statements (useful when updates are required)batchInserts
– if returning insert statements return separate insert commands and value tuplesreDatetime
– compiled regular expression matching datetime (passing this in cuts down on execution time as it doesn’t have to be recompiled everytime during multiple iterations ofconvert_dictionary_to_mysql_table
)skipChecks
– skip reliability checks. Less robust but a little faster.dateCreated
– add a timestamp for dateCreated?
Return
returnInsertOnly
– the insert statement if requested
Usage
To add a python dictionary to a database table, creating the table and/or columns if they don’t yet exist:
from fundamentals.mysql import convert_dictionary_to_mysql_table dictionary = {"a newKey": "cool", "and another": "super cool", "uniquekey1": "cheese", "uniqueKey2": "burgers"} convert_dictionary_to_mysql_table( dbConn=dbConn, log=log, dictionary=dictionary, dbTableName="testing_table", uniqueKeyList=["uniquekey1", "uniqueKey2"], dateModified=False, returnInsertOnly=False, replace=True )
Or just return the insert statement with a list of value tuples, i.e. do not execute the command on the database:
insertCommand, valueTuple = convert_dictionary_to_mysql_table( dbConn=dbConn, log=log, dictionary=dictionary, dbTableName="testing_table", uniqueKeyList=["uniquekey1", "uniqueKey2"], dateModified=False, returnInsertOnly=True, replace=False, batchInserts=True ) print(insertCommand, valueTuple) # OUT: 'INSERT IGNORE INTO `testing_table` # (a_newKey,and_another,dateCreated,uniqueKey2,uniquekey1) VALUES # (%s, %s, %s, %s, %s)', ('cool', 'super cool', # '2016-06-21T12:08:59', 'burgers', 'cheese')
You can also return a list of single insert statements using
batchInserts = False
. Usingreplace = True
will also add instructions about how to replace duplicate entries in the database table if found:inserts = convert_dictionary_to_mysql_table( dbConn=dbConn, log=log, dictionary=dictionary, dbTableName="testing_table", uniqueKeyList=["uniquekey1", "uniqueKey2"], dateModified=False, returnInsertOnly=True, replace=True, batchInserts=False ) print(inserts) # OUT: INSERT INTO `testing_table` (a_newKey,and_another,dateCreated,uniqueKey2,uniquekey1) # VALUES ("cool" ,"super cool" ,"2016-09-14T13:12:08" ,"burgers" ,"cheese") # ON DUPLICATE KEY UPDATE a_newKey="cool", and_another="super # cool", dateCreated="2016-09-14T13:12:08", uniqueKey2="burgers", # uniquekey1="cheese"