Python XML tartalom db-be töltés

 

---

1

#  kl.xml tartalma

2

<?xml version="1.0"?>

3

<people>

4

 <person>

5

 <firstName>Alan</firstName>

6

 <lastName>Turing</lastName>

7

 <profession>Computer Scientist</profession>

8

 </person>

9

 <person>

10

 <firstName>Stephen</firstName>

11

 <lastName>Hawking</lastName>

12

 <profession>Physicist</profession>

13

 </person>

14

</people>

15

​

In [20]:

1

import pandas as pd

2

import xml.etree.ElementTree

3

​

4

people = xml.etree.ElementTree.parse(r"c:\Users\User\Documents\data\xml\kl.xml")

5

persons = people.findall("person")

6

for person in persons:

7

    print (person.find("firstName").text)

8

    print (person.find("lastName").text)

9

    print (person.find("profession").text)

10

11

print('----------------------')

12

​

13

## XML olvasása és paraméter változókba gyújtés

14

people = xml.etree.ElementTree.parse(r"c:\Users\User\Documents\data\xml\kl.xml")

15

args_list = ([t.text for t in people.iter(tag)] for tag in ['firstName','lastName','profession'])

16

print( list(zip(*args_list)))

17

​

Alan
Turing
Computer Scientist
Stephen
Hawking
Physicist
----------------------
[('Alan', 'Turing', 'Computer Scientist'), ('Stephen', 'Hawking', 'Physicist')]

In [9]:

1

##sqlite db

2

​

3

import sqlite3

4

conn = sqlite3.connect("people.sqlite3")

5

curs = conn.cursor()

6

##üres tábla létrehozása, ha még nem létezik

7

​

8

curs.execute('''

9

create table if not exists person (

10

 person_id integer primary key autoincrement,

11

 first_name text,

12

 last_name text,

13

 profession text

14

);

15

''')

16

​

17

​

18

​

19

## beírás

20

curs.execute("insert into person values (?, ?, ?, ?)", (2, "Andy", "Register", "EE"))

21

print(curs.rowcount)

22

## ternsakció kommitálás

23

conn.commit()

24

​

25

## kiolvasás

26

curs.execute("select * from person")

27

for row in curs.fetchall():

28

    print(row)

29

​

30

31

##------ eljárás deffinició

32

def insert_person( person_id, first_name, last_name, profession):

33

    curs = db_conn.cursor()

34

    curs.execute("insert into person values ( ?, ?, ?)",

35

    (person_id, first_name, last_name, profession))

36

    db_conn.commit()

37

​

38

insert_person(conn, 22, "Lajos", "Kecskeméti", "Student")

39

​

40

## erőforrás felszabadítás

41

curs.close()

42

conn.close()

43

​

1
(1, 'Andy', 'Register', 'EE')
(2, 'Andy', 'Register', 'EE')

In [29]:

1

##sqlite db

2

​

3

import sqlite3

4

conn = sqlite3.connect("people.sqlite3")

5

curs = conn.cursor()

6

​

7

## paraméteres sQL parancs

8

query = "INSERT INTO person( first_name, last_name, profession) VALUES ( ?, ?, ?);"

9

​

10

## XML olvasása és paraméter változókba gyújtés

11

people = xml.etree.ElementTree.parse(r"c:\Users\User\Documents\data\xml\kl.xml")

12

args_list = ([t.text for t in people.iter(tag)] for tag in ['firstName','lastName','profession'])

13

​

14

## paraméter tuples generálása

15

sqltuples = list(zip(*args_list))

16

​

17

print(sqltuples)

18

print('----------------------------')

19

​

20

# SQL parancs futtatása

21

curs.executemany(query,sqltuples)

22

conn.commit()

23

​

24

​

25

## kiolvasás

26

curs.execute("select * from person")

27

for row in curs.fetchall():

28

    print(row)

29

​

30

## erőforrás felszabadítás

31

curs.close()

32

conn.close()

