練習 XML to SQL

訂單的XML

<orders>

  <order id="1">

    <product>Shirt</product>

    <quantity>2</quantity>

    <price>20</price>

  </order>

  <order id="2">

    <product>Pants</product>

    <quantity>1</quantity>

    <price>30</price>

  </order>

</orders>

'''

DDL(資料定義語言)

用來處理建立、刪除、限制、以及資料表、資料庫與用戶權限。

基本 SQL DDL命令

建立資料庫

"CREATE DATABASE dbname"   範例: "CRATE DATABASE d"

建立資料表

"CREATE TABLE tbname(coldefs)" 範例:"CREATE TABLE t(id INT, name STR, age INT, prices FLOAT)"

選擇目前的資料庫

"USE dbname"    範例: "use d"

刪除資料庫與他的資料表

"DROP DATABASE dbname"     範例:"DROP DATABASE d"

刪除資料表

"DROP TABLE tbname"    範例: "DROP TABLE d"

移除資料表的每一列

"TRUNCATE TABLE tbname"    範例:"TRUNCATE TABLE t"

--------------------------------

DML(資料處理語言)

用來處理資料插入、選取、更新和刪除

基本 SQL DML命令

加入一列

"INSERT INTO tbname VALUES(...)"    範例: "INSERT INTO t VALUES(7,Jackey,2,150.0)"

選擇每一列與每一欄位

"SELECT * FROM tbname "    範例:"SELECT * FROM t"

選擇每一列,與一些欄位

"SELECT cols FROM tbname"    範例:"SELECT id, age FROM t"

選擇一些列,一些欄

"SELECT cols FROM tbname WHERE condition" 範例: "SELECT id, age, price WHERE id <100 AND age<=2 AND price <=100.0"

改變一些欄中的一些列

"UPDATE tbname SET col=value WHERE condition"    範例:"UPDATE t SET age+=1 WHERE id=*"  將所有寵物都+1歲

刪除一些列

"DELETE FROM tbname WHERE condition"    範例: "DELETE FROM t WHERE age>5 OR id <=15"    將超過5歲的寵物送走,或是最早加入的15支

'''

'''

練習題目:

將上述XML資料轉換成SQL格式,生成INSERT語句。

'''

import sqlite3

import xml.etree.ElementTree as et

import os

conn=sqlite3.connect('orders.db') #連接orders資料庫,如果不存在就建立 orders資料庫

curs=conn.cursor()#建立指標物件操作和查詢資料

curs.execute('SELECT name FROM sqlite_master WHERE type="table" AND name="orders"')  #檢查資料庫oders是否存在資料表

result=curs.fetchone() #取出回傳的結果,如果不存在會返回None

if result is None: #如果是None 就創建資料表

    curs.execute('''CREATE TABLE orders

    (id STR(100) PRIMARY KEY,

    product STR,

    quantitfy INT,

    price INT)

    ''')

    #建立orders.db資料表,指定主欄位為id 接受(訂單編號)字串資料,

    # product 接受(產品名稱)字串資料,

    # quantitfy 接受(產品數量)整數資料,

    # price 接受(產品價格)整數資料

ins='INSERT INTO orders (id,product,quantitfy,price) VALUES(?,?,?,?)'

tree=et.ElementTree(file='orders.xml')

root=tree.getroot()

for order in root:

    id_num=order.get('id') #取得id的屬性值

    product_name=order.find('product').text  

    product_num=int(order.find('quantity').text)

    product_prices=int(order.find('price').text)

    curs.execute(ins,(id_num,product_name,product_num,product_prices))

conn.commit()  #將新的值插入到現有的資料表後, commit() 只屬於資料庫物件的conn的方法,  游標物件不存在commit()

#確認資料表資料

curs.execute('SELECT * FROM orders') #讀取資料表

rows=curs.fetchall()

print(rows)

curs.close() #關閉指標

conn.close() #關閉資料庫連線