DataBase

DB-Python-예제

IT1004 2023. 11. 11. 09:59

예제1 )

 

import pymysql
conn = pymysql.connect(host='127.0.0.1', user='root', password='1234', db='employees', charset='utf8')
cur=conn.cursor()
cur.execute("select * from  titles where emp_no = 10160 ")

print("emp_no      title                  from_date            to_date")
print("===============================================================")

while(True):
    row=cur.fetchone()
    # print(type(row))
    if row == None :
        break
    data1 = row[0]
    data2 = row[1]
    data3 = row[2]
    data4 = row[3]

    print("%5s   %30s   %12s   %12s"  %(data1, data2, data3, data4))

conn.close()

 

import pymysql
conn = pymysql.connect(host='127.0.0.1', user='root', password='1234', db='employees', charset='utf8')
cur=conn.cursor()
cur.execute("select * from  titles where emp_no = 10160 ")

print("emp_no      title                  from_date            to_date")
print("===============================================================")

while(True):
    row=cur.fetchone()
    # print(type(row))
    if row == None :
        break
    data1 = row[0]
    data2 = row[1]
    data3 = row[2]
    data4 = row[3]

    print("%5s   %30s   %12s   %12s"  %(data1, data2, data3, data4))

conn.close()

 

예제2

import pymysql

# book_id=input('bookid ? :' )
# book_publisher=input('bookpulisher ? :')


conn = pymysql.connect(host='localhost', port=3306,
                       user='root', passwd='1234', charset='utf8')

# cursor = conn.cursor()
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 첫번째 방법
sql = 'select * from madang.book '
cursor.execute(sql)
print(sql)
# 두번째 방법
# sql = f'select * from madang.book where bookname = {book_id} and publisher = {book_publisher}'
# print(sql)
# cursor.execute(sql)


rows = cursor.fetchall()
print(rows)

for i in rows:
    print(i)
    print(i['bookid'], i['bookname'], i['publisher'], i['price'])

# conn.commit()
conn.close()

 

 

import pymysql

# book_id=input('bookid ? :' )
# book_publisher=input('bookpulisher ? :')


conn = pymysql.connect(host='localhost', port=3306,
                       user='root', passwd='1234', charset='utf8')

# cursor = conn.cursor()
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 첫번째 방법
sql = 'select * from madang.book '
cursor.execute(sql)
print(sql)
# 두번째 방법
# sql = f'select * from madang.book where bookname = {book_id} and publisher = {book_publisher}'
# print(sql)
# cursor.execute(sql)


rows = cursor.fetchall()
print(rows)

for i in rows:
    print(i)
    print(i['bookid'], i['bookname'], i['publisher'], i['price'])

# conn.commit()
conn.close()

예제3

 

from tkinter import ttk

import tkinter as tk

import pymysql

#import sqlite3

def View():

    conn = pymysql.connect(host='127.0.0.1', user='root', password='1234', db='employees', charset='utf8')
    cur1 = conn.cursor()
    #cur1.execute("select * from titles where emp_no = 10160")
    cur1.execute("select emp_no, first_name, gender, hire_date from employees where hire_date = '1994-02-26'")

    rows = cur1.fetchall()
    print("rows==: " ,rows)


    for row in rows:

        print("wwww:",row)

        tree.insert("", tk.END, values=row)

    conn.close()




root = tk.Tk()

tree = ttk.Treeview(root, column=("c1", "c2", "c3", "c4"), show='headings')


tree.column("#1", anchor=tk.CENTER)

tree.heading("#1", text="ID")

tree.column("#2", anchor=tk.CENTER)

tree.heading("#2", text="이름")

tree.column("#3", anchor=tk.CENTER)

tree.heading("#3", text="성별")

tree.column("#4", anchor=tk.CENTER)

tree.heading("#4", text="고용일")


tree.pack()

button1 = tk.Button(text="Display data", command=View)

button1.pack(pady=10)

root.mainloop()

 

from tkinter import ttk

import tkinter as tk

import pymysql

#import sqlite3

def View():

    conn = pymysql.connect(host='127.0.0.1', user='root', password='1234', db='employees', charset='utf8')
    cur1 = conn.cursor()
    #cur1.execute("select * from titles where emp_no = 10160")
    cur1.execute("select emp_no, first_name, gender, hire_date from employees where hire_date = '1994-02-26'")

    rows = cur1.fetchall()
    print("rows==: " ,rows)


    for row in rows:

        print("wwww:",row)

        tree.insert("", tk.END, values=row)

    conn.close()




