[파이썬(Python)] SQL

1. 입력

-입력 : insert into [table]([field]) values('입력할 값')

insert into contract(name, phone) values('안중근', '010-2222-3333')

========================================================================================

2. 조회

-전체값을 조회할 때: select [field]  from [table]

select name, phone from contract

-원하는 값만 조회할 때: select [field]  from [table] where [filed] = '원하는 값'

select name, phone from contract where name = '안중근'

-특정 부분이 들어가는 값을 조회할 때: select [field]  from [table] where [특정 field] like '특정 부분%'

select name, phone from contract where name like '이%'

** % : 문자가 길거나 짧거나 / 있거나 없거나

- 나이가 40이상인 사람을 조회

select name, age from contract where age >= 40
select * from contract where age >= 40

** * : 모든 것의 의미

========================================================================================

3. 수정(변경)

-전체값을 수정할 때: update [table] set [field] = '입력 값'

update contract set name = '윤봉길'

-원하는 값만 수정할 때: update [table] set [field] = '입력 값' where [field] = '기존 입력값'

update contract set name = '홍길동' where phone = '010-2222-3333'

========================================================================================

4. 삭제

-전체값을 삭제할 때: delete from [table]

-원하는 값만 삭제할 때: delete from [table] where [field] = '입력값'

delete from contract where name = '홍길동'

========================================================================================

5. 정렬

-- 나이 순서대로 오름차순 정렬

select * from contract order by age asc

-- 나이 순서대로 내림차순 정렬

select * from contract order by age desc

========================================================================================

파이썬 - sqlite 연결하기

import sqlite3

conn= sqlite3.connect("d:/contract.db") # sqlite 파일 연결

cur = conn.cursor() #sql 구문을 실행할 수 있는 변수

cur.execute("select name, phone, age from contract") #sql 구문 실행

rows = cur.fetchall() #테이블의 행을 저장하는 변수

print(rows)

conn.close()

import sqlite3

conn= sqlite3.connect("d:/contract.db") 

cur = conn.cursor() 

cur.execute("select name, phone, age from contract") 

rows = cur.fetchall() 

for row in rows :
    print(row)

conn.close()

========================================================================================

파이썬으로 sql 특정 값 추출

import sqlite3

conn= sqlite3.connect("d:/contract.db") 

cur = conn.cursor() 

sql = "select name, phone, age from contract where name = :Name"

cur.execute( sql, {'Name':'이순신'} )

rows = cur.fetchall()

for row in rows :
    print(row)


conn.close()

========================================================================================

파이썬으로 sql 값 추가

import sqlite3

conn= sqlite3.connect("d:/contract.db") 

cur = conn.cursor() 

sql = "insert into contract (name, phone, age) values (:Name, :Phone, :Age)"

cur.execute(sql, {'Name' : '임꺽정', 'Phone' : '010-9999-0000', 'Age' : 42})

conn.commit() #sqlite - 변경사항 저장하기(delete, update, insert)

conn.close()