[pymysql 모듈] 파이썬과 mysql 연동시키기!

 

 

 

 

 

 

 

🤝PYTHON과 MySQL 연결시켜주기!

 

 

PYTHON과 MySQL을 연결해주는  pymysql 모듈 을 이용하게되면 !!!

python에서 sql 쿼리문을 작성하고, sql db 접속 및 테이블 작성이 가능하다!

한번 차근 차근 알아볼까나?? 😄

 

---

 

 

   목차    

1.  기본 (튜플형태로 얻기)

2. 딕셔너리 형태로 얻기

3. 연결된 SQL테이블에 데이터 입력하기 ( + connect 과정을 함수 지정하기)

4. 결과 값 개수 정하기 (fetch)

5. SQL 테이블 pandas로 출력해보기

 

---

In [1]:

from IPython.core.display import display, HTML 
display(HTML("<style>.container { width:100% !important; }</style>"))
#티스토리 업로드 원활하게:-)

 

 

 

Python과 SQL 연동하기¶

 

순서

1. 모듈 설치: !pip install pymysql
2. 모듈 import
3. db 접속 : db 접속 객체를 얻어온다. (connect 함수)
   • db 접속이 제대로 이루어지면 <pymysql.connections.connection as ~ ~ > 문구가 뜬다.
4. sql 쿼리 작성
5. 쿼리 실행 (execute 함수)
6. 결과값 얻어오기 (print하여 출력)
7. 접속 종료 (connect와 execute close하기)

In [1]:

!pip install pymysql
# pymysql을 설치해준다! (주피터의 경우 앞에 !를 붙임)

 

Requirement already satisfied: pymysql in c:\users\light\anaconda3\lib\site-packages (1.0.2)

In [2]:

import pymysql
#모듈을 임포트해준다.

 

기본 (튜플로 얻어오기)¶

In [5]:

#connect는 둘 사이를 연결해줍니다.
#conn이라는 변수에 pymysql.connet로 연결하는 코드식을 넣어줍니다.
#connect안에는 보다시피 유저,db명, host명 등 필요한 정보를 써준다.
#참고로 mysql의 기본 port는 3306이다. 

conn= pymysql.connect(user="homework",passwd="homework",db="homework", host="localhost", port=3306)
conn

Out[5]:

<pymysql.connections.Connection at 0x21eee465f40>

#이렇게 떴다면 연결이 성공했다는 것!

In [6]:

#connect로 연결해줬다면, cursor로 상호작용을 시켜줍니다.
cur = conn.cursor()

In [7]:

# emp 테이블을 조회하는 sql쿼리문을 작성해주고~
sql="select * from emp;"

In [9]:

#execute로 실행해줍니다.
#하나의 데이터만 실행할때는 execute()사용
cur.execute(sql)

Out[9]:

14

In [11]:

#execute 함수로 하나만 실행했으니, 반복문으로 하나씩 출력해줍니다.
for i in cur: 
	print(i)

 

(Decimal('7369'), 'SMITH', 'CLERK', Decimal('7902'), datetime.date(1980, 12, 17), Decimal('800.00'), None, Decimal('20'))
(Decimal('7499'), 'ALLEN', 'SALESMAN', Decimal('7698'), datetime.date(1981, 2, 20), Decimal('1600.00'), Decimal('300.00'), Decimal('30'))
(Decimal('7521'), 'WARD', 'SALESMAN', Decimal('7698'), datetime.date(1981, 2, 22), Decimal('1250.00'), Decimal('500.00'), Decimal('30'))
(Decimal('7566'), 'JONES', 'MANAGER', Decimal('7839'), datetime.date(1981, 4, 2), Decimal('2975.00'), None, Decimal('20'))
(Decimal('7654'), 'MARTIN', 'SALESMAN', Decimal('7698'), datetime.date(1981, 9, 28), Decimal('1250.00'), Decimal('1400.00'), Decimal('30'))
(Decimal('7698'), 'BLAKE', 'MANAGER', Decimal('7839'), datetime.date(1981, 5, 1), Decimal('2850.00'), None, Decimal('30'))
(Decimal('7782'), 'CLARK', 'MANAGER', Decimal('7839'), datetime.date(1981, 6, 9), Decimal('2450.00'), None, Decimal('10'))
(Decimal('7788'), 'SCOTT', 'ANALYST', Decimal('7566'), datetime.date(1987, 6, 28), Decimal('3000.00'), None, Decimal('20'))
(Decimal('7839'), 'KING', 'PRESIDENT', None, datetime.date(1981, 11, 17), Decimal('5000.00'), None, Decimal('10'))
(Decimal('7844'), 'TURNER', 'SALESMAN', Decimal('7698'), datetime.date(1981, 9, 8), Decimal('1500.00'), Decimal('0.00'), Decimal('30'))
(Decimal('7876'), 'ADAMS', 'CLERK', Decimal('7788'), datetime.date(1987, 7, 13), Decimal('1100.00'), None, Decimal('20'))
(Decimal('7900'), 'JAMES', 'CLERK', Decimal('7698'), datetime.date(1981, 12, 3), Decimal('950.00'), None, Decimal('30'))
(Decimal('7902'), 'FORD', 'ANALYST', Decimal('7566'), datetime.date(1981, 12, 3), Decimal('3000.00'), None, Decimal('20'))
(Decimal('7934'), 'MILLER', 'CLERK', Decimal('7782'), datetime.date(1982, 1, 23), Decimal('1300.00'), None, Decimal('10'))

