# import sqlite3
# conn = sqlite3.connect(":memory:")
# conn.close()
#
# import sqlite3
# conn = sqlite3.connect("First.db")
# conn.close
#
# cur.execute('Create table T_fish(date text, name text, nums int, price real, Explain text)')
# cur.execute("insert into T_fish Values('2018-3-2', '黑色', '10,29,3', 'Tom')")
# conn.commit()
# conn.close()
#--------------------创建数据库表------------------------------------------
'''
import pymysql
import sys

try:
    conn = pymysql.connect(host='localhost', user='root', passwd='mysql123',db= 'test', port = 3306, charset='utf8')
except:
    print("打开数据库连接出错，请检查！")
    conn.close()
    sys.exit()

cur=conn.cursor()
sql='"create table if not exist T_fish" \
    "(date1 char(12) primary key not null," \
    "name char(10) not null," \
    "nums int not null," \
    "price decimal(10,2) not null," \
    "sExplain varchar(200));"'
try:
    cur.execute(sql)
    conn.commit()
    print("T_fish 表可以使用")
except:
    print("T_fish表建立过程出错！")
conn.close()
'''
#-------------------------------插入，修改，删除，查找案例-----------------
import cx_Oracle

'''
import pymysql
import sys

try:
    conn = pymysql.connect(host='localhost',user='root',passwd='mysql123',db='test',port=3306,charset='utf8')
except:
    print("打开数据库报错，请检查！")
    conn.close()
    sys.exit()

cur=conn.cursor()
insertSQL = "insert into T_fish values('2018-3-4','甲鱼','10,27,4','Tom')"
insertSQL1 = "insert into T_fish values('2018-3-3','甲鱼','10,28,8','Tom')"
try:
    cur.execute(insertSQL)
    cur.execute(insertSQL1)
    conn.commit()
    print("两条记录都插入成功！")
except:
    print("两条记录插入失败！")
    conn.close()
    sys.exit()
updateSQL = "update T_fish set nums = 12 where date1 = '2018-5-3'"
try:
    cur.execute(updateSQL)
    conn.commit()
    print("第一条记录插入成功")
except:
    print("第一条记录插入失败")
    conn.close()
    sys.exit()
deleteSQL = "DELETE FROM T_fish WHERE date1 = '2018-5-3'"
try:
    cur.execute(deleteSQL)
    conn.commit()
    print("第二条记录删除成功")
except:
    print("第二条记录删除失败")
    conn.close()
    sys.exit()
selectSQL = 'select * from T_fish'
cur.execute(selectSQL)
one_records= []
for row in cur.fetchall():
    one_records.append(row)
print(one_records)
'''
# -----------------把数据库表里的数据记录显示到图形用户界面上---------
'''
def turn_property(event):
    getSQLDate()
def getSQLDate():
    import pymysql
    import sys
    try:
        conn = pymysql.connect(host= 'localhost',user= 'root',passwd='mysql123',db='test',port= 3306,charset='utf8')
    except:
        print("打开数据库报错，请检查！")
        conn.close()
        sys.exit()
    cur=conn.connect()
    selectSQL= 'Selcet * from T_fish'
    cur.execute(selectSQL)
    for row in cur.fetchall():
        tree.insert("",0,test=row[0],values=(row[1],row[2],row[3],row[4]))
from tkinter import ttk
import tkinter as tk
root=tk.Tk()
tree=ttk.Treeview(root)
tree["columns"]=("name","nums","price","Explain")
tree.column("name",width=50)
tree.column("nums",width=50)
tree.column("price",width=50)
tree.column("Explain",width=50)
tree.heading("name",text="名称")
tree.heading("nums",text="数量")
tree.heading("price",text="单价（元）")
tree.heading("Explain",text="说明")
tree.pack(side="top")
bs=tk.Button(root,text="显示数据",width=10)
bs.bind("<Button-1>",turn_property)
bs.pack(side="top")
root.mainloop
'''
#---------------python连接oracle数据库------------------
'''
import cx_Oracle
tns = cx_Oracle.makedsn('127.0.0.1','1521','orcl')
db = cx_Oracle.connect('username','password',tns)
db.close()
'''
# -------连接oracle并查找T_fish表案例
'''
import cx_Oracle
dsn = cx_Oracle.makedsn('127.0.0.1','1521','orcl')
connection = cx_Oracle.connect('testname','ask124',dsn)
cursor = connection.cursor()
sql = "select * from T_fish"
cursor.execute(sql)
result = cursor.fetchall()
for row in result:
    print(row)
cursor.close()
connection.close()
'''
import pymysql
import sys
import tkinter.messagebox
from tkinter import *
import tkinter.ttk

