读书笔记--python数据可视化--005_从数据库导入数据

#-*- coding: UTF-8 -*-
'''
 #################################################  
 # Author : 余欢
 # Date : Dec 28, 2015    11:16:49 AM
 #company : 南京师范大学--大数据实验室
 # description :从数据库导入数据 
 ################################################# 
'''
from __builtin__ import str
from _sqlite3 import Row

'''
1  连接数据库引擎(或者SQLite文件)
2  在选择的表上执行查询操作
3  读取从数据库引擎返回的结果
'''
import sqlite3
import sys

#第一步，将SQL文件导入到SQLite数据库中   
def ImportDataIntoDB(script_path):
    #创建一个数据库
    db = "world"
    
    try:
        con = sqlite3.connect(db)
        with con:
            cur = con.cursor()
            with open(script_path, "rb") as f:
                cur.executescript(f.read())
    except sqlite3.Error as e:
        print "错误发生：%s" % e
        
def ReadDataFromDB(db):
    try:
        con = sqlite3.connect(db)
        with con:
            cur = con.cursor()
            query = "select ID, Name, Population From City Order by Population desc limit 20"
            con.text_factory = str
            cur.execute(query)
            
            resultset = cur.fetchall()
            
            #提取所有的名称
            col_names = [cn[0] for cn in cur.description]
            print "%10s %30s %10s" % tuple(col_names)
            print "="*(10 + 1 + 30 + 1 + 10)
            
            for row in resultset:
                print "%10s %30s %10s" % row
    except sqlite3.Error as e:
        print "[Error]:", e

if __name__ == "__main__":
    ImportDataIntoDB("/root/Desktop/data-visualization/data_visualization_Code/3367OS_02_Code/world.sql")
    ReadDataFromDB("world")