In [12]:

#실행하고, 연결해줬던 함수들을 close 해줍니다.

cur.close()
conn.close()

 

딕셔너리로 얻어오기

¶

In [13]:

conn=pymysql.connect(host="localhost",db="homework",port=3306,user="homework",passwd="homework")

In [14]:

conn

Out[14]:

<pymysql.connections.Connection at 0x21eee462910>

In [17]:

#기본적으로는 튜플 형태로 받아오겠지만, 딕셔너리형태로 받아오기 위해서는 하나의 과정을 더 거쳐줍니다.
#cursor()함수 안에 pymysql.cursors.DictCursor

dictcur=conn.cursor(pymysql.cursors.DictCursor)
sql="select * from emp;"

In [18]:

dictcur.execute(sql)

Out[18]:

14

In [19]:

for i in dictcur: 
    print(i)

 

{'EMPNO': Decimal('7369'), 'ENAME': 'SMITH', 'JOB': 'CLERK', 'MGR': Decimal('7902'), 'HIREDATE': datetime.date(1980, 12, 17), 'SAL': Decimal('800.00'), 'COMM': None, 'DEPTNO': Decimal('20')}
{'EMPNO': Decimal('7499'), 'ENAME': 'ALLEN', 'JOB': 'SALESMAN', 'MGR': Decimal('7698'), 'HIREDATE': datetime.date(1981, 2, 20), 'SAL': Decimal('1600.00'), 'COMM': Decimal('300.00'), 'DEPTNO': Decimal('30')}
{'EMPNO': Decimal('7521'), 'ENAME': 'WARD', 'JOB': 'SALESMAN', 'MGR': Decimal('7698'), 'HIREDATE': datetime.date(1981, 2, 22), 'SAL': Decimal('1250.00'), 'COMM': Decimal('500.00'), 'DEPTNO': Decimal('30')}
{'EMPNO': Decimal('7566'), 'ENAME': 'JONES', 'JOB': 'MANAGER', 'MGR': Decimal('7839'), 'HIREDATE': datetime.date(1981, 4, 2), 'SAL': Decimal('2975.00'), 'COMM': None, 'DEPTNO': Decimal('20')}
{'EMPNO': Decimal('7654'), 'ENAME': 'MARTIN', 'JOB': 'SALESMAN', 'MGR': Decimal('7698'), 'HIREDATE': datetime.date(1981, 9, 28), 'SAL': Decimal('1250.00'), 'COMM': Decimal('1400.00'), 'DEPTNO': Decimal('30')}
{'EMPNO': Decimal('7698'), 'ENAME': 'BLAKE', 'JOB': 'MANAGER', 'MGR': Decimal('7839'), 'HIREDATE': datetime.date(1981, 5, 1), 'SAL': Decimal('2850.00'), 'COMM': None, 'DEPTNO': Decimal('30')}
{'EMPNO': Decimal('7782'), 'ENAME': 'CLARK', 'JOB': 'MANAGER', 'MGR': Decimal('7839'), 'HIREDATE': datetime.date(1981, 6, 9), 'SAL': Decimal('2450.00'), 'COMM': None, 'DEPTNO': Decimal('10')}
{'EMPNO': Decimal('7788'), 'ENAME': 'SCOTT', 'JOB': 'ANALYST', 'MGR': Decimal('7566'), 'HIREDATE': datetime.date(1987, 6, 28), 'SAL': Decimal('3000.00'), 'COMM': None, 'DEPTNO': Decimal('20')}
{'EMPNO': Decimal('7839'), 'ENAME': 'KING', 'JOB': 'PRESIDENT', 'MGR': None, 'HIREDATE': datetime.date(1981, 11, 17), 'SAL': Decimal('5000.00'), 'COMM': None, 'DEPTNO': Decimal('10')}
{'EMPNO': Decimal('7844'), 'ENAME': 'TURNER', 'JOB': 'SALESMAN', 'MGR': Decimal('7698'), 'HIREDATE': datetime.date(1981, 9, 8), 'SAL': Decimal('1500.00'), 'COMM': Decimal('0.00'), 'DEPTNO': Decimal('30')}
{'EMPNO': Decimal('7876'), 'ENAME': 'ADAMS', 'JOB': 'CLERK', 'MGR': Decimal('7788'), 'HIREDATE': datetime.date(1987, 7, 13), 'SAL': Decimal('1100.00'), 'COMM': None, 'DEPTNO': Decimal('20')}
{'EMPNO': Decimal('7900'), 'ENAME': 'JAMES', 'JOB': 'CLERK', 'MGR': Decimal('7698'), 'HIREDATE': datetime.date(1981, 12, 3), 'SAL': Decimal('950.00'), 'COMM': None, 'DEPTNO': Decimal('30')}
{'EMPNO': Decimal('7902'), 'ENAME': 'FORD', 'JOB': 'ANALYST', 'MGR': Decimal('7566'), 'HIREDATE': datetime.date(1981, 12, 3), 'SAL': Decimal('3000.00'), 'COMM': None, 'DEPTNO': Decimal('20')}
{'EMPNO': Decimal('7934'), 'ENAME': 'MILLER', 'JOB': 'CLERK', 'MGR': Decimal('7782'), 'HIREDATE': datetime.date(1982, 1, 23), 'SAL': Decimal('1300.00'), 'COMM': None, 'DEPTNO': Decimal('10')}

