Python import XML intro SQLITE (xmltodict)

Question

I'm trying to parse an XML file and import it into an SQLITE database.

the XML looks like this:

<resultset>
    <row>
        <column name="pct_lucru">unit name</column>
        <column name="cod_comercial">00032749</column>
        <column name="denumire_med">stuff name</column>
        <column name="producator">fabri</column>
        <column name="tip_produs">koops</column>
        <column name="tva">24.000000</column>
        <column name="umc">1</column>
        <column name="furnizor">FURNIZORI DIVERSI</column>
        <column name="data_expirarii">2015-12-31</column>
        <column name="lot">80063</column>
        <column name="cant_fl">1</column>
        <column name="fractie">0</column>
        <column name="cantitate">1</column>
        <column name="pret_intr">62.930000</column>
        <column name="val_intr">62.930000</column>
        <column name="pret_fl">82.720000</column>
        <column name="valoare">82.720000</column>
    </row>
</resultset>

And I have the following python code

import xmltodict
import sqlite3

conn = sqlite3.connect("test.sqlite")
c = conn.cursor()

with open("export.xml") as fd:
    obj = xmltodict.parse(fd.read())

for row in obj["resultset"]["row"]:
    for column in row["column"]:
        c.execute("INSERT INTO stocks ? VALUES '?'", [column["@name"], column["#text"]])
    print "item inserted \n"

Which produces the following error

Traceback (most recent call last):
        File "dbimport.py", line 12, in <module>
            c.execute("INSERT INTO stocks ? VALUES '?'", [column["@name"], column["#text"]])
sqlite3.OperationalError: near "?": syntax error

What am I doing wrong here? I've used this method before and it worked just fine, albeit not with XML files.

score 2 · Accepted Answer · answered Jun 19 '15 at 08:13

2

The ? can only be used for values, not for column names. This

INSERT INTO stocks ? VALUES '?'

is not valid. You must use

INSERT INTO stocks (columnname) VALUES (?)

Note the missing quotes around the ?. In code:

c.execute("INSERT INTO stocks ({}) VALUES (?)".format(column["@name"]), column["#text"])

answered Jun 19 '15 at 08:13

Thanks. The query works now, but it doesn't insert anything into the database. Interesting... – Faryus Jun 19 '15 at 08:32
1

Do you commit after the last insert? – Jun 19 '15 at 08:34
Yeah I realized I wasn't committing. Now I have to adjust my code so that it inserts everything on one row. Currently it inserts each value onto it's own row. I need to move a level up in the loop, probably :) http://imgr.ro/images/ab8b8cf5c0.png – Faryus Jun 19 '15 at 08:57

Python import XML intro SQLITE (xmltodict)

1 Answers1