topic Re: Quoting Quotes in Python Questions

Quoting Quotes

JoeBorgione — Fri, 10 Dec 2021 23:31:19 GMT

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?

Re: Quoting Quotes

DanPatterson_Retired — Fri, 10 Dec 2021 23:31:22 GMT

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'‍‍‍‍‍

Re: Quoting Quotes

JoeBorgione — Thu, 23 Jan 2020 21:22:01 GMT

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

Re: Quoting Quotes

DanPatterson_Retired — Thu, 23 Jan 2020 21:32:23 GMT

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

Re: Quoting Quotes

JoeBorgione — Thu, 23 Jan 2020 21:59:11 GMT

but is a print the same as a query?

Apparently not....

Re: Quoting Quotes

JoshuaBixby — Thu, 23 Jan 2020 23:05:42 GMT

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

Re: Quoting Quotes

JoeBorgione — Thu, 23 Jan 2020 23:51:21 GMT

Just got it to work a moment ago with:

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

Re: Quoting Quotes

DanPatterson_Retired — Fri, 24 Jan 2020 00:07:21 GMT

f"SELECT * FROM master_db.{table}"

I thought you tried that! see above

Re: Quoting Quotes

JoeBorgione — Fri, 24 Jan 2020 12:37:38 GMT

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:

Re: Quoting Quotes

DanPatterson_Retired — Fri, 24 Jan 2020 13:14:16 GMT

ahhh I updated the thread tags

Re: Quoting Quotes

JoeBorgione — Fri, 24 Jan 2020 20:37:07 GMT

hahahaha....