13장. 파이썬에서 sqlite 활용

Posted on: 2019년 08월 05일 2019년 08월 05일
Categories: 강의자료, 파이썬

DB Browser for Sqlite 다운로드 : http://sqlitebrowser.org/
sqlite db 파일 : addr.zip

테이블에 레코드 추가 후 목록 출력 : 13_01_Sqlite.py

# -*- coding:utf-8 -*-
import sqlite3

con = sqlite3.connect("addr.db")
cur = con.cursor()

# Insert
sql = "insert into contact(name, phone, addr, post) values ('python', '010-9876-5432', '전주시 완산구', '54123');"
cur.execute(sql)
con.commit()

# Select
sql = "select * from contact;"
cur.execute(sql)
print("no    name   phone   addr   post")
print("--------------------------------")
while(True):
    row = cur.fetchone()
    if row == None:
        break;
    print(row[0], row[1], row[2], row[3], row[4])

con.close()

# -*- coding:utf-8 -*-

import sqlite3

con = sqlite3.connect("addr.db")

cur = con.cursor()

# Insert

sql = "insert into contact(name, phone, addr, post) values ('python', '010-9876-5432', '전주시 완산구', '54123');"

cur.execute(sql)

con.commit()

# Select

sql = "select * from contact;"

cur.execute(sql)

print("no name phone addr post")

print("--------------------------------")

while(True):

row = cur.fetchone()

if row == None:

break;

print(row[0], row[1], row[2], row[3], row[4])

con.close()

Sqlite 입력 부분1 : INSERT

import sqlite3

sql = "insert into contact(name, phone) values ('python', '010-9876-5432');"

con = sqlite3.connect("addr.db")
cur = con.cursor()
cur.execute(sql)
con.commit()
con.close()

import sqlite3

sql = "insert into contact(name, phone) values ('python', '010-9876-5432');"

con = sqlite3.connect("addr.db")

cur = con.cursor()

cur.execute(sql)

con.commit()

con.close()

Sqlite 입력 부분2 : INSERT

import sqlite3

name = input("이름 : ")
engname = input("영어이름 : ")
phone = input("전화번호 : ")
addr = input("주소 : ")
post = input("우편번호 : ")

sql = "insert into contact(name, engname, phone, addr, post) values ('%s', '%s', '%s', '%s', '%s');" % (name, engname, phone, addr, post)
con = sqlite3.connect("addr.db")

cur = con.cursor()
cur.execute(sql)
con.commit()
con.close()

import sqlite3

name = input("이름 : ")

engname = input("영어이름 : ")

phone = input("전화번호 : ")

addr = input("주소 : ")

post = input("우편번호 : ")

sql = "insert into contact(name, engname, phone, addr, post) values ('%s', '%s', '%s', '%s', '%s');" % (name, engname, phone, addr, post)

con = sqlite3.connect("addr.db")

cur = con.cursor()

cur.execute(sql)

con.commit()

con.close()

Sqlite 목록 출력 부분1 : SELECT

import sqlite3

sql = "select * from contact;"

con = sqlite3.connect("addr.db")
cur = con.cursor()
cur.execute(sql)
row = cur.fetchone()     #row = cur.fetchall()
if row != None:
  print(row)       #print(row[0], row[1], row[2], row[3], row[4], row[5])
con.close()

import sqlite3

sql = "select * from contact;"

con = sqlite3.connect("addr.db")

cur = con.cursor()

cur.execute(sql)

row = cur.fetchone() #row = cur.fetchall()

if row != None:

print(row) #print(row[0], row[1], row[2], row[3], row[4], row[5])

con.close()

Sqlite 목록 출력 부분2 : SELECT

import sqlite3

sql = "select * from contact;"

con = sqlite3.connect("addr.db")
cur = con.cursor()
cur.execute(sql)
print("no    name   engname phone   addr   post")
print("----------------------------------------")
while(True):
  row = cur.fetchone()
  if row == None:
      break;
  print(row[0], row[1], row[2], row[3], row[4], row[5])
con.close()

import sqlite3

sql = "select * from contact;"

con = sqlite3.connect("addr.db")

cur = con.cursor()

cur.execute(sql)