In [20]:

dictcur.close()
conn.close()

 

연결된 sql 테이블에 데이터 입력하기 (+ connect과정을 함수 지정하기)¶

In [22]:

def getConn():
    conn=pymysql.connect(host="localhost",db="homework",port=3306,user="homework",passwd="homework")
    return conn

In [30]:

empno_inp = input("사원 번호를 입력해주세요! :")
ename_inp = input("사원 명을 입력해주세요! :")
job_inp = input("직책명을 입력해주세요! :")

 

사원 번호를 입력해주세요! :7777
사원 명을 입력해주세요! :SONA
직책명을 입력해주세요! :NURSE

In [31]:

conn = getConn()
cur = conn.cursor()
sql ='''
insert into emp(empno,ename,job)
values(%s,%s,%s)'''

In [32]:

cur.execute(sql,(empno_inp,ename_inp,job_inp))

Out[32]:

1

In [33]:

conn.commit()

In [34]:

cur.close()
conn.close()

 

결과 값 개수 정하기 (fetch)¶

 

• fetchone(): 1개만
• fetchmany(숫자): 입력한 숫자만큼
• fetchall(): 전체

In [35]:

conn = getConn()
cur= conn.cursor()
sql="select * from emp"

In [38]:

cur.execute(sql)
rows = cur.fetchmany(2)

In [39]:

rows

Out[39]:

((Decimal('7369'),
  'SMITH',
  'CLERK',
  Decimal('7902'),
  datetime.date(1980, 12, 17),
  Decimal('800.00'),
  None,
  Decimal('20')),
 (Decimal('7499'),
  'ALLEN',
  'SALESMAN',
  Decimal('7698'),
  datetime.date(1981, 2, 20),
  Decimal('1600.00'),
  Decimal('300.00'),
  Decimal('30')))

 

pandas로 출력¶

In [46]:

import pandas as pd
conn = getConn()
cur=conn.cursor()

In [48]:

result = pd.read_sql_query("select * from emp;",conn)

In [50]:

result

Out[50]:

	EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
0	7369.0	SMITH	CLERK	7902.0	1980-12-17	800.0	NaN	20.0
1	7499.0	ALLEN	SALESMAN	7698.0	1981-02-20	1600.0	300.0	30.0
2	7521.0	WARD	SALESMAN	7698.0	1981-02-22	1250.0	500.0	30.0
3	7566.0	JONES	MANAGER	7839.0	1981-04-02	2975.0	NaN	20.0
4	7654.0	MARTIN	SALESMAN	7698.0	1981-09-28	1250.0	1400.0	30.0
5	7698.0	BLAKE	MANAGER	7839.0	1981-05-01	2850.0	NaN	30.0
6	7777.0	SONA	NURSE	NaN	None	NaN	NaN	NaN
7	7782.0	CLARK	MANAGER	7839.0	1981-06-09	2450.0	NaN	10.0
8	7788.0	SCOTT	ANALYST	7566.0	1987-06-28	3000.0	NaN	20.0
9	7839.0	KING	PRESIDENT	NaN	1981-11-17	5000.0	NaN	10.0
10	7844.0	TURNER	SALESMAN	7698.0	1981-09-08	1500.0	0.0	30.0
11	7876.0	ADAMS	CLERK	7788.0	1987-07-13	1100.0	NaN	20.0
12	7900.0	JAMES	CLERK	7698.0	1981-12-03	950.0	NaN	30.0
13	7902.0	FORD	ANALYST	7566.0	1981-12-03	3000.0	NaN	20.0
14	7934.0	MILLER	CLERK	7782.0	1982-01-23	1300.0	NaN	10.0

In [51]:

cur.close()
conn.close()

In [ ]: