Parse xml and store into mysql table in python [closed]

Question

I have written the code that retrieves the data from XML file using xml.etree. Is there any best way to store the data into database by parsing an XML file.

My code:

from xml.etree import ElementTree
import mysql.connector

dom = ElementTree.parse('profile.xml')

ticker = dom.findall('TICKER')
name = dom.findall('NAME')
address = dom.findall('ADDRESS')
phone = dom.findall('PHONE')
website = dom.findall('WEBSITE')
sector = dom.findall('SECTOR')
industry = dom.findall('INDUSTRY')
full_time = dom.findall('FULL_TIME')
bus_summ = dom.findall('BUS_SUMM')

ticker_list = [t.text for t in ticker]
name_list = [t.text for t in name]
add_list = [t.text for t in address]
phn_list = [t.text for t in phone]
site_list = [t.text for t in website]
sec_list = [t.text for t in sector]
ind_list = [t.text for t in industry]
emp_list = [t.text for t in full_time]
sum_list = [t.text for t in bus_summ]

db = mysql.connector.Connect(host = 'localhost', user = 'root', password ='root' , database = 'nldb_project')
cur = db.cursor()
query = "INSERT INTO profiles(`prof_ticker`,`name`,`address`,`phonenum`,`website`,`sector`,`industry`,full_time`,`bus_summ`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"

sqltuples = [(t,n,a,p,s,sec,i,e,su) for t,n,a,p,s,sec,i,e,su in zip(ticker_list,name_list,add_list,phn_list,site_list,sec_list,ind_list,emp_list,sum_list)]
cur.executemany(query,sqltuples)

I am using python 3.6.5 version.

Here is my XML code:

<?xml version="1.0"?>
<collection shelf = 'profile'>
<INFO>
    <TICKER>AAPL</TICKER>
    <NAME> Apple Inc.</NAME>
    <ADDRESS>1 Infinite Loop;Cupertino, CA 95014;United State</ADDRESS>
    <PHONE>408-996-1010</PHONE>
    <WEBSITE>http://www.apple.com</WEBSITE>
    <SECTOR>Technology</SECTOR>
    <INDUSTRY>Consumer Electronics</INDUSTRY>
    <FULL_TIME>100,000</FULL_TIME>
    <BUS_SUMM>Apple</BUS_SUMM>
    <SOURCE>https://finance.yahoo.com/quote/AAPL/profile?p=AAPL</SOURCE> 
</INFO>
<INFO>
    <TICKER>T</TICKER>
    <NAME> AT and T Inc.</NAME>
    <ADDRESS>208 South Akard Street;Dallas, TX 75202;United States</ADDRESS>
    <PHONE>210-821-4105</PHONE>
    <WEBSITE>http://www.att.com</WEBSITE>
    <SECTOR>Communication Services</SECTOR>
    <INDUSTRY> Telecom Services</INDUSTRY>
    <FULL_TIME>254,000</FULL_TIME>
    <BUS_SUMM>at and t</BUS_SUMM>
    <SOURCE>https://finance.yahoo.com/quote/T/profile?p=T</SOURCE>
</INFO>
<INFO>
    <TICKER>IBM</TICKER>
    <NAME>International Business Machines Corporation</NAME>
    <ADDRESS>1 New Orchard Road;Armonk, NY 10504;United States</ADDRESS>
    <PHONE>914-499-1900</PHONE>
    <WEBSITE>http://www.ibm.com</WEBSITE>
    <SECTOR>Technology</SECTOR>
    <INDUSTRY> Information Technology Services</INDUSTRY>
    <FULL_TIME>366,600</FULL_TIME>
    <BUS_SUMM>ibm</BUS_SUMM>
    <SOURCE>https://finance.yahoo.com/quote/IBM/profile?p=IBM</SOURCE>
</INFO>
<INFO>
    <TICKER>TWTR</TICKER>
    <NAME>Twitter,Inc.</NAME>
    <ADDRESS>1355 Market Street;Suite 900;San Francisco, CA 94103;United States</ADDRESS>
    <PHONE>415-222-9670</PHONE>
    <WEBSITE>http://www.twitter.com</WEBSITE>
    <SECTOR>Technology</SECTOR>
    <INDUSTRY>Internet Content Information</INDUSTRY>
    <FULL_TIME>3,372</FULL_TIME>
    <BUS_SUMM>twitter</BUS_SUMM>
    <SOURCE>https://finance.yahoo.com/quote/TWTR/profile?p=TWTR</SOURCE>
</INFO>
<INFO>
    <TICKER>TSLA</TICKER>
    <NAME>Tesla,Inc.</NAME>
    <ADDRESS>3500 Deer Creek Road;Palo Alto, CA 94304;United States</ADDRESS>
    <PHONE>650-681-5000</PHONE>
    <WEBSITE>http://www.tesla.com</WEBSITE>
    <SECTOR>Consumer Cyclical</SECTOR>
    <INDUSTRY>Auto Manufacturers</INDUSTRY>
    <FULL_TIME>37,543</FULL_TIME>
    <BUS_SUMM>tesla</BUS_SUMM>
    <SOURCE>https://finance.yahoo.com/quote/TSLA/profile?p=TSLA</SOURCE>
</INFO>
<INFO>
    <TICKER>PYPL</TICKER>
    <NAME>PayPal Holdings, Inc.</NAME>
    <ADDRESS>2211 North First Street;San Jose, CA 95131;United States</ADDRESS>
    <PHONE>408-967-1000</PHONE>
    <WEBSITE>http://www.paypal.com</WEBSITE>
    <SECTOR>Financial Services</SECTOR>
    <INDUSTRY>Credit Services</INDUSTRY>
    <FULL_TIME>18,700</FULL_TIME>
    <BUS_SUMM>paypal</BUS_SUMM>
    <SOURCE>https://finance.yahoo.com/quote/PYPL/profile?p=PYPL</SOURCE>
</INFO>
</collection>

Answer 1

I didn't test your code but there's a big block of copy-paste only to generate the arguments to pass to zip.

So the alternative is to generate the arguments "anonymously" from the tag names only, and pass to zip using argument unpacking.

(changed from comments: findall => iter)

Also, no need to unpack the arguments just to pass them exactly as-is in the zip loop. Just convert the result of zip to a list

Here's a much shorter version of your code. If you add more tags, you just have one item to add in the tag list & in the query now:

from xml.etree import ElementTree
import mysql.connector

dom = ElementTree.parse('profile.xml')

# compose the argument list in one line, drop the big copied/pasted block
args_list = ([t.text for t in dom.iter(tag)] for tag in ['TICKER','NAME','ADDRESS','PHONE','WEBSITE','SECTOR','INDUSTRY','FULL_TIME','BUS_SUMM'])

# db connection code is unchanged
db = mysql.connector.Connect(host = 'localhost', user = 'root', password ='root' , database = 'nldb_project')
cur = db.cursor()
# this part could be optimized with a dictionary to relate to the tag names
query = "INSERT INTO profiles(`prof_ticker`,`name`,`address`,`phonenum`,`website`,`sector`,`industry`,full_time`,`bus_summ`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"

# create the tuples from the argument list
sqltuples = list(zip(*args_list))

# execute query (unchanged)
cur.executemany(query,sqltuples)