print("no name engname phone addr post")

print("----------------------------------------")

while(True):

row = cur.fetchone()

if row == None:

break;

print(row[0], row[1], row[2], row[3], row[4], row[5])

con.close()

Sqlite 목록 출력 부분3 : SELECT

import sqlite3

name = input('검색할 이름:')
sql = "select * from contact WHERE name='%s';" % (name)

con = sqlite3.connect("addr.db")
cur = con.cursor()
cur.execute(sql)
print("no    name   engname phone   addr   post")
print("----------------------------------------")
while(True):
  row = cur.fetchone()
  if row == None:
      break;
  print(row[0], row[1], row[2], row[3], row[4], row[5])
con.close()

import sqlite3

name = input('검색할 이름:')

sql = "select * from contact WHERE name='%s';" % (name)

con = sqlite3.connect("addr.db")

cur = con.cursor()

cur.execute(sql)

print("no name engname phone addr post")

print("----------------------------------------")

while(True):

row = cur.fetchone()

if row == None:

break;

print(row[0], row[1], row[2], row[3], row[4], row[5])

con.close()

Sqlite 통합

import sqlite3

choice = int(input('''[연락처 관리 프로그램]
   1. 연락처 입력
   2. 전체 목록 출력
   3. 이름으로 검색
   4. 전화번호로 검색
   99. 종료
선택 : '''))

if choice == 1: # Insert
  name = input("이름 : ")
  engname = input("영어이름 : ")
  phone = input("전화번호 : ")
  addr = input("주소 : ")
  post = input("우편번호 : ")

  sql = "insert into contact(name, engname, phone, addr, post) values ('%s', '%s', '%s', '%s', '%s');" % (name, engname, phone, addr, post)
  con = sqlite3.connect("addr.db")

  cur = con.cursor()
  cur.execute(sql)
  con.commit()
  con.close()
elif choice == 2: # Select
  sql = "select * from contact;"

  con = sqlite3.connect("addr.db")
  cur = con.cursor()
  cur.execute(sql)
  print("no    name   engname phone   addr   post")
  print("----------------------------------------")
  while(True):
    row = cur.fetchone()
    if row == None:
        break;
    print(row[0], row[1], row[2], row[3], row[4], row[5])
  con.close()
elif choice == 3: # Select
  name = input('검색할 이름:')
  sql = "select * from contact WHERE name='%s';" % (name)

  con = sqlite3.connect("addr.db")
  cur = con.cursor()
  cur.execute(sql)
  print("no    name   engname phone   addr   post")
  print("----------------------------------------")
  while(True):
    row = cur.fetchone()
    if row == None:
        break;
    print(row[0], row[1], row[2], row[3], row[4], row[5])
  con.close()

import sqlite3

choice = int(input('''[연락처 관리 프로그램]

1. 연락처 입력

2. 전체 목록 출력

3. 이름으로 검색

4. 전화번호로 검색

99. 종료

선택 : '''))

if choice == 1: # Insert

name = input("이름 : ")

engname = input("영어이름 : ")

phone = input("전화번호 : ")

addr = input("주소 : ")

post = input("우편번호 : ")

sql = "insert into contact(name, engname, phone, addr, post) values ('%s', '%s', '%s', '%s', '%s');" % (name, engname, phone, addr, post)

con = sqlite3.connect("addr.db")

cur = con.cursor()

cur.execute(sql)

con.commit()

con.close()

elif choice == 2: # Select

sql = "select * from contact;"

con = sqlite3.connect("addr.db")

cur = con.cursor()

cur.execute(sql)

print("no name engname phone addr post")

print("----------------------------------------")

while(True):

row = cur.fetchone()

if row == None:

break;

print(row[0], row[1], row[2], row[3], row[4], row[5])

con.close()

elif choice == 3: # Select

name = input('검색할 이름:')

sql = "select * from contact WHERE name='%s';" % (name)

con = sqlite3.connect("addr.db")

cur = con.cursor()

cur.execute(sql)

print("no name engname phone addr post")

print("----------------------------------------")

while(True):

row = cur.fetchone()

if row == None:

break;

print(row[0], row[1], row[2], row[3], row[4], row[5])

con.close()

whan0623

516