Quoting Quotes

[JoeBorgione]

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?

[DanPatterson_Retired]

question... do you need two sets of quotes of some form or just 1?

The problem with "print" is that it gets rid of one level of quotes, so

f'"SELECT * FROM master_db.{table}"'  # two levels of quotes
'"SELECT * FROM master_db.blah"'

f"SELECT * FROM master_db.{table}"    # one level of quotes
'SELECT * FROM master_db.blah'‍‍‍‍‍

[JoeBorgione]

Just the one set of double quotes around the select statement as shown in lines 9-13 above....

[DanPatterson_Retired]

The choice between both the types (single quotes and double quotes) depends on the programmer’s choice. Generally, double quotes are used for string representation and single quotes are used for regular expressions, dict keys or SQL. Hence both single quote and double quotes depict string in python but it’s sometimes our need to use one type over the other.

So no matter what, python dumps out single quotes for strings, but dictionary keys and SQL want single ones....? hmmmm

f'SELECT * FROM master_db.{table}'
'SELECT * FROM master_db.blah'

f"SELECT * FROM master_db.{table}"
'SELECT * FROM master_db.blah'

Fun

but is a print the same as a query? hmm

f'"SELECT * FROM master_db.{table}"'
'"SELECT * FROM master_db.blah"'

print(f'"SELECT * FROM master_db.{table}"')
"SELECT * FROM master_db.blah"

Specifying a query in Python—ArcPy Get Started | ArcGIS Desktop 

Using AddFieldDelimiters with the SQL expression

but I could have sworn Joshua or someone said something about it going quietly away

[JoeBorgione]

but is a print the same as a query?

Apparently not....

[JoshuaBixby]

Why are you wanting to wrap your text string in literal double quotes? 

[JoeBorgione]

Just got it to work a moment ago with: 

query = f"SELECT * FROM master_db.{table}"‍

[DanPatterson_Retired]

f"SELECT * FROM master_db.{table}"

I thought you tried that!  see above

[JoeBorgione]

No, I hadn't; reading your suggestions, I wasn't able to make out the quote marks. (Remember, my eyes are just about at retirement age...) 

At any rate, this this whole thing was just a Joe being a cement head momement  I'm not sure what I was thinking; Joshua's post pretty much nails it as does:

[DanPatterson_Retired]

ahhh  I updated the thread tags