파이썬에서 sqlite3 예제 코드 CREATE, INSERT, SELECT, DELETE, UPDATE

간단한 sqlite3 예제 코드다.

 

CREATE

conn = sqlite3.connect('student.sqlite')
cursor = conn.cursor()
cursor.execute("CREATE TABLE student (id INTEGER primary key autoincrement, name char(32), class char(4))")
cursor.close()
conn.close()

 

INSERT

conn = sqlite3.connect('student.sqlite')
cursor = conn.cursor()
cursor.execute("INSERT INTO student (name, class) VALUES (?, ?)", ['홍길동', '1-1'])
id = cursor.lastrowid
print(id)
conn.commit()
cursor.execute("INSERT INTO student (name, class) VALUES (?, ?)", ['임꺽정', '2-1'])
id = cursor.lastrowid
print(id)
conn.commit()
cursor.close()
conn.close()

1
2

 

SELECT

conn = sqlite3.connect('student.sqlite')
cursor = conn.cursor()
cursor.execute("SELECT * FROM student")
rows = cursor.fetchall()
for row in rows:
    print(row)
    print(row[0])
    print(row[1])
cursor.close()
conn.close()

(1, '홍길동', '1-1')
1
홍길동
(2, '임꺽정', '2-1')
2
임꺽정

DELETE

conn = sqlite3.connect('student.sqlite')
cursor = conn.cursor()
cursor.execute("DELETE FROM student WHERE name = ?", ['홍길동'])
conn.commit()
cursor.execute("SELECT * FROM student")
rows = cursor.fetchall()
for row in rows:
    print(row)
cursor.close()
conn.close()

(2, '임꺽정', '2-1')

UPDATE

conn = sqlite3.connect('student.sqlite')
cursor = conn.cursor()
cursor.execute("UPDATE student SET class = ? WHERE name=?", ['3-1', '임꺽정'])
conn.commit()
cursor.execute("SELECT * FROM student")
rows = cursor.fetchall()
for row in rows:
    print(row)
cursor.close()
conn.close()

(2, '임꺽정', '3-1')

 

sqlite-example.ipynb

0.00MB

 

참고할 사이트

sqlite3 — SQLite 데이터베이스용 DB-API 2.0 인터페이스