[Python] 3/12 | 파이썬과 데이터베이스 연동(python에 MariaDB설치, 연동, statement기법 사용, preparedstatement기법 사용, select문 사용)

복습 및 배울내용

Math
디렉터리파일정보
	os.path
	glob

파일IO
	open
	pickle

*SQL
데이터베이스
	대형급
		DB2, Oracle
	중형급
		MariaDB
	소형급(파일 DB)
		SQLite

---

1. 파이썬과 데이터베이스 연동

1. python에 MariaDB 설치

아래 사이트 참고
mariadb.com/kb/en/mariadb-connector-python/

Python

 

먼저 cmd창을 열어서 파이썬과 mariadb 라이브러리를 다운받는다.
이때 방법은 2가지가 있다.
1. zip을 다운받고 원하는 위치에 넣고 활용
2. pip방법은 원격저장소에서 넣고 활용하는 것
이때 명령어로는 다음과 같다.
pip list : 현재 내 안에 설치된 외부모듈확인

pip install 모듈명 : 해당 모듈명 설치하기

 

아래 명령어를 입력

설치후에 다시 pip list를 하면 python pip를 설치하라는 내용이 사라진다.

다음 install 시킨다.
그러면 pip list목록에 mysqlclient가 생긴것을 알 수 있다.

설치된 디렉터리 위치는 다음과 같다.

 

혹은 아래처럼 mariadb를 설치해도 된다.

 

사용할 수 있는 함수들을 살펴보자.

import MySQLdb
import mariadb

print( dir( MySQLdb ) )
print( dir( mariadb ) )




['BINARY', 'Binary', 'Connect', 'Connection', 'DATE', 'DATETIME', 'DBAPISet', 'DataError', 'DatabaseError', 'Date', 'DateFromTicks', 'Error', 'FIELD_TYPE', 'IntegrityError', 'InterfaceError', 'InternalError', 'MySQLError', 'NUMBER', 'NotSupportedError', 'OperationalError', 'ProgrammingError', 'ROWID', 'STRING', 'TIME', 'TIMESTAMP', 'Time', 'TimeFromTicks', 'Timestamp', 'TimestampFromTicks', 'Warning', '__all__', '__builtins__', '__cached__', '__doc__', '__file__', '__loader__', '__name__', '__package__', '__path__', '__spec__', '_exceptions', '_mysql', 'apilevel', 'connect', 'constants', 'debug', 'escape', 'escape_string', 'get_client_info', 'paramstyle', 'release', 'string_literal', 'test_DBAPISet_set_equality', 'test_DBAPISet_set_equality_membership', 'test_DBAPISet_set_inequality', 'test_DBAPISet_set_inequality_membership', 'threadsafety', 'times', 'version_info']
['MySQLdb', '__builtins__', '__cached__', '__doc__', '__file__', '__loader__', '__name__', '__package__', '__spec__', 'mariadb']
['BINARY', 'Binary', 'Connect', 'Connection', 'DATE', 'DATETIME', 'DBAPISet', 'DataError', 'DatabaseError', 'Date', 'DateFromTicks', 'Error', 'FIELD_TYPE', 'IntegrityError', 'InterfaceError', 'InternalError', 'MySQLError', 'NUMBER', 'NotSupportedError', 'OperationalError', 'ProgrammingError', 'ROWID', 'STRING', 'TIME', 'TIMESTAMP', 'Time', 'TimeFromTicks', 'Timestamp', 'TimestampFromTicks', 'Warning', '__all__', '__builtins__', '__cached__', '__doc__', '__file__', '__loader__', '__name__', '__package__', '__path__', '__spec__', '_exceptions', '_mysql', 'apilevel', 'connect', 'constants', 'debug', 'escape', 'escape_string', 'get_client_info', 'paramstyle', 'release', 'string_literal', 'test_DBAPISet_set_equality', 'test_DBAPISet_set_equality_membership', 'test_DBAPISet_set_inequality', 'test_DBAPISet_set_inequality_membership', 'threadsafety', 'times', 'version_info']
['MySQLdb', '__builtins__', '__cached__', '__doc__', '__file__', '__loader__', '__name__', '__package__', '__spec__', 'mariadb']

 

2. MariaDB 연동하기

import mariadb

conn = mariadb.connect( host='127.0.0.1', port=3307, user='root', password='!123456', database='sample' )
print( conn )

conn.close()






<mariadb.connection object at 0x0000019ED7A8BE00>

 혹은 보통 다음과 같이 연동한다.

import mariadb

config = {
    'host': '127.0.0.1',
    'port': 3307,
    'user': 'root',
    'password': '!123456',
    'database': 'sample'
}

try:
    conn = mariadb.connect( **config )
    print( conn )
except mariadb.Error as err :
    print( '[error]: ', err )
else:
    print( '연결 종료' )
    conn.close()
    
    

<mariadb.connection object at 0x000002361934BE00>
연결 종료

 

3. statement 기법 사용

insert문 사용

기존 MariaDB에 있는 dept2테이블에 데이터를 입력해보자.

import mariadb

config = {
    'host': '127.0.0.1',
    'port': 3307,
    'user': 'root',
    'password': '!123456',
    'database': 'sample'
}

try:
    conn = mariadb.connect( **config )
    print( '연결성공' )
    
    cursor = conn.cursor()
    cursor.execute( "insert into dept2 values( 99, '연구', '대전' )" )
    
    #쿼리 실행이 제대로 되었는지 인증해주는 인증문장필요 : commit
    #commit은 select문을 제외한 DML문에는 무조건 써준다.
    conn.commit()
    cursor.close()
    
    print( '입력성공' )
    
except mariadb.Error as err :
    print( '[error]: ', err )
else:
    print( '연결 종료' )
    conn.close()
    
    
    
    
연결성공
입력성공
연결 종료

 

미리 sql문을 만들어서 사용할 수 있다.

import mariadb

config = {
    'host': '127.0.0.1',
    'port': 3307,
    'user': 'root',
    'password': '!123456',
    'database': 'sample'
}

try:
    conn = mariadb.connect( **config )
    print( '연결성공' )
    
    cursor = conn.cursor()
    sql_insert = "insert into dept2 values( 50, '연구', '대전' )"
    cursor.execute( sql_insert )
    
    #쿼리 실행이 제대로 되었는지 인증해주는 인증문장필요 : commit
    #commit은 select문을 제외한 DML문에는 무조건 써준다.
    conn.commit()
    cursor.close()
    
    print( '입력성공' )
    
except mariadb.Error as err :
    print( '[error]: ', err )
else:
    print( '연결 종료' )
    conn.close()
    
    
    
연결성공
입력성공
연결 종료

 

입력할 데이터를 분리해서 사용해보자.

import mariadb

config = {
    'host': '127.0.0.1',
    'port': 3307,
    'user': 'root',
    'password': '!123456',
    'database': 'sample'
}

try:
    conn = mariadb.connect( **config )
    print( '연결성공' )
    
    cursor = conn.cursor()
    sql_insert = "insert into dept2 values( %s, '%s', '%s' )" %( 60, '연구', '대전' )
    cursor.execute( sql_insert )
    
    #쿼리 실행이 제대로 되었는지 인증해주는 인증문장필요 : commit
    #commit은 select문을 제외한 DML문에는 무조건 써준다.
    conn.commit()
    cursor.close()
    
    print( '입력성공' )
    
except mariadb.Error as err :
    print( '[error]: ', err )
else:
    print( '연결 종료' )
    conn.close()
    
    
연결성공
입력성공
연결 종료

 

format을 이용할 수 있다.

import mariadb

config = {
    'host': '127.0.0.1',
    'port': 3307,
    'user': 'root',
    'password': '!123456',
    'database': 'sample'
}

try:
    conn = mariadb.connect( **config )
    print( '연결성공' )
    
    cursor = conn.cursor()
    #sql_insert = "insert into dept2 values( %s, '%s', '%s' )" %( 60, '연구', '대전' )
    sql_insert = "insert into dept2 values( {0}, '{1}', '{2}' )".format( 61, '기획', '서울' )
    cursor.execute( sql_insert )
    
    #쿼리 실행이 제대로 되었는지 인증해주는 인증문장필요 : commit
    #commit은 select문을 제외한 DML문에는 무조건 써준다.
    conn.commit()
    cursor.close()
    
    print( '입력성공' )
    
except mariadb.Error as err :
    print( '[error]: ', err )
else:
    print( '연결 종료' )
    conn.close()
    

 

4. preparedstatement 기법사용

import mariadb

config = {
    'host': '127.0.0.1',
    'port': 3307,
    'user': 'root',
    'password': '!123456',
    'database': 'sample'
}

try:
    conn = mariadb.connect( **config )
    print( '연결성공' )
    
    cursor = conn.cursor()
    
    #preparedstatement 기법
    sql_insert = 'insert into dept2 values ( %s, %s, %s )'
    sql_data = ( 70, '영업', '제주' )
    cursor.execute( sql_insert, sql_data )
    
    conn.commit()
    cursor.close()
    
    print( '입력성공' )
    
except mariadb.Error as err :
    print( '[error]: ', err )
else:
    print( '연결 종료' )
    conn.close()
    

 

실습) 70번부서의 제주를 부산으로 바꾸고 70번부서를 지워보자.(update, delete문 사용)

import mariadb

config = {
    'host': '127.0.0.1',
    'port': 3307,
    'user': 'root',
    'password': '!123456',
    'database': 'sample'
}

try:
    conn = mariadb.connect( **config )
    print( '연결성공' )
    
    cursor = conn.cursor()
    
    #preparedstatement 기법
    sql_update = 'update dept2 set loc = %s where deptno = %s'
    sql_data = ( '부산', 70 )
    cursor.execute( sql_update, sql_data )
    
    conn.commit()
    cursor.close()
    
    print( '입력성공' )
    