[('Alan', 'Turing', 'Computer Scientist'), ('Stephen', 'Hawking', 'Physicist')]
----------------------------
(1, 'Andy', 'Register', 'EE')
(2, 'Andy', 'Register', 'EE')
(22, 'Lajos', 'Kecskeméti', 'Student')
(23, 'Alan', 'Turing', 'Computer Scientist')
(24, 'Stephen', 'Hawking', 'Physicist')
(25, 'Alan', 'Turing', 'Computer Scientist')
(26, 'Stephen', 'Hawking', 'Physicist')

In [ ]:

1

### oracle db

2

import cx_Oracle

3

SQL = ''.join([ 'SELECT alias.COLUMN_NAME.getClobVal() FROM XML_TABLE ])

4

5

## adatbázis kapcsolat

6

conn = cx_Oracle.connect('username/password@database')

7

curs = conn.cursor()

8

9

## ciklus               

10

for i in range(num_rows):

11

     file_name = i

12

     file_path = ''.join([ 'C:\..., file_name, '.xml ])

13

     file = open(file_path, 'w')

14

     curs.execute(SQL)

15

     oracle_data = cx_Oracle.Cursor.fetchone(cursor_oracle_data)[0]

16

     xml_data = str(oracle_data)

17

18

## filebe kiírás

19

     with open(file_path, 'w') as file:

20

         file.write(xml_data)

21

​

22

     file.close()

23

24

## erőforrás felszabadítás

25

curs.close()

26

conn.close()

In [42]:

1

import csv 

2

import requests 

3

import xml.etree.ElementTree as ET 

4

5

def loadRSS(): 

6

7

    # url of rss feed 

8

    url = 'https://mierdekel.hu/blog/feed/'

9

10

    # creating HTTP response object from given url 

11

    resp = requests.get(url) 

12

13

    # saving the xml file 

14

    with open('mierdekelhufeed.xml', 'wb') as f: 

15

        f.write(resp.content) 

16

17

def parseXML(xmlfile): 

18

19

    # create element tree object 

20

    tree = ET.parse(xmlfile) 

21

22

    # get root element 

23

    root = tree.getroot() 

24

25

    # create empty list for news items 

26

    newsitems = [] 

27

28

    # iterate news items 

29

    for item in root.findall('./channel/item'): 

30

31

        # empty news dictionary 

32

        news = {} 

33

34

        # iterate child elements of item 

35

        for child in item: 

36

37

            # special checking for namespace object content:media 

38

            if child.tag == '{http://search.yahoo.com/mrss/}content': 

39

                news['media'] = child.attrib['url'] 

40

            else: 

41

                news[child.tag] = child.text.encode('utf8') 

42

43

        # append news dictionary to news items list 

44

        newsitems.append(news) 

45

46

    # return news items list 

47

    return newsitems 

48

49

50

def savetoCSV(newsitems, filename): 

51

52

    # specifying the fields for csv file 

53

    fields = ['title', 'pubDate', 'description', 'link'] 

54

55

    # writing to csv file 

56

    with open(filename, 'w') as csvfile: 

57

58

        # creating a csv dict writer object 

59

        writer = csv.DictWriter(csvfile, fieldnames = fields) 

60

61

        # writing headers (field names) 

62

        writer.writeheader() 

63

64

        # writing data rows 

65

        writer.writerows(newsitems) 

66

67

68

def main(): 

69

    # load rss from web to update existing xml file 

70

    loadRSS() 

71

72

    # parse xml file 

73

    newsitems = parseXML('mierdekelhufeed.xml') 

74

#    print(newsitems)

75

76

    people = xml.etree.ElementTree.parse(r"mierdekelhufeed.xml")

77

    args_list = ([t.text for t in people.iter(tag)] for tag in ['title', 'pubDate', 'description'])

78

79

80

## paraméter tuples generálása

81

    sqltuples = list(zip(*args_list))

82

    print('----------------------------')

83

    print(sqltuples[2][0])

84

    print('----------------------------')

85

86

    # store news items in a csv file 

87

  #  savetoCSV(newsitems, 'topnews.csv') 

88

89

90

if 1==1 : ## ---__name__ == "__main__": 

91

92

    # calling main function 

93

    main() 

----------------------------
Adatmodellezés alapjai PowerDesignerrel
----------------------------