AnsweredAssumed Answered

Quoting Quotes

Question asked by jborgion Champion on Jan 23, 2020
Latest reply on Jan 24, 2020 by jborgion

I'm using python to connect to a MySQL database, loop through a list of table names and then create csv files from those tables. (End game is to geocode the csv tables in another process...)

 

I need to pass a standard sql select expression, but I'm having a devil of a time getting it the work correctly.  My code:

def createCSV(table):
   
    cn = mysql.connect(user = 'myUser', password = 'myPassword', host = 'myHost', database = 'master_db')
    cursor = cn.cursor()


    #query = f'"SELECT * FROM master_db.{table}"'
    query = '"SELECT * FROM master_db.{}"'.format(table)
    cursor.execute(query)
   
    rows = cursor.fetchall()
    column_names = [i[0] for i in cursor.description]
    dotcsv = f'{table}.csv'
    outFile = open(os.path.join(outFolder,dotcsv),'w',newline ='')
    myFile = csv.writer(outFile)
    myFile.writerow(column_names)
    myFile.writerows(rows)
   
    cursor.close()


def main():
  tableList = ['businesslicenses','businesslicenseenforcements', 'complaints',
               'buildingpermits','inspections', 'codeenforcementcases'] 
   
  for table in tableList:
    table = table.replace("'","")
    createCSV(table)

if __name__ == '__main__':  #if def main exists, call it
    main() 

The problem is coming from lines 7 or 8; I've tried both f string and .format().  I get the following error:

 

ProgrammingError: You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server
version for the right syntax to use near
'"SELECT * FROM master_db.businesslicenses"' at line 1

 

When I hard code the the select expression to "SELECT * FROM master_db.businesslicenses"  rather than using the for loop, it runs just fine.

 

If I modify the for loop and put a print() function instead of the call to my createCSV() def, no errors:

 

 tableList = ['businesslicenses','businesslicenseenforcements', 'complaints',
             'buildingpermits','inspections', 'codeenforcementcases'] 

for table in tableList:
  table = table.replace("'","")
  #createCSV(table)
  query = f'"SELECT * FROM master_db.{table}"'
  print(query)
"SELECT * FROM master_db.businesslicenses"
"SELECT * FROM master_db.businesslicenseenforcements"
"SELECT * FROM master_db.complaints"
"SELECT * FROM master_db.buildingpermits"
"SELECT * FROM master_db.inspections"
"SELECT * FROM master_db.codeenforcementcases"

Obviously lines 9 through 14 are the output from my print(query) function and the select strings look good to me.

 

What am I missing?

Outcomes