except mariadb.Error as err :
    print( '[error]: ', err )
else:
    print( '연결 종료' )
    conn.close()
    

import mariadb

config = {
    'host': '127.0.0.1',
    'port': 3307,
    'user': 'root',
    'password': '!123456',
    'database': 'sample'
}

try:
    conn = mariadb.connect( **config )
    print( '연결성공' )
    
    cursor = conn.cursor()
    
    #preparedstatement 기법
    sql_delete = 'delete from dept2 where deptno = 70'
    cursor.execute( sql_delete )
    
    conn.commit()
    cursor.close()
    
    print( '입력성공' )
    
except mariadb.Error as err :
    print( '[error]: ', err )
else:
    print( '연결 종료' )
    conn.close()
    

 

실습) 우편번호 검색기 mariaDB에 집어넣기

먼저 현재디렉터리 안에 해당 csv파일을 집어넣는다.

import mariadb

config = {
    'host': '127.0.0.1',
    'port': 3307,
    'user': 'root',
    'password': '!123456',
    'database': 'sample'
}

f = open( './zipcode_all_utf8_type2.csv', 'r', encoding='utf-8' )

try:
    conn = mariadb.connect( **config )
    print( '연결성공' )
    cursor = conn.cursor()
    
    #zipcode, sido, gugun, dong, ri, bunzi, seq
    sql_insert = 'insert into zipcode2 values( %s, %s, %s, %s, %s, %s, %s )'
    
    for line in f.readlines():
        addresses = line.split( ',' )
        sql_data = ( addresses[0], addresses[1], addresses[2], addresses[3], addresses[4], addresses[5], addresses[6] )
        cursor.execute( sql_insert, sql_data )
        conn.commit()
        sql_data = ()
        
    
    cursor.close()
    
    print( '입력성공' )
    
except mariadb.Error as err :
    print( '[error]: ', err )
else:
    print( '연결 종료' )
    conn.close()
    

 

5. select문 사용

import mariadb

config = {
    'host': '127.0.0.1',
    'port': 3307,
    'user': 'root',
    'password': '!123456',
    'database': 'sample'
}

try:
    conn = mariadb.connect( **config )
    print( '연결성공' )
    
    cursor = conn.cursor()

    sql_select = 'select deptno, dname, loc from dept2 limit 0, 2'
    cursor.execute( sql_select )
    
    for( deptno, dname, loc ) in cursor:    #해당 컬럼명의 데이터들이 cursor안에 담겨져 나온다.
        print( deptno, dname, loc )
    
    cursor.close()
    
    print( '입력성공' )
    
except mariadb.Error as err :
    print( '[error]: ', err )
else:
    print( '연결 종료' )
    conn.close()
    
    
    
연결성공
20 RESEARCH DALLAS
30 SALES CHICAGO
입력성공
연결 종료

 

preparedstatement 사용

import mariadb

config = {
    'host': '127.0.0.1',
    'port': 3307,
    'user': 'root',
    'password': '!123456',
    'database': 'sample'
}

try:
    conn = mariadb.connect( **config )
    print( '연결성공' )
    
    cursor = conn.cursor()

    sql_select = 'select empno, ename, job, mgr from emp where ename like %s'
    sql_data = ( 'S%', )
    #sql_data = [ 'S%' ] 이렇게 써도 된다.
    cursor.execute( sql_select, sql_data )
    
    for( empno, ename, job, mgr ) in cursor:    #해당 컬럼명의 데이터들이 cursor안에 담겨져 나온다.
        print( empno, ename, job, mgr )
    
    cursor.close()
    
    print( '입력성공' )
    
except mariadb.Error as err :
    print( '[error]: ', err )
else:
    print( '연결 종료' )
    conn.close()
    
    
연결성공
7369 SMITH CLERK 7902
7788 SCOTT ANALYST 7566
입력성공
연결 종료

 

실습) 우편번호 검색기 select문으로 가져오기

import mariadb

config = {
    'host': '127.0.0.1',
    'port': 3307,
    'user': 'root',
    'password': '!123456',
    'database': 'sample'
}

try:
    conn = mariadb.connect( **config )
    print( '연결성공' )
    
    cursor = conn.cursor()

    sql_select = 'select zipcode, sido, gugun, dong, ri, bunji from zipcode2 where dong like %s'
    sql_data = ( '개포%', )
    #sql_data = [ 'S%' ] 이렇게 써도 된다.
    cursor.execute( sql_select, sql_data )
    
    for( zipcode, sido, gugun, dong, ri, bunji ) in cursor:    #해당 컬럼명의 데이터들이 cursor안에 담겨져 나온다.
        print( zipcode, sido, gugun, dong, ri, bunji )
    
    cursor.close()
    
    print( '입력성공' )
    
except mariadb.Error as err :
    print( '[error]: ', err )
else:
    print( '연결 종료' )
    conn.close()