postgresql insert timestamp error with python
<p>I use psycopg2 for postgresql. Here is my snippet:</p>

<pre><code>a = "INSERT INTO tweets (Time) VALUES (%s);" % (datetime.now(),)

cursor.execute(a)
</code></pre>

<p>this won't work and gives me an error:</p>

<pre><code>ProgrammingError: syntax error at or near "20"
LINE 1: INSERT INTO tweets (Time) VALUES (2016-10-03 20:14:49.065092...
</code></pre>

<p>However, if I run this way:</p>

<pre><code>cursor.execute("INSERT INTO tweets (Time) VALUES (%s);", (datetime.now(),))
</code></pre>

<p>it works. I want to know what is the difference between these two expressions, and what is wrong with the first one. Can I do this function use the first structure?</p>

<p>If you check the first query, it states <code>INSERT INTO tweets (Time) VALUES (2016-10-03 20:14:49.065092...</code>, that means, it tries to use unquoted value as a time and this won't work.</p>

<p>If you really want to use your first approach, you have to quote the value:</p>

<pre><code>a = "INSERT INTO tweets (Time) VALUES ('%s');" % (datetime.now(),)
cursor.execute(a)
</code></pre>

<p>I'd suggest you to use the second approach, where client library handles all quotes and usually prevents a lot of possible problems like SQL injection.</p>

python postgresql timestamp psycopg2