Pulling XML from an API and saving to SQL database with Python

Question

Not a programmer- could use some help with understanding/best practice for bringing xml files into a SQL database. Here's some code and commentary to understand where I'm at now:

import pyodbc
import requests
import xml.etree.ElementTree as ET

Grabbing the XML file from the API:

requestURL = 'myURL.com'
response = requests.get(requestURL)

This is where I start losing understanding. Mostly pieced together from various tutorials:

root = ET.fromstring(response.text)
tree = ET.ElementTree(root)
for child in root:
    print(child.attrib)

The output looks like this:

{'date': '1986-01-02', 'value': '0.90'}
{'date': '1986-01-03', 'value': '0.99'}
{'date': '1986-01-06', 'value': '1.07'}
...

Now I login into the database I want to write the information above into:

#establish connection with server
conn = pyodbc.connect(driver='{SQL Server}', 
                  server=myServer, 
                  database=myDatabase,
                  user=myUsername,
                  password=myPassword)

cursor = conn.cursor()
cursor.execute('SELECT * FROM mySeries WHERE series_id = myID')

for row in cursor:
    print(row)

Output looks like this:

(myID, '1986-01-02', Decimal('0.90'))
(myID, '1986-01-03', Decimal('0.99'))
(myID, '1986-01-06', Decimal('1.07'))
...

So the next step is checking the database to see what data points I'm missing (new data added daily) and appending data onto the end of the series. I'm pretty sure I should be using SQL in python to accomplish that, but I'm just a little confused on the best way to write the XML data into the database.

Any help would be greatly appreciated.

ajrice6713 · Accepted Answer · 2019-09-04 23:06:41Z

0

This might be what you're looking for.

Will the XML files only contain new database records? Or will they contain records that could be duplicates, and are you trying to avoid duplication? If so, does each record in your SQL database have a unique identifier you could easily search and match up to eliminate duplication from the XML?

I believe you can grab data from the XML files using the following

# load XML doc
# Open SQL Connection
for child in root:
    date = child.get('date')
    value = child.get('value')
    # enter variables into SQL server

Within your loop, add each individual record to your SQL as you iterate through the XML.

(I'm fairly new to this as well, so I hope this does more help than harm!)

answered Sep 4, 2019 at 23:06

ajrice6713

951 silver badge11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pulling XML from an API and saving to SQL database with Python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related