###### tags: `python`,`資料庫`
# Python 資料庫處理
## 資料庫連線方法

# Oracle 範例: 20200730
import cx_Oracle
def connectDB(dbname='ORDB1'):	
    if dbname == 'ORDB1':
        username = 'user1'
        password = 'pwd1'
        dsn = '192.168.0.20/ORDB1Name'
        # port = 1512
        encoding = 'UTF-8'
    if dbname == 'ORDB2':
        username = 'user2'
        password = 'pwd2'
        dsn = '172.1.1.20/ORDB2Name'
        encoding = 'Big5'	
    db = cx_Oracle.connect(username,password,dsn,encoding=encoding)
    return db 	

#連接 MSSQL範例(各種方式) 20200730
import pyodbc  #需先於Windows設定ODBC
def connectMSSQL_DB(dbname='Default'):
    if dbname == 'MSDB1':        #ODBC設定的名稱
        server = '192.168.0.20'
        username = 'user_sa'
        password = 'pwd'
        database = 'MSDB1'
        connStr = (r'DRIVER={ODBC Driver 17 for SQL Server};' +
                r"Integrated Security=True;" +
                r'SERVER=' + server +
                r';UID=' + username +
                r';PWD=' + password +
                r';DSN=MSSQL-PYTHON' +
                r';DATABASE=' + database + ';'
                )
        db = pyodbc.connect(connStr)
    if dbname == 'MSDB2':
        username = 'user_sa'
        password = 'pwd'
        dsn = '192.168.0.30/DSN=MSDB2'
        encoding = 'UTF-8'
    if dbname == 'Default':
        db = pyodbc.connect('DSN=MSDB0;SERVER=192.168.0.10;UID=user_sa;PWD=pwd')
    else:
        db = pyodbc.connect(username,password,dsn,encoding=encoding)
    return db 

## 範本參考(下列程式尚未全部測試過)

# 將用Python 對DB 進行一些操作，包括，建立一張表，並插入一些資料，在修改其中的部分資料。
#coding=utf-8
import cx_Oracle
import sys
import urllib
import os
 def connectDB(dbname='dave'):
	if dbname == 'dave':
		connstr = 'system/Oracle@192.168.2.42:1521/dave'
	db = cx_Oracle.connect(connstr)
	return db
 def sqlSelect(sql,db):
        #include:select
        cr=db.cursor()
        cr.execute(sql)
        rs=cr.fetchall()
        cr.close()
        return rs
 def sqlDML(sql,db):
        #include: insert,update,delete
        cr=db.cursor()
        cr.execute(sql)
        cr.close()
        db.commit()
 def sqlDML2(sql,params,db):
        # execute dml with parameters
        cr=db.cursor()
        cr.execute(sql,params)
        cr.close()
        db.commit()
 def sqlDDL(sql,db):
        #include: create
        cr=db.cursor()
        cr.execute(sql)
        cr.close()
  
if __name__=='__main__':
	print "This is a test python program,write by tianlesoftware!\n"
	os.environ['NLS_LANG']='SIMPLIFIED CHINESE_CHINA.UTF8'
	 #connect to database:
	db=connectDB()
	 #create a table:
	sql='create table dave(id number,name varchar2(20),phone number)'
	sqlDDL(sql,db)
	 #insert data to table dave:
	sql='insert into dave values(1,\'tianlesoftware\',13888888888)'
	sqlDML(sql,db)
	 dt=[{'id':2,'name':'dave','phone':138888888888},
		{'id':3,'name':'Oracle','phone':13888888888},
		{'id':4,'name':'anqing','phone':13888888888}]
	 sql='insert into dave values(:id,:name,:phone)'
	 for x in dt:
			sqlDML2(sql,x,db)

	#select the result:
	print "this is the first time select the data from dave"
	sql='select * from dave'
	rs=sqlSelect(sql,db)
	for x in rs:
			print x
	 #update data where id=1,change the name to anhui
	sql='update dave set name=\'anhui\' where id=1'
	sqlDML(sql,db)
	 #select again:
	print "\n change the nanme to anhui where id equal 1,and select the result"
	sql='select * from dave'
	rs=sqlSelect(sql,db)
	for x in rs:
			print x
	 #delete data where id=3
	sql='delete from dave where id=3'
	sqlDML(sql,db)
	 #select again:
	print "\n delete the data where id equal 3 and select the result"
	sql='select * from dave'
	rs=sqlSelect(sql,db)
	for x in rs:
			print x
	 db.close()
# S20200507 By YTC尚未測試

### 插入一筆資料到Table

cursorObj.execute("INSERT INTO employees VALUES(1, 'John', 700, 'HR', 'Manager', '2017-01-04')")
con.commit()
#VALUES裡面的值，可用?代替，表示缺失的空值

### 更新資料

import sqlite3
con = sqlite3.connect('mydatabase.db')
def sql_update(con):
    cursorObj = con.cursor()
    cursorObj.execute('UPDATE employees SET name = "Rogers" where id = 2')
    con.commit()
sql_update(con)

### 插入大量資料

import sqlite3
con = sqlite3.connect('mydatabase.db')
cursorObj = con.cursor()
cursorObj.execute('create table if not exists projects(id integer, name text)')
data = [(1, "Ridesharing"), (2, "Water Purifying"), (3, "Forensics"), (4, "Botany")]
cursorObj.executemany("INSERT INTO projects VALUES(?, ?)", data)
con.commit()
# 測試過 可行 看似乎還可以改進。

### 資料庫時間之處理

import sqlite3
import datetime
con = sqlite3.connect('mydatabase.db')
cursorObj = con.cursor()
cursorObj.execute('create table if not exists assignments(id integer, name text, date date)')
data = [(1, "Ridesharing", datetime.date(2017, 1, 2)), (2, "Water Purifying", datetime.date(2018, 3, 4))]
cursorObj.executemany("INSERT INTO assignments VALUES(?, ?, ?)", data)
con.commit()

參考:[Python資料庫學習筆記(六)：SQLite3](https://medium.com/@yanweiliu/python%E8%B3%87%E6%96%99%E5%BA%AB%E5%AD%B8%E7%BF%92%E7%AD%86%E8%A8%98-%E5%85%AD-sqlite3-818ca8e7eff5)

S20200721 By YTC
M20200730