파이썬과 MySQL 연동하기

파이썬으로 데이터베이스와 연결하면 데이터를 입력, 수정,조회하는 등 SQL의 활용도를 더 높일 수 있습니다. 

파이썬에서 이 그림의 순서대로 DB을 구축하고 사용해보겠습니다.

 

1번.MySQL 을 사용하기 위해서는 관련 모듈인 pymysql을 임포트 한후 pymysql.connect()로 데이터베이스와 연동해야합니다.

pymysql.connect(host=서버IP주소,user='사용자',password='비번',db='데이터베이스명',charset='문자세트')

 

2번. 커서는 데이터베이스에  SQL문을 실행하거나 실행된 결과를 돌려받는 통로로 생각하면 됩니다.

cur=conn.cursor()

3번. 테이블을 만들 차례입니다. 테이블을 만드는  SQL문을 커서이름.execute() 함수의 매개변수로 넘겨주면 SQL문이 데이터베이스에 실행됩니다.

cur.execute("create table usertable(id char(4), .......)")

4번. 데이터는 필요한 만큼 반복해서 입력합니다. 데이터 입력도 SQL문을 사용하므로 커서이름.execute()함수를 사용합니다.

cur.execute("insert into usertable values(~~~)")

5번. 입력한 데이터는 아직 데이터베이스에 완전히 저장된 것은 아닙니다. 이를 확실하게 저장하는 것을 commit이라고 합니다.

cnn.commit()

6번.데이터 베이스 이용이 끝났다면 데이터베이스를 닫아야합니다.

cnn.close()

 

위에는 코드를 활용하고 나서 연속해서 데이터 입력 프로그램을 만들어보겠습니다. 

import pymysql

# 전역변수 선언부
conn, cur = None, None
data1, data2, data3, data4 = "", "", "", ""
sql=""

# 메인 코드
conn = pymysql.connect(host='#ip주소', user='#아이디', password='#비번', db='#db이름', charset='utf8')
cur = conn.cursor()

while (True) :
    data1 = input("사용자 ID ==> ")
    if data1 == "" :
        break; #id에서 빈칸이 입력되면 break
    data2 = input("사용자 이름 ==> ")
    data3 = input("사용자 이메일 ==> ")
    data4 = input("사용자 출생연도 ==> ")
    sql = "INSERT INTO userTable VALUES('" + data1 + "','" + data2 + "','" + data3 + "'," + data4 + ")"
    cur.execute(sql)

conn.commit()
conn.close()

 

이번에는 파이썬으로 데이터를 조회하기 위해서는 어떻게 해야하는지 볼까요?

import pymysql

# 전역변수 선언부
con, cur = None, None
data1, data2, data3, data4 = "", "", "", ""
row=None

# 메인 코드
conn = pymysql.connect(host='IP주소', user='아이디', password='비번', db='데베이름', charset='utf8')
cur = conn.cursor()

cur.execute("SELECT * FROM userTable")

print("사용자ID    사용자이름    이메일        출생연도")
print("----------------------------------------------------")

while (True) :
    row = cur.fetchone()
    if row== None :
        break
    data1 = row[0]
    data2 = row[1]
    data3 = row[2]
    data4 = row[3]
    print("%5s   %15s   %20s   %d" % (data1, data2, data3, data4))

conn.close()

 

테이블 CRUD 함수 생성을 함수 형태로 변경해서 진행했습니다!!

import pymysql

config={
  'host':'127.0.0.1',
  'user':'root',
  'password':'******',
  'database':'****',
  'port':3306,
  'charset':'utf8',
  'use_unicode':True
  }
#테이블 생성 함수
def create_table():
  sql="create table goods(code int primary key,name varchar(30) not null, su int default 0, dan int default 0)"
  cursor.execute(sql)
  
def add_record():
  code=int(input('코드입력:'))
  name=input('상품명입력:')
  su=int(input('수량입력:'))
  dan=int(input('단가 입력:'))
  sql = "INSERT INTO goods (code, name, su, dan) VALUES (%s, %s, %s, %s)"
  cursor.execute(sql, (code, name, su, dan))
  conn.commit()
  
def update_record():
  code=int(input('수정할 코드입력:'))
  name=input('수정할 상품명입력:')
  su=int(input('수정할 수량입력:'))
  dan=int(input('수정할 단가 입력:'))
  
  sql=f"update goods set name='{name}',su={su},dan={dan} where code={code}"
  cursor.execute(sql)
  conn.commit()
  
def delete_record():
  code=int(input('삭제할 코드입력:'))
  sql=f"select * from goods where code={code}"
  cursor.execute(sql)
  rows=cursor.fetchall() 
  if rows:
    print('레코드 삭제')
    sql=f"delete from goods where code={code}"
    cursor.execute(sql)
    conn.commit()
    
  else:
    print('해당없음')  
  
def look_up_record():
  
  name=input('조회할 상품명 입력:')
  sql=f"select * from goods where name like '%{name}%'"
  cursor.execute(sql)
  rows=cursor.fetchall()
  
  if rows:
    for r in rows:
      print(r[0],r[1],r[2],r[3])
      
  else:
    print("해당 상품 없음")
  
  

try:
    conn = pymysql.connect(**config)
    cursor = conn.cursor()

    while True:
        statement = input("테이블생성/추가/삭제/수정/조회:").strip()

        if statement == "테이블 생성":
            create_table()

        elif statement == "추가":
            add_record()

        elif statement == "삭제":
            delete_record()

        elif statement == "수정":
            update_record()

        elif statement == "조회":
            look_up_record()

        else:
            print("잘못된 입력.")

        sentence = input("더하실껀가요?[y/n]").strip()
        if sentence.lower() != "y":
            break  # Exit the loop if the user does not want to continue

except Exception as e:
    print('db 연동 오류:', e)
    conn.rollback()

finally:
    cursor.close()
    conn.close()