nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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:

<SPAN class="keyword token">def</SPAN> <SPAN class="token function">createCSV</SPAN><SPAN class="punctuation token">(</SPAN>table<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
    
    cn <SPAN class="operator token">=</SPAN> mysql<SPAN class="punctuation token">.</SPAN>connect<SPAN class="punctuation token">(</SPAN>user <SPAN class="operator token">=</SPAN> <SPAN class="string token">'myUser'</SPAN><SPAN class="punctuation token">,</SPAN> password <SPAN class="operator token">=</SPAN> <SPAN class="string token">'myPassword'</SPAN><SPAN class="punctuation token">,</SPAN> host <SPAN class="operator token">=</SPAN> <SPAN class="string token">'myHost'</SPAN><SPAN class="punctuation token">,</SPAN> database <SPAN class="operator token">=</SPAN> <SPAN class="string token">'master_db'</SPAN><SPAN class="punctuation token">)</SPAN>
    cursor <SPAN class="operator token">=</SPAN> cn<SPAN class="punctuation token">.</SPAN>cursor<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>


    <SPAN class="comment token">#query = f'"SELECT * FROM master_db.{table}"'</SPAN>
    query <SPAN class="operator token">=</SPAN> <SPAN class="string token">'"SELECT * FROM master_db.{}"'</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>table<SPAN class="punctuation token">)</SPAN>
    cursor<SPAN class="punctuation token">.</SPAN>execute<SPAN class="punctuation token">(</SPAN>query<SPAN class="punctuation token">)</SPAN>
    
    rows <SPAN class="operator token">=</SPAN> cursor<SPAN class="punctuation token">.</SPAN>fetchall<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
    column_names <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN>i<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">.</SPAN>description<SPAN class="punctuation token">]</SPAN>
    dotcsv <SPAN class="operator token">=</SPAN> f<SPAN class="string token">'{table}.csv'</SPAN>
    outFile <SPAN class="operator token">=</SPAN> open<SPAN class="punctuation token">(</SPAN>os<SPAN class="punctuation token">.</SPAN>path<SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN>outFolder<SPAN class="punctuation token">,</SPAN>dotcsv<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'w'</SPAN><SPAN class="punctuation token">,</SPAN>newline <SPAN class="operator token">=</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">)</SPAN>
    myFile <SPAN class="operator token">=</SPAN> csv<SPAN class="punctuation token">.</SPAN>writer<SPAN class="punctuation token">(</SPAN>outFile<SPAN class="punctuation token">)</SPAN>
    myFile<SPAN class="punctuation token">.</SPAN>writerow<SPAN class="punctuation token">(</SPAN>column_names<SPAN class="punctuation token">)</SPAN>
    myFile<SPAN class="punctuation token">.</SPAN>writerows<SPAN class="punctuation token">(</SPAN>rows<SPAN class="punctuation token">)</SPAN>
    
    cursor<SPAN class="punctuation token">.</SPAN>close<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>


<SPAN class="keyword token">def</SPAN> <SPAN class="token function">main</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
  tableList <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'businesslicenses'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'businesslicenseenforcements'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'complaints'</SPAN><SPAN class="punctuation token">,</SPAN>
               <SPAN class="string token">'buildingpermits'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'inspections'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'codeenforcementcases'</SPAN><SPAN class="punctuation token">]</SPAN>  
    
  <SPAN class="keyword token">for</SPAN> table <SPAN class="keyword token">in</SPAN> tableList<SPAN class="punctuation token">:</SPAN>
    table <SPAN class="operator token">=</SPAN> table<SPAN class="punctuation token">.</SPAN>replace<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"'"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">""</SPAN><SPAN class="punctuation token">)</SPAN>
    createCSV<SPAN class="punctuation token">(</SPAN>table<SPAN class="punctuation token">)</SPAN>

<SPAN class="keyword token">if</SPAN> __name__ <SPAN class="operator token">==</SPAN> <SPAN class="string token">'__main__'</SPAN><SPAN class="punctuation token">:</SPAN>  <SPAN class="comment token">#if def main exists, call it</SPAN>
    main<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>  <SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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

ProgrammingError<SPAN class="punctuation token">:</SPAN> You have an error <SPAN class="keyword token">in</SPAN> your SQL syntax<SPAN class="punctuation token">;</SPAN> 
check the manual that corresponds to your MySQL server 
version <SPAN class="keyword token">for</SPAN> the right syntax to use near 
<SPAN class="string token">'"SELECT * FROM master_db.businesslicenses"'</SPAN> at line <SPAN class="number token">1</SPAN>‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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 <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'businesslicenses'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'businesslicenseenforcements'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'complaints'</SPAN><SPAN class="punctuation token">,</SPAN>
             <SPAN class="string token">'buildingpermits'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'inspections'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'codeenforcementcases'</SPAN><SPAN class="punctuation token">]</SPAN>  

<SPAN class="keyword token">for</SPAN> table <SPAN class="keyword token">in</SPAN> tableList<SPAN class="punctuation token">:</SPAN>
  table <SPAN class="operator token">=</SPAN> table<SPAN class="punctuation token">.</SPAN>replace<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"'"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">""</SPAN><SPAN class="punctuation token">)</SPAN>
  <SPAN class="comment token">#createCSV(table)</SPAN>
  query <SPAN class="operator token">=</SPAN> f<SPAN class="string token">'"SELECT * FROM master_db.{table}"'</SPAN>
  <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN>query<SPAN class="punctuation token">)</SPAN>
<SPAN class="string token">"SELECT * FROM master_db.businesslicenses"</SPAN>
<SPAN class="string token">"SELECT * FROM master_db.businesslicenseenforcements"</SPAN>
<SPAN class="string token">"SELECT * FROM master_db.complaints"</SPAN>
<SPAN class="string token">"SELECT * FROM master_db.buildingpermits"</SPAN>
<SPAN class="string token">"SELECT * FROM master_db.inspections"</SPAN>
<SPAN class="string token">"SELECT * FROM master_db.codeenforcementcases"</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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?

Find more posts tagged with

Accepted answers

All comments

JoeBorgione

hahahaha....

DanPatterson_Retired

ahhh I updated the thread tags

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

f"SELECT * FROM master_db.{table}"

I thought you tried that! see above

JoeBorgione

Just got it to work a moment ago with:

query <SPAN class="operator token">=</SPAN> f<SPAN class="string token">"SELECT * FROM master_db.{table}"</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

JoeBorgione

but is a print the same as a query?

Apparently not....

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

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

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'<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.