lock = tkinter.ttk.Tk()
sDate = StringVar()     # 创建StringVar()实例（全局变量）
sname = StringVar()
snums = StringVar()
sprice = StringVar()
sExplain = StringVar()
def turn_save(event):
    # 往表里插入新记录
    try:
        conn = pymysql.connect(host='localhost',user='root',passwd='mysql123',db='test',port=3306,charset='utf8')
    except:
        print("打开数据库连接出错，请检查！")
        conn.close()
        sys.exit()
    cur = conn.cursor()
    selectSQL = "Insert into T_fish values("+sDate.get()+","+sname.get()+","+snums.get()+","+sprice.get()+","+sExplain.get()+")"
    cur.execute(selectSQL)
    conn.commit()
    cur.close()
    conn.close()
    tkinter.messagebox.showinfo('提示','记录保存成功！')
def turn_property(event):
    getSQLDate()
def getSQLDate():
    try:
        conn = pymysql.connect(host='localhost',user='root',passwd='mysql123',db='test',port=3306,charset='utf8')
    except:
        print("打开数据库连接出错，请检查！")
        conn.close()
        sys.exit()
    cur = conn.cursor()
    selectSQL = 'select * from T_fish'
    cur.execute(selectSQL)
    for row in cur.fetchall():
        tree.insert("",0,text = row[0],values = (row[1],row[2],row[3],row[4]))
    cur.close()
    conn.close()

import tkinter as tkr
from tkinter.constants import *
from tkinter import ttk
tree = ttk.Treeview(lock)
tree["columns"] = ("name","nums","price","Explain")
tree.column("name",width = 50)
tree.column("nums",width = 50)
tree.column("price",width = 50)
tree.column("Explain",width = 50)
tree.heading("name",text = "名称")
tree.heading("nums",text = "数量")
tree.heading("price",text = "单价（元）")
tree.heading("Explain",text = "说明")
tree.pack(side = "top")
bs = tkr.Button(lock,text = "显示数据",width = 10)
bs.bind("<Button-1>",turn_property)
bs.pack(side = "top")
top = tkinter.ttk.Frame(lock,relief = RAISED,bd = 1)
top.pack(side = "left")
top.date1 = tkinter.ttk.LabelEntry(top,label = "日期：",labelside = 'top',)
top.date1.pack(side = "left")
top.date1.entry['textvariable'] = sDate
top.name1 = tkinter.ttk.LabelEntry(top,label = "名称:",labelside = 'top',)
top.name1.pack(side = "left")
top.name1.entry['textvariable'] = sname
top.nums1 = tkinter.ttk.LabelEntry(top,label = "数量",labelside = 'top',)
top.nums1.pack(side = "left")
top.nums1.entry['textvariable'] = snums
top.price = tkinter.ttk.LabelEntry(top,label = "价格：",labelside = 'top',)
top.price.pack(side = "left")
top.price.entry['textvariable'] = sprice
top.Explain = tkinter.ttk.LabelEntry(top,label = "说明",labelside = 'top',)
top.Explain.pack(side = "left")
top.Explain.entry['textvariable'] = sExplain
Savebn = tkr.Button(top,text = "保存数据",width = 10)
Savebn.bind("<Button-1>",turn_save)
Savebn.pack(side = "left")
lock.mainloop()