def getheadersfromtable(configfile, section, tabletitle):
cnxn = connect(configfile, section)
cursor = cnxn.cursor()
query = r"SELECT column_name FROM information_schema.columns WHERE table_name = '%s';"
cursor.execute(query, (tabletitle))
cnxn.commit()
cursor.close()
cnxn.close()
def deleteallrows(configfile, section, tabletitle):
cnxn = connect(configfile, section)
cursor = cnxn.cursor()
query = "DELETE FROM %s" % tabletitle
cursor.execute(query)
cnxn.commit()
cursor.close()
cnxn.close()
def createtable(configfile, section, tabletitle, headers):
cnxn = connect(configfile, section)
cursor = cnxn.cursor()
query = "CREATE TABLE %s (%s);" % (tabletitle, headers)
cursor.execute(query)
cnxn.commit()
cursor.close()
cnxn.close()
def selectonequery(configfile, section, sqlquery):
'''connects to a mysql database and makes a query to that database. Need to input a configuration
file with host, user, password etc.'''
cnxn = connect(configfile, section)
cursor = cnxn.cursor()
cursor.execute(sqlquery)
result = cursor.fetchone()
cursor.close()
cnxn.close()
return result
def insertmanyquery(configfile, section, tabletitle, headers, values):
'''connects to a mysql database and inserts a list of tabe delimited rows into a table'''
cnxn = connect(configfile, section)
cursor = cnxn.cursor()
valnum = r"%s, " * len(headers.split(','))
valnum = valnum[:-2]
query = "INSERT INTO %s (%s) VALUES (%s)" % (tabletitle, headers, valnum)
cursor.executemany(query, values)
cnxn.commit()
cursor.close()
cnxn.close()
def selectallquery(configfile, section, tabletitle, headers, whereheadsvals):
'''connects to a mysql database and makes a select query to that database. Need to input a configuration
file with host, user, password etc. Returns a list of the result of the select query '''
cnxn = connect(configfile, section)
cursor = cnxn.cursor()
conditional = ' AND '.join(
[key + ' = %(' + key + ')s' for key in whereheadsvals])
query = "SELECT {0} FROM {1} WHERE {2}".format(headers, tabletitle,
conditional)
cursor.execute(query, whereheadsvals)
result = cursor.fetchall()
cursor.close()
cnxn.close()
return result