Python insert to database only one data from tuple

Question

I have a problem. I need parse muliple xml files and insert data to database.

import os 
from lxml import etree
import sqlite3
conn = sqlite3.connect("xml.db")
cursor = conn.cursor()
path = 'C:/tools/XML'
for filename in os.listdir(path):
    fullname = os.path.join(path, filename)
    tree = etree.parse(fullname)
    test = tree.xpath('//*[@name="Name"]/text()')
    tpl = tuple(test)
    cursor.executemany("INSERT INTO parsee VALUES (?);", (tpl,))
conn.commit()
sql = "SELECT * FROM parsee"
cursor.execute(sql)
print(cursor.fetchall())

result:

[('testname1',)]

If I run the program again the program adds another same name. Result:

[('testname1',),('testname1',)]

There are 100 files in folder:

<curent name="Name">testname1<curent>
<curent name="Name">testname2<curent>
<curent name="Name">testname3<curent>
<curent name="Name">testname4<curent>

Possible duplicate of ["Insert if not exists" statement in SQLite](https://stackoverflow.com/questions/19337029/insert-if-not-exists-statement-in-sqlite) — Azat Ibrakov, Apr 02 '19 at 13:49
forgot to say variable tpl looks like this: print(type(tpl)) [link](https://ibb.co/YNvZjbT) — vicukalebi, Apr 02 '19 at 15:22

Gro · Answer 1 · 2019-04-04T05:34:04.890

Since I don't have admin rights to install lxml on my computer, I will use a battery (class) that is included in Python by default to deal with XPATHs - xml.etree.ElementTree. However, my code will show you how to insert multiple records in SQLITE using executemany()

It looks like in C:/tools/XML you will have many xml files of same structure. I put following two in a folder to simulate this (I noted that your example has 'curent' as element, not sure if it is a typo, I am using 'current')

file1.xml

<note>
    <current name="Name">testname1</current>
    <current name="Name">testname2</current>
    <otherdetail></otherdetail>    
</note>

file2.xml

<note>
    <current name="Name">testname3</current>
    <current name="Name">testname4</current>
    <otherdetail></otherdetail>    
</note>

Created an sqlite database case called xml.db and a table in it with following statement

CREATE TABLE PARSEE (NAME VARCHAR(100));

And here is my python script

import os 
import xml.etree.ElementTree as ET
import sqlite3
conn = sqlite3.connect("xml.db")
cursor = conn.cursor()
path = 'C:/tools/XML'
for filename in os.listdir(path):
    fullname = os.path.join(path, filename)
    print("Parsing file: %s" %fullname)
    tree = ET.parse(fullname)
    root = tree.getroot()
    elements = root.findall(".//*[@name='Name']");
    names = [(e.text,)  for e in elements]
    print("Names found: %s" %names)
    cursor.executemany("INSERT INTO PARSEE VALUES (?)", names)
conn.commit()
sql = "SELECT * FROM PARSEE"
print("Printing table PARSEE content")
cursor.execute(sql)
print(cursor.fetchall())

And here is the output

Python insert to database only one data from tuple

1 Answers1