Select to view content in your preferred language

Quoting Quotes

01-23-2020 12:56 PM
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)
    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)

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

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

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("'","")
  query = f'"SELECT * FROM master_db.{table}"'
"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
MVP Emeritus


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