I want to upload a xml file into a PostgreSQL using Django

I am new to Django and my current task is to upload a xml file with 16 fields and more than 60000 rows to a database in PostgreSQL. I used Django to connect to the Database and was able to create a table in the database. I also used XML Etree to parse the xml file. I am having trouble storing the data in the table that I created in the sql database.

This is the code that I used to parse:

import xml.etree.ElementTree as ET

def saveXML2db(): my_file = "C:/Users/Adithyas/myproject/scripts/supplier_lookup.xml"

tree = ET.parse(my_file)
root = tree.getroot()

cols = ["organization", "code", "name"]
rows = []

for i in root:
    organization = i.find("organization").text
    code = i.find("code").text
    name = i.find("name").text
    x = rows.append([organization, code, name])
    
    data = """INSERT INTO records(organization,code,name) VALUES(%s,%s,%s)"""
    
    x.save()
    

saveXML2db()

the code runs without any error, but I am unable to store the data into the table in the SQL database.

Please help

Have you checked any python/PostgreSQL examples? Your code should have something like this (untested):

import psycopg2

def storeXmlToPostgres(xmldata):
    with psycopg2.connect(host="dbhost", database="dbname", user="username", password="password") as conn:
        sql = "INSERT INTO records(organization,code,name) VALUES(%s,%s,%s)"
        cur = conn.cursor()
        for i in xmldata:
            organization = i.find("organization").text
            code = i.find("code").text
            name = i.find("name").text
            cur.execute(sql, [organization, code, name])

So I figured out the answer to my question and I wish to share this with you guys. This is how I imported a xml file to PostgreSQL database using Django ORM:

First, I created a virtual environment to work with: open command prompt in the folder you wish to run the project

py -m venv envy

envy\Scripts\activate

our virtual environment is ready to use then,

pip install django

pip install psycopg2

django-admin startproject projectq

cd projectq

py manage.py startapp myapp

now both our project and app is created and ready to use

code . #to open Visual code

now go to settings.py in 'projectq' and add 'myapp' to INSTALLED_APPS:

INSTALLED_APPS = [

'myapp',#add myapp to the installed apps

]

now to connect our project to PostgreSQL database we have to make some changes in the DATABASES in settings.py as well:

DATABASES = {

'default': {

    'ENGINE': 'django.db.backends.postgresql_psycopg2',
    'NAME': 'projectq',
    'USER': 'postgres',
    'PASSWORD': '1234',
}

}

change dbsqlite to the name of the database that you are using, add name of your Database, username and password

now the connection is established. we move on to the next step

go to models.py to create our table in PostgreSQL to store our xml data:

from django.db import models

# Create your models here.

class Record(models.Model):
   po_organization = models.IntegerField()
   code = models.CharField(max_length = 100)
   name = models.CharField(max_length=100)
   address_1 = models.CharField(max_length=100 , null = True)
   address_2 = models.CharField(max_length=100, null = True)

If your data has null values it's best to add null = True, to avoid errors

py manage.py makemigrations
py manage.py migrate

now the table we created should appear on the PostgreSQL database

next step is to parse our xml file and to import it to the table we created. For that we will use Django ORM queries

open terminal in our visual code in models.py activate virtual environment again

to use ORM query:

py manage.py shell

now add these codes to the interactive console:

>>>from myapp.models import Record
>>>import xml.etree.ElementTree as ET

>>>def data2db():
...file_dir = 'supplier_lookup.xml'
...data = ET.parse(file_dir)
...root = data.findall('record')
...for i in root:
...    organization = i.find('organization').text
...    code = i.find('code').text
...    name = i.find('name').text
...    address_1 = i.find('address_1').text
...    address_2 = i.find('address_2').text
...    x = Record.objects.create(organization=organization, code=code, 
...     name=name, address_1=address_1, address_2=address_2)
...    x.save()
...
>>>data2db()

That's It. The data should be loaded into the database now. Hope this helps.

Back to Top