read_sql and redshift giving error on unicode
<p>Query 1: Using pandas read_sql to read from MySQL. The resulting dataframe has a column whose datatype is unicode strings. This column is converted to a tuple and used in the following query.</p>

<p>Query 2: Using pandas read_sql to read from Redshift. The query is something like </p>

<pre><code>select b.a from b where b.c in {0}
</code></pre>

<p>On one string, it gives me an error. The string is like u"Hello 'There" which is a valid unicode string. 
The error is </p>

<pre><code>syntax error at or near ""Hello 'There""
</code></pre>

<p>But it shouldn't take it that way. Its treating it as an empty string ("") followed by un-understandable symbol like (Hello 'There"")</p>

<p>Should some configuration be changed or some parameters in read_sql to be added</p>

<p>I suspect that that error message is being generated by someone replacing a marker (I've used <code>{something}</code>) a template like</p>

<pre><code>'syntax error at or near "{something}"'
</code></pre>

<p>This means you should instead see it as a complaint about a single string whose value would appear to be</p>

<pre><code>'"Hello \' There"'
</code></pre>

<p>In other words, it seems likely that you are dealing with a fifteen-character string starting and ending in a double-quote character, though there is always the possibility that some inappropriate conversion or quoting has added the double-quotes. No code, no traceback, so difficult to tell.</p>

<p>It seems as though somewhere in the processing chain there is a failure to correctly handle single apostrophes in SQL string literals. In SQL that string would be correctly represented as</p>

<pre><code>'"Hello, ''There"'
</code></pre>

<p>You will observe that the single-quote embedded in the string had had to be doubled to represent it - the SQL syntax specifically denotes this as the correct way to embed apostrophes in SQL string constants. Since you don't actually show details of your code there is a limit to how helpful this answer can be.</p>

<p>I suspect you might be writing your own SQL, in which case given a query in the variable <code>sql</code> you might want to consider using</p>

<pre><code>sql.replace("'", "''")
</code></pre>

<p>as the value injected into the query. But beware all the usual warnings about SQL injection if you plan to incorporate user inputs into this scheme.</p>

python mysql pandas unicode amazon-redshift