Python x MySQL μ°λ
1.
pymysql λͺ¨λ μ€μΉ λ° μ¬μ©
2.
λ°μ΄ν° μμ€λ‘ μ μ
β’
λ°μ΄ν° μμ€ : DB μ μ μ 보
β¦
κ³μ
β¦
λΉλ°λ²νΈ
β¦
νΈμ€νΈ
β¦
μ€ν€λ§(λ°μ΄ν°λ² μ΄μ€)
3.
λ°μ΄ν° λ² μ΄μ€ μ μ
4.
컀μ μμ±
5.
μ‘°ν λ° μ‘°μ ν¨μ μ¬μ©
β’
μ‘°ν ν¨μ : fetchall(), fetchone()
β’
μ‘°μ ν¨μ : excute() - λ±λ‘,μμ ,μμ
pymysql λͺ¨λ μ€μΉ λ° μ¬μ©
β’
pymysql λͺ¨λ
β’
pymysql λͺ¨λ μ€μΉ
β’
pymysql λͺ¨λ μ¬μ©
pymysql λͺ¨λ
Pythonμμ MySQL λ°μ΄ν°λ² μ΄μ€μ μνΈμμ©νκΈ° μν λͺ¨λ
pymysql λͺ¨λμ μ¬μ©νλ©΄ Python μ½λμμ MySQL λ°μ΄ν°λ² μ΄μ€λ₯Ό λ€λ£° μ μμ΅λλ€. μΌλ°μ μΌλ‘ λ€μκ³Ό κ°μ μμ
μ μνν μ μμ΅λλ€:
1.
μ°κ²°: MySQL λ°μ΄ν°λ² μ΄μ€μ μ°κ²°ν©λλ€.
2.
쿼리 μ€ν: SQL 쿼리λ₯Ό μ€ννμ¬ λ°μ΄ν°λ₯Ό μ‘°ν, μΆκ°, μμ λλ μμ ν©λλ€.
3.
κ²°κ³Ό μ²λ¦¬: 쿼리 μ€ν κ²°κ³Όλ₯Ό λ°μμμ Python μ½λμμ μ²λ¦¬ν©λλ€.
4.
μ°κ²° μ’
λ£: λ°μ΄ν°λ² μ΄μ€μμ μ°κ²°μ μ’
λ£ν©λλ€.
pymysql λͺ¨λ μ€μΉ
pip install pymysql
Bash
볡μ¬
MySQL μλ²μμ ν΅μ μ 보μνκΈ° μν΄ TLS/SSLμ μ¬μ©νκΈ° μν΄, cryptography λΌμ΄λΈλ¬λ¦¬λ μ€μΉν©λλ€.
pip install cryptography
Bash
볡μ¬
pymysql λͺ¨λ μ¬μ©
import pymysql
Bash
볡μ¬
pymysql μ μ¬μ©νμ΄ μν κΈ°λ³Έ μ½λ
import pymysql
# MySQL λ°μ΄ν°λ² μ΄μ€μ μ°κ²°
connection = pymysql.connect(host='νΈμ€νΈλͺ
',
user='μ¬μ©μλͺ
',
password='λΉλ°λ²νΈ',
database='λ°μ΄ν°λ² μ΄μ€λͺ
',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
try:
with connection.cursor() as cursor:
# 쿼리 μ€ν
sql = "SELECT * FROM ν
μ΄λΈλͺ
"
cursor.execute(sql)
# κ²°κ³Ό κ°μ Έμ€κΈ°
result = cursor.fetchall()
for row in result:
print(row)
finally:
# μ°κ²° μ’
λ£
connection.close()
Python
볡μ¬
λ°μ΄ν° μμ€λ‘ μ μ
λ°μ΄ν° μμ€ : DB μ μ μ 보
β’
κ³μ
β’
λΉλ°λ²νΈ
β’
νΈμ€νΈ
β’
μ€ν€λ§(λ°μ΄ν°λ² μ΄μ€)
μμ± | κ° |
νΈμ€νΈ | 127.0.0.1 |
μ¬μ©μ | aloha |
λΉλ°λ²νΈ | 123456 |
λ°μ΄ν°λ² μ΄μ€ | aloha |
νΈμ€νΈλ λ°μ΄ν°λ² μ΄μ€ μλ²μ ip μ£Όμλ₯Ό μλ―Έν©λλ€. νμ¬ PC λ‘ μ μνλ€λ©΄, 127.0.0.1 λλ localhost λ‘ μ§μ νλ©΄ λ©λλ€. λ§μ½ λ€λ₯Έ DB μλ²κ° λ€λ₯Έ μ»΄ν¨ν°λΌλ©΄ ν΄λΉ DBμλ²μ IP μ£Όμλ₯Ό μ
λ ₯ν©λλ€.
μ¬μ©μλ μ¬κΈ°μλ aloha λΌκ³ μμ±νκ³ μ κ·Όν©λλ€. λΉλ°λ²νΈλ 123456 μΌλ‘ μ§μ νμμ΅λλ€. λ°μ΄ν° μμ€ (μ μμ 보)λ 미리 μμ±λ κ³μ μ μ 보λ₯Ό μ
λ ₯ν©λλ€. database(μ€ν€λ§)λ μ¬κΈ°μλ aloha λΌκ³ μμ±νκ³ μ¬μ©ν©λλ€.
λ°μ΄ν° λ² μ΄μ€ μ μ
pymysql λͺ¨λμ connect ν¨μλ₯Ό μ¬μ©νμ¬ DBμ μ μν©λλ€.
# MySQL μλ²μ μ μ
conn = pymysql.connect(
host='127.0.0.1',
user='aloha',
password='123456',
database='aloha',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor
)
Python
볡μ¬
pymysql.connect( λ°μ΄ν°μμ€ ) ννλ‘ ν¨μλ₯Ό νΈμΆνκ³ μ μμ μΌλ‘ DBμ μ μμ΄ μ±κ³΅λλ©΄, μ°κ²° κ°μ²΄λ₯Ό λ°νν©λλ€. μ΄ conn κ°μ²΄λ₯Ό ν΅ν΄μ 컀μλ₯Ό μμ±ν μ μμ΅λλ€.
컀μ μμ±
λ°μ΄ν°λ² μ΄μ€μμ 쿼리λ₯Ό μ€ννκ³ κ·Έ κ²°κ³Όλ₯Ό κ°μ Έμ€λ λ° μ¬μ©λλ κ°μ²΄μ
λλ€.
컀μμ μν
1.
쿼리 μ€ν: 컀μλ₯Ό μ¬μ©νμ¬ λ°μ΄ν°λ² μ΄μ€μ SQL 쿼리λ₯Ό μ€νν©λλ€. 쿼리λ λ°μ΄ν°λ₯Ό μ‘°ν, μΆκ°, μμ , μμ νλ λ° μ¬μ©λ μ μμ΅λλ€.
2.
κ²°κ³Ό κ°μ Έμ€κΈ°: 컀μλ₯Ό μ¬μ©νμ¬ μ€νλ 쿼리μ κ²°κ³Όλ₯Ό κ°μ Έμ΅λλ€. μ΄ κ²°κ³Όλ νλ μ΄μμ λ μ½λλ‘ κ΅¬μ±λλ©°, κ° λ μ½λλ νλ μ΄μμ μ΄μ ν¬ν¨ν©λλ€.
3.
κ²°κ³Ό μ²λ¦¬: κ°μ Έμ¨ κ²°κ³Όλ₯Ό Python μ½λμμ μ²λ¦¬νκ³ μ¬μ©ν μ μλλ‘ ν©λλ€. μ΄λ₯Ό ν΅ν΄ κ°λ°μλ λ°μ΄ν°λ² μ΄μ€μμ κ°μ Έμ¨ λ°μ΄ν°λ₯Ό μ‘°μνκ³ λΆμν μ μμ΅λλ€.
4.
νΈλμμ
κ΄λ¦¬: 컀μλ₯Ό μ¬μ©νμ¬ νΈλμμ
μ μμνκ³ μ’
λ£νλ©°, νΈλμμ
λ΄μμ 쿼리λ₯Ό μ€νν©λλ€. μ΄λ₯Ό ν΅ν΄ λ°μ΄ν°λ² μ΄μ€μ μΌκ΄μ±κ³Ό 무결μ±μ μ μ§ν μ μμ΅λλ€.
컀μ μμ±
with connection.cursor() as cursor:
# 쿼리 μ€ν
sql = "SQL 쿼리"
cursor.execute(sql)
# κ²°κ³Ό κ°μ Έμ€κΈ°
result = cursor.μ‘°νλ°μ‘°μν¨μ()
Python
볡μ¬
μμ κ°μ΄ μ€νν SQL λ¬Έμ₯μ βλ¬Έμμ΄β λ‘ μμ±νκ³ , excute(sql) ν¨μλ‘ μΏΌλ¦¬λ₯Ό DB μ μ€ν μμ²ν©λλ€. κ·Έ ν, λ°μ΄ν° μ‘°ν λ° μ‘°μ ν¨μλ₯Ό νΈμΆνμ¬ κ²°κ³Όλ₯Ό λ°νλ°μ΅λλ€.
μ‘°ν λ° μ‘°μ ν¨μ μ¬μ©
β’
μ‘°ν ν¨μ : fetchall(), fetchone()
β’
μ‘°μ ν¨μ : excute() - λ±λ‘,μμ ,μμ
μ‘°ν ν¨μ : fetchall(), fetchone()
ν¨μ | μ€λͺ
| λ°νκ° |
fetchall() | νμ¬ μ€νλ 쿼리μ λͺ¨λ κ²°κ³Όλ₯Ό κ°μ Έμ΅λλ€. | κ²°κ³Όλ₯Ό ν¬ν¨νλ λͺ¨λ νμ λ°ν |
fetchone() | νμ¬ μ€νλ 쿼리μ λ€μ κ²°κ³Ό ν νλλ₯Ό κ°μ Έμ΅λλ€. | λ€μ κ²°κ³Ό νμ λ°ν |
fetchall() ν¨μλ νμ¬ μ€νλ 쿼리μ λͺ¨λ κ²°κ³Ό νμ κ°μ Έμ΅λλ€. μ΄ ν¨μλ₯Ό νΈμΆνλ©΄ 쿼리μ λͺ¨λ κ²°κ³Όλ₯Ό κ°μ Έμμ 리μ€νΈ ννλ‘ λ°νν©λλ€. κ° νμ 리μ€νΈμ μμλ‘ ννλ©λλ€.
fetchone() ν¨μλ νμ¬ μ€νλ 쿼리μ κ²°κ³Ό ν μ€ λ€μ ν νλλ₯Ό κ°μ Έμ΅λλ€. μ΄ ν¨μλ₯Ό νΈμΆν λλ§λ€ λ€μ κ²°κ³Ό νμ΄ λ°νλ©λλ€. λ§μ½ λ μ΄μ κ²°κ³Όκ° μμΌλ©΄ Noneμ λ°νν©λλ€.
β’
νμ ν
μ΄λΈμμ λ°μ΄ν° λͺ©λ‘ μ‘°ν
# pip install pymysql
# pip install cryptography
import pymysql
# MySQL μλ²μ μ μ
conn = pymysql.connect(
host='127.0.0.1',
user='joeun',
password='123456',
database='joeun',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor
)
try:
with conn.cursor() as cursor:
sql = "SELECT * FROM νμ"
cursor.execute(sql) # DBμ 쿼리 μμ²
students = cursor.fetchall() # κ²°κ³Ό
for student in students:
print( student )
except pymysql.MySQLError as e:
print('MySQL μλ¬ : ', e)
finally:
conn.close()
Python
볡μ¬
β’
νμ ν
μ΄λΈμμ λ¨μΌ ν
μ΄ν° μ‘°ν
# pip install pymysql
# pip install cryptography
import pymysql
# MySQL μλ²μ μ μ
conn = pymysql.connect(
host='127.0.0.1',
user='joeun',
password='123456',
database='joeun',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor
)
try:
with conn.cursor() as cursor:
sql = "SELECT * FROM νμ WHERE no = 1 "
cursor.execute(sql) # DBμ 쿼리 μμ²
student = cursor.fetchone() # κ²°κ³Ό
print( student )
except pymysql.MySQLError as e:
print('MySQL μλ¬ : ', e)
finally:
conn.close()
Python
볡μ¬
μ‘°μ ν¨μ : excute() - λ±λ‘,μμ ,μμ
excute( SQL, νλΌλ―Έν° )
νλΌλ―Έν° | μ€λͺ
|
SQL | μ€νν SQL 쿼리μ
λλ€. |
νλΌλ―Έν° | νμμ λ°λΌ SQL 쿼리μ μ λ¬ν νλΌλ―Έν°μ
λλ€.
|
SQL λ¬Έμ νλΌλ―Έν°λ₯Ό λ§€ννκΈ° μν΄μ β%sβ κΈ°νΈλ₯Ό μμ±ν©λλ€.
μλμ κ°μ΄ λ°μ΄ν° μ‘°ν, λ±λ‘, μμ , μμ 쿼리μ λνμ¬ νλΌλ―Έν°λ₯Ό λ§€ννλ κ²½μ° %s κΈ°νΈλ₯Ό μ¬μ©ν©λλ€.
sql = 'SELECT * FROM νμ WHERE no = %s'
Python
볡μ¬
sql = " INSERT INTO νμ (std_id, name, tel) "\
+ " VALUES (%s, %s, %s) "
Python
볡μ¬
sql = " UPDATE νμ "\
+ " SET std_id = %s "\
+ " ,name = %s "\
+ " ,tel = %s "\
+ " ,upd_date = now() "\
+ " WHERE std_id = %s "
Python
볡μ¬
sql = " DELETE FROM νμ "\
+ " WHERE std_id = %s "
Python
볡μ¬
μμ μμ±ν SQL λ¬Έμ₯μ λνμ¬, execute( sql, (νλΌλ―Έν°1, νλΌλ―Έν°2,) ) ν¨μλ₯Ό νΈμΆνλ©΄,
%s κΈ°νΈμ νλΌλ―Έν°1, 2 λ₯Ό μμλ₯Ό λ§μΆ°μ λ§€νν©λλ€.
β’
νμ λ°μ΄ν° μ‘°ν
β’
νμ λ°μ΄ν° μΆκ°
β’
νμ λ°μ΄ν° μμ
β’
νμ λ°μ΄ν° μμ
νμ λ°μ΄ν° μ‘°ν
# pip install pymysql
# pip install cryptography
import pymysql
# MySQL μλ²μ μ μ
conn = pymysql.connect(
host='127.0.0.1',
user='joeun',
password='123456',
database='joeun',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor
)
try:
with conn.cursor() as cursor:
sql = "SELECT * FROM νμ WHERE no = %s "
cursor.execute(sql, 1) # DBμ 쿼리 μμ²
student = cursor.fetchone() # κ²°κ³Ό
print( student )
except pymysql.MySQLError as e:
print('MySQL μλ¬ : ', e)
finally:
conn.close()
Python
볡μ¬
νμ λ°μ΄ν° μΆκ°
import pymysql
# MySQL μλ²μ μ μ
conn = pymysql.connect(
host='127.0.0.1',
user='joeun',
password='123456',
database='joeun',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor
)
std_id = input('νλ² : ')
name = input('μ΄λ¦ : ')
tel = input('μ νλ²νΈ : ')
try:
# 컀μ μμ±
with conn.cursor() as cursor:
# λ°μ΄ν° λ±λ‘ 쿼리
sql = " INSERT INTO νμ (std_id, name, tel) "\
+ " VALUES (%s, %s, %s) "
# + " VALUES ('B2401', 'νκΈΈλ', '010-1234-1234') "
# result = cursor.execute(sql) # 쿼리 μ€ν μμ²
result = cursor.execute(sql, (std_id, name, tel)) # νλΌλ―Έν° μ§μ
print('{}νμ λ°μ΄ν° λ±λ‘ μλ£'.format(result))
# λ³κ²½μ¬ν μ μ©
conn.commit()
except pymysql.MySQLError as e:
print("λ°μ΄ν° λ±λ‘ μ€ μλ¬ λ°μ : ", e)
finally:
conn.close()
Python
볡μ¬
νμ λ°μ΄ν° μμ
import pymysql
# MySQL μλ²μ μ μ
conn = pymysql.connect(
host='127.0.0.1',
user='joeun',
password='123456',
database='joeun',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor
)
std_id = input('νλ² : ')
name = input('μ΄λ¦ : ')
tel = input('μ νλ²νΈ : ')
try:
# 컀μ μμ±
with conn.cursor() as cursor:
# λ°μ΄ν° μμ 쿼리
sql = " UPDATE νμ "\
+ " SET std_id = %s "\
+ " ,name = %s "\
+ " ,tel = %s "\
+ " ,upd_date = now() "\
+ " WHERE std_id = %s "
# + " VALUES ('B2401', 'νκΈΈλ', '010-1234-1234') "
# result = cursor.execute(sql) # 쿼리 μ€ν μμ²
result = cursor.execute(sql, (std_id, name, tel, std_id)) # νλΌλ―Έν° μ§μ
print('{}νμ λ°μ΄ν° μμ μλ£'.format(result))
# λ³κ²½μ¬ν μ μ©
conn.commit()
except pymysql.MySQLError as e:
print("λ°μ΄ν° μμ μ€ μλ¬ λ°μ : ", e)
finally:
conn.close()
Python
볡μ¬
νμ λ°μ΄ν° μμ
import pymysql
# MySQL μλ²μ μ μ
conn = pymysql.connect(
host='127.0.0.1',
user='joeun',
password='123456',
database='joeun',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor
)
std_id = input('νλ² : ')
try:
# 컀μ μμ±
with conn.cursor() as cursor:
# λ°μ΄ν° μμ 쿼리
sql = " DELETE FROM νμ "\
+ " WHERE std_id = %s "
result = cursor.execute(sql, (std_id)) # νλΌλ―Έν° μ§μ
print('{}νμ λ°μ΄ν° μμ μλ£'.format(result))
# λ³κ²½μ¬ν μ μ©
conn.commit()
except pymysql.MySQLError as e:
print("λ°μ΄ν° μμ μ€ μλ¬ λ°μ : ", e)
finally:
conn.close()
Python
볡μ¬