root = tk.Tk()

tree = ttk.Treeview(root, column=("c1", "c2", "c3", "c4"), show='headings')


tree.column("#1", anchor=tk.CENTER)

tree.heading("#1", text="ID")

tree.column("#2", anchor=tk.CENTER)

tree.heading("#2", text="이름")

tree.column("#3", anchor=tk.CENTER)

tree.heading("#3", text="성별")

tree.column("#4", anchor=tk.CENTER)

tree.heading("#4", text="고용일")


tree.pack()

button1 = tk.Button(text="Display data", command=View)

button1.pack(pady=10)

root.mainloop()

예제4

import pymysql
from tkinter import *
from tkinter import messagebox

def insertData():
    con, cur = None, None
    data1, data2, data3, data4 = "", "", "", ""
    sql = ""

    conn = pymysql.connect(host='127.0.0.1', user='root', password= '1234', db='employees', charset='utf8' )
    cur = conn.cursor()
    data1=edt1.get()
    data2=edt2.get()
    data3=edt3.get()
    data4=edt4.get()

    try:
        sql="insert into employees values(" + data1 + ",'" +data2 + "','" +data3 + "','" + data4 +"')"
        print(f'1:{sql}')

        # sql=f"insert into employees(emp_no, hire_date,first_name,gender,birth_date,last_name) values({data1},'{data2}','{data3}','{data4}','0000-00-00','')"
        # print(f'2:{sql}')
        cur.execute(sql)
    except :
        messagebox.showerror('오류', '데이터 입력 오류가 발생함')
    else :
        messagebox.showinfo('성공', '데이터 입력 성공')
    conn.commit()
    conn.close()

def selectData():

    strData1, strData2, strData3, strData4 = [], [], [], []
    conn = pymysql.connect(host='127.0.0.1', user='root', password= '1234', db='employees', charset='utf8' )
    cur=conn.cursor()
    cur.execute("select emp_no, first_name, gender, hire_date from employees where hire_date = '1994-02-26'")

    strData1.append("ID")
    strData2.append("이름")
    strData3.append("성별")
    strData4.append("고용일")

    strData1.append("-------------")
    strData2.append("-------------")
    strData3.append("-------------")
    strData4.append("-------------")

    while (True):
        row= cur.fetchone()
        print(f'row:{row}')
        if row== None :
            break;
        strData1.append(row[0])
        strData2.append(row[1])
        strData3.append(row[2])
        strData4.append(row[3])

    listData1.delete(0,listData1.size() - 1)
    listData1.delete(0,listData2.size() - 1)
    listData1.delete(0,listData3.size() - 1)
    listData1.delete(0,listData4.size() - 1)

    for item1, item2, item3, item4 in zip(strData1, strData2, strData3, strData4 ) :
        listData1.insert(END, item1)
        listData2.insert(END, item2)
        listData3.insert(END, item3)
        listData4.insert(END, item4)


    conn.close()

##메인 코드부
window = Tk()
window.geometry("600x300")
window.title("GUI 데이터 입력")

edtFrame = Frame(window)
edtFrame.pack()
listFrame=Frame(window)
listFrame.pack(side = BOTTOM, fill = BOTH, expand=1)

edt1=Entry(edtFrame, width = 10)
edt1.pack(side=LEFT, padx=10,pady=10)
edt2=Entry(edtFrame, width = 10)
edt2.pack(side=LEFT, padx=10,pady=10)
edt3=Entry(edtFrame, width = 10)
edt3.pack(side=LEFT, padx=10,pady=10)
edt4=Entry(edtFrame, width = 10)
edt4.pack(side=LEFT, padx=10,pady=10)

btnInsert=Button(edtFrame, text="입력", command= insertData)
btnInsert.pack(side=LEFT,padx=10,pady=10)

btnSelect=Button(edtFrame, text="조회", command= selectData)
btnSelect.pack(side=LEFT,padx=10,pady=10)

listData1 = Listbox(listFrame,bg='yellow')
listData1.pack(side=LEFT,fill=BOTH, expand=1)
listData2 = Listbox(listFrame,bg='yellow')
listData2.pack(side=LEFT,fill=BOTH, expand=1)
listData3 = Listbox(listFrame,bg='yellow')
listData3.pack(side=LEFT,fill=BOTH, expand=1)
listData4= Listbox(listFrame,bg='yellow')
listData4.pack(side=LEFT,fill=BOTH, expand=1)

print("""""""""""""")
window.mainloop()

 

 

import pymysql
from tkinter import *
from tkinter import messagebox

def insertData():
    con, cur = None, None
    data1, data2, data3, data4 = "", "", "", ""
    sql = ""

    conn = pymysql.connect(host='127.0.0.1', user='root', password= '1234', db='employees', charset='utf8' )
    cur = conn.cursor()
    data1=edt1.get()
    data2=edt2.get()
    data3=edt3.get()
    data4=edt4.get()

    try:
        sql="insert into employees values(" + data1 + ",'" +data2 + "','" +data3 + "','" + data4 +"')"
        print(f'1:{sql}')

        # sql=f"insert into employees(emp_no, hire_date,first_name,gender,birth_date,last_name) values({data1},'{data2}','{data3}','{data4}','0000-00-00','')"
        # print(f'2:{sql}')
        cur.execute(sql)
    except :
        messagebox.showerror('오류', '데이터 입력 오류가 발생함')
    else :
        messagebox.showinfo('성공', '데이터 입력 성공')
    conn.commit()
    conn.close()

def selectData():

    strData1, strData2, strData3, strData4 = [], [], [], []
    conn = pymysql.connect(host='127.0.0.1', user='root', password= '1234', db='employees', charset='utf8' )
    cur=conn.cursor()
    cur.execute("select emp_no, first_name, gender, hire_date from employees where hire_date = '1994-02-26'")

    strData1.append("ID")
    strData2.append("이름")
    strData3.append("성별")
    strData4.append("고용일")

    strData1.append("-------------")
    strData2.append("-------------")
    strData3.append("-------------")
    strData4.append("-------------")

    while (True):
        row= cur.fetchone()
        print(f'row:{row}')
        if row== None :
            break;
        strData1.append(row[0])
        strData2.append(row[1])
        strData3.append(row[2])
        strData4.append(row[3])

    listData1.delete(0,listData1.size() - 1)
    listData1.delete(0,listData2.size() - 1)
    listData1.delete(0,listData3.size() - 1)
    listData1.delete(0,listData4.size() - 1)

    for item1, item2, item3, item4 in zip(strData1, strData2, strData3, strData4 ) :
        listData1.insert(END, item1)
        listData2.insert(END, item2)
        listData3.insert(END, item3)
        listData4.insert(END, item4)


    conn.close()

##메인 코드부
window = Tk()
window.geometry("600x300")
window.title("GUI 데이터 입력")

edtFrame = Frame(window)
edtFrame.pack()
listFrame=Frame(window)
listFrame.pack(side = BOTTOM, fill = BOTH, expand=1)

edt1=Entry(edtFrame, width = 10)
edt1.pack(side=LEFT, padx=10,pady=10)
edt2=Entry(edtFrame, width = 10)
edt2.pack(side=LEFT, padx=10,pady=10)
edt3=Entry(edtFrame, width = 10)
edt3.pack(side=LEFT, padx=10,pady=10)
edt4=Entry(edtFrame, width = 10)
edt4.pack(side=LEFT, padx=10,pady=10)

btnInsert=Button(edtFrame, text="입력", command= insertData)
btnInsert.pack(side=LEFT,padx=10,pady=10)

btnSelect=Button(edtFrame, text="조회", command= selectData)
btnSelect.pack(side=LEFT,padx=10,pady=10)

listData1 = Listbox(listFrame,bg='yellow')
listData1.pack(side=LEFT,fill=BOTH, expand=1)
listData2 = Listbox(listFrame,bg='yellow')
listData2.pack(side=LEFT,fill=BOTH, expand=1)
listData3 = Listbox(listFrame,bg='yellow')
listData3.pack(side=LEFT,fill=BOTH, expand=1)
listData4= Listbox(listFrame,bg='yellow')
listData4.pack(side=LEFT,fill=BOTH, expand=1)

print("""""""""""""")
window.mainloop()

'DataBase' 카테고리의 다른 글

PostgreSQL DBA  (0) 2021.03.01
파이썬과 MySQL 연동  (0) 2021.02.10
데이타베이스(정보처리산업기사)출제기준(필기)2021년까지  (0) 2021.02.08
1-MySQL 접속 및 설정  (1) 2021.02.05
0-MySQL 설치  (0) 2021.02.05

'DataBase'의 다른글

  • 현재글DB-Python-예제

관련글

티스토리툴바