Errors inserting many rows into postgreSQL with psycopg2
<p>I have a a number of XML files I need to open and then process to produce a large number of rows that are then inserted into several tables in a remote postgress database.</p>

<p>To extract the XML data I am using <code>xml.etree.ElementTree</code> to parse the XML tree and extract elements as needed. While I am doing a number of things, the basic action is to take a specific element, whether String or Integer and place in one of a number of dictionaries. </p>

<p>After some more processing I have a number of dictionaries that I need to insert into my database. For any single xml file I may produce up to 8-10,000 rows (or queries) across 3 tables. </p>

<p>While testing, I was outputting to sql files and then manually running the query. That is obviously not going to work if I have lots of xml files..</p>

<p>I have therefore tried using <code>psycopg2</code> to automate this process. As I understand it from stack overflow and elsewhere running individual <code>execute</code>  functions is painfully slow. Based on <a href="http://stackoverflow.com/questions/8134602/psycopg2-insert-multiple-rows-with-one-query">This stackoverflow question</a> I have attempted to write code as follows:</p>

<pre><code>QueryData = ','.join(cur.mogrify('(%s,%s,%s)', row) for row in myData)
cur.execute('INSERT INTO DBTABLE' + QueryData)
cur.commit()
</code></pre>

<p>where <code>myData</code> is a list of tuples <code>[(a,b,c),(a,b,c),(a,b,c)...]</code> the contents of which are a combination of data extracted by <code>xml.etree.ElementTree</code> and values I have calculated myself.</p>

<p>When I try to actually execute the above code however I get the following error:</p>

<p><code>TypeError: sequence item 0: expected str instance, bytes found</code></p>

<p>OK... if I then try to convert my data (each tuple element) to <code>str()</code> however I get:</p>

<pre><code>TypeError: encoding without a string argument
</code></pre>

<p>Am I just going about this totally wrong? How can I do what I need? I am using Python3.</p>

<p>ADDITIONAL </p>

<p>I was asked to show an example of the data.</p>

<p>Here is the simplest, it is 3 integer values to put into a table. It is of the form: <code>(document_id,item_index,item_code)</code></p>

<p>A typical example would be: <code>(937, 138, 681)</code></p>

<p>My general attempts to convert have been to try:</p>

<pre><code>(str(document_id),str(item_index),str(item_code))
</code></pre>

<p>I have also tried going the other way:</p>

<pre><code>(bytes(document_id,'utf-8'),bytes(item_index,'utf-8'),bytes(item_code,'utf-8'))
</code></pre>

<p>the latter also raises the error: <code>TypeError: encoding without a string argument</code></p>

<p>you are missing VALUES after the table name, everything else seems correct:</p>

<p>cursorPG.execute("INSERT INTO test <strong>VALUES</strong> "+','.join(cursorPG.mogrify('(%s,%s)',x) for x in mydata))</p>

python postgresql python-3.x xml-parsing psycopg2