Select to view content in your preferred language

Quoting Quotes

1356
10
01-23-2020 12:56 PM
JoeBorgione
MVP Emeritus

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?

That should just about do it....
0 Kudos
10 Replies
JoeBorgione
MVP Emeritus

hahahaha....

That should just about do it....
0 Kudos