SQL - SQL with Python

SQLite

 

 

✅ Python 환경에서 SQLite 사용하기

---

Placeholder

import sqlite3

connection = sqlite3.connect("users.db")

cursor = connection.cursor()


def init_table():
    cursor.execute(
        """
    CREATE TABLE users (
        user_id integer primary key autoincrement,
        username text not null,
        password text not null
    );
    """
    )
    cursor.execute(
        """
        insert into users (username, password) 
        values ('nico', 123), ('lynn', 321);
    """
    )


def print_all_users():
    result = cursor.execute("select * from users;")
    data = result.fetchall()
    print(data)


def i_change_password(username, new_password):
    cursor.execute(
        f"UPDATE users SET password = '{new_password}' WHERE username = '{username}'"
    )


def s_change_password(username, new_password):
    cursor.execute(
        "UPDATE users SET password = ? WHERE username = ?", (new_password, username)
    )


s_change_password("nico", "hached again' --")
print_all_users()
connection.commit()
connection.close()

i_change_password() 방식은 SQL Injection 위험에 노출되어 있는 방식이다.
s_change_password() 방식을 사용하여 ?를 placeholder로 사용해 보안을 강화한다.

 

executemany

import sqlite3

connection = sqlite3.connect("users.db")

cursor = connection.cursor()


def init_table():
    cursor.execute(
        """
    CREATE TABLE users (
        user_id integer primary key autoincrement,
        username text not null,
        password text not null
    );
    """
    )
    cursor.execute(
        """
        insert into users (username, password) 
        values ('nico', 123), ('lynn', 321);
    """
    )


def print_all_users():
    result = cursor.execute("select * from users;")
    data = result.fetchall()
    print(data)


def i_change_password(username, new_password):
    cursor.execute(
        f"UPDATE users SET password = '{new_password}' WHERE username = '{username}'"
    )


def s_change_password(username, new_password):
    cursor.execute(
        "UPDATE users SET password = ? WHERE username = ?", (new_password, username)
    )


data = [
    ("lannna", 567),
    ("bora", 123),
    ("max", 123),
    ("jja", 898),
]

# cursor.executemany("INSERT INTO users (username, password) VALUES (?, ?)", data)


data = [
    {"name": "lannna", "password": 567},
    {"name": "bora", "password": 123},
    {"name": "max", "password": 123},
    {"name": "jja", "password": 898},
]

cursor.executemany(
    "INSERT INTO users (username, password) VALUES (:name, :password)", data
)
print_all_users()

connection.commit()
connection.close()

placeholder를 사용할 때 전달해주어야 하는 값이 key와 value를 가질 때, :와 key를 사용하여 value를 넘길 수 있다.

 

Cursor, fetchmany, fetchone, etc

import sqlite3

conn = sqlite3.connect("movies_download.db")

cur = conn.cursor()

res = cur.execute(
    "SELECT movie_id, title FROM movies ORDER BY movie_id"
)  # 여기서 선택된 데이터들은 아직 내 파이썬 프로그램에(메모리에) 로드된 것이 아니다.

# all_movies = res.fetchall()  # 여기서 fetchall을 해야 실제로 로드가 된 것이다.

print(res.fetchmany(20))

print(res.fetchone(), res.fetchone(), res.fetchone())

for movie in res:
    print(
        movie
    )  # 순환하면서 모든 영화들을 가져오지만, fetchall처럼 한 번에 메모리에 로드하지는 않아서, 부담이 적다.

conn.commit()
conn.close()

fetchmany(20)을 했을 때 cursor는 20개의 데이터를 가져온 뒤 마지막 데이터 값의 위치에 cursor를 두게 된다.

그래서 다음에 fetchmany(20)을 또 하게 됐을 때, 기존 값들을 skip하고 20개의 값을 가져오게 된다.
(이러한 이유로 fetchmany(20)같은 동작을 하고 난 후, fetchall을 하게 되면 모든 값을 가져오는 것이 아니라 앞의 20개의 데이터는 포함이 되지 않는다.)

 

 

✅ Python에서 SQLite, PostgreSQL, MySQL과 소통하는 방식은 같다.

---

모든 driver가 Python Database API Specification을 만족하여 구현하였기 때문

 

PostgreSQL driver document

https://www.psycopg.org/psycopg3/docs/basic/usage.html

Basic module usage - psycopg 3.3.4.dev1 documentation

 

MySQL driver document

https://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html

MySQL :: MySQL Connector/Python Developer Guide :: 5.1 Connecting to MySQL Using Connector/Python