0
\$\begingroup\$

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>
\$\endgroup\$

closed as off-topic by t3chb0t, Ludisposed, Stephen Rauch, Sᴀᴍ Onᴇᴌᴀ, Mast May 4 '18 at 15:12

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Code not implemented or not working as intended: Code Review is a community where programmers peer-review your working code to address issues such as security, maintainability, performance, and scalability. We require that the code be working correctly, to the best of the author's knowledge, before proceeding with a review." – t3chb0t, Ludisposed, Stephen Rauch, Sᴀᴍ Onᴇᴌᴀ, Mast
If this question can be reworded to fit the rules in the help center, please edit the question.

  • \$\begingroup\$ It is impossible that your code works. \$\endgroup\$ – Billal Begueradj May 4 '18 at 6:00
  • 1
    \$\begingroup\$ Have you even checked how findall() works? \$\endgroup\$ – Billal Begueradj May 4 '18 at 6:01
  • 1
    \$\begingroup\$ It doesn't show any error but is not entering into the database. @Begueradj \$\endgroup\$ – band_cherry May 4 '18 at 6:13
  • \$\begingroup\$ @band_cherry You most likely want to use iter instead of findall. If the change makes the code work as intended, please edit it in so we can make meaningful review. \$\endgroup\$ – 409_Conflict May 4 '18 at 8:03
  • 3
    \$\begingroup\$ Does the code function correctly? If not, it isn't ready for review (see help center) and the question may be deleted. If you've tested it, I recommend that you edit to add a summary of the testing. \$\endgroup\$ – Toby Speight May 4 '18 at 8:47
0
\$\begingroup\$

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)
\$\endgroup\$
  • \$\begingroup\$ Thanks alot for providing me the useful information by making the code simpler. \$\endgroup\$ – band_cherry May 4 '18 at 8:59

Not the answer you're looking for? Browse other questions tagged or ask your own question.