728x90
반응형
import sqlite3
# DB 파일 조회(없으면 새로 생성)
conn = sqlite3.connect("D:/python/fastcamp_python/fundational/resource/database2.db") # 본인 DB 경로로
# ZJTJ QKDLSELD
c = conn.cursor()
# 데이터 조회(전체)
c.execute("SELECT * FROM users")
로우 1개 선택
# 커서 위치가 변경
# 1개 로우 선택
print('One -> \n',c.fetchone())
지정 로우 선택
# 지정 로우 선택
print('Thee -> \n',c.fetchmany(size=3))
모든 로우 선택
#
print('All -> \n',c.fetchall())
순회1
# 순회1
rows = c.fetchall()
for row in rows: # or for row in c.fetchall():
print('retrieve1 -> ', row)
순회2
# 순회2
for row in c.execute("SELECT * FROM users ORDER BY id desc"):
print('retriveve2 ->', row)
WHERE Retrieve1
# WHERE Retrieve1
param1 =(3,)
c.execute('SELECT * FROM users WHERE id=?',param1)
print('param1',c.fetchone())
print('param1',c.fetchall()) # 데이터 x
WHERE Retrieve2
# WHERE Retrieve2
param2 = 4
c.execute('SELECT * FROM users WHERE id="%s"'%param2) #%s, %f, %d
print('param1',c.fetchone())
print('param1',c.fetchall()) # 데이터 x
WHERE Retrieve3
# WHERE Retrieve3
c.execute('SELECT * FROM users WHERE id=:Id',{"Id":5})
print('param1',c.fetchone())
print('param1',c.fetchall()) # 데이터 x
WHERE Retrieve4
# WEHERE Retrieve4
param4 = (3,5)
c.execute('SELECT * FROM users WHERE id IN(?,?)',param4)
print('param4',c.fetchall())
WHERE Retrieve5
# WHERE Retrieve5
c.execute('SELECT * FROM users WHERE id IN("%d","%d")'%(3,4))
print('param5',c.fetchall())
WHERE Retrieve6
# WHERE Retrieve6
c.execute('SELECT * FROM users WHERE id=:id1 OR id=:id2',{'id1':2,'id2':5})
print('param6',c.fetchall())
Dump 출력
# Dump 출력
with conn:
with open('D:/python/fastcamp_python/fundational/resource/dump.sql','w') as f:
for line in conn.iterdump():
f.write('%s\n' %line)
print('Dump Print Complete')
dump.sql
BEGIN TRANSACTION;
CREATE TABLE users(id INTEGER PRIMARY KEY, username text, email text,phone text, website text, regdate text);
INSERT INTO "users" VALUES(1,'LEE','as_thtls@naver.com','010-1111-2222','Lee.com','2021-01-07 18:44:37');
INSERT INTO "users" VALUES(2,'Park','Park@naver.com','010-2222-1111','PARK.com','2021-01-07 18:44:37');
INSERT INTO "users" VALUES(3,'Kim','Kim@naver.com','010-3333-4444','Kim.com','2021-01-07 18:44:37');
INSERT INTO "users" VALUES(4,'Cho','Cho@naver.com','010-4444-3333','Cho.com','2021-01-07 18:44:37');
INSERT INTO "users" VALUES(5,'Na','Na@naver.com','010-5555-5555','Na.com','2021-01-07 18:44:37');
COMMIT;
728x90
반응형
최근댓글