Lecture 86 - Python(7) 파이썬에 데이터베이스 연결

데이터베이스를 연결해 보자

python
        데이터베이스 연결
        
        내장 데이터베이스 (+API)
                sqlite(초경량형 데이터베이스 - 파일 - SQL 문)
        외부 데이터베이스(API 추가)
                MySQL, Oracle, DB2 ...
        * MySQL(mariadb) - python

mysqlclient-1.3.13-cp36-cp36m-win_amd64.whl -> zip

C:\Python\Python36\Lib\site-package에서 압축 풀기

공식사이트
        - 버전때문
=>
http://www.lfd.uci.edu/~gohlke/pythonlibs/

mac에서 설치 하는법 아래 참조

Mac OS X Python 3 MySQL 연동

MySQL 접속법

C:\Users\kitcoop>cd C:\Program Files\MySQL\MySQL Server 8.0\bin

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL 경로를 설정해 두고 파이선을 실행할 수 있게 설정을 해주자

mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 8.0\bin>python
Python 3.6.5 (v3.6.5:f59c0932b4, Mar 28 2018, 17:00:18) [MSC v.1900 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
>>> import MySQLd
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ModuleNotFoundError: No module named 'MySQLd'
>>> conn = MySQLdb.connect(host='127.0.0.1', user='root', password='123456', database='sakila')
>>> print(conn)
<_mysql.connection open to '127.0.0.1' at 22dbe68>

암호를 잘못 치면 뜨는 에러

>>> conn = MySQLdb.connect(host='127.0.0.1', user='root', password='12345', database='sakila')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Python\Python36\lib\site-packages\MySQLdb\__init__.py", line 85, in Connect
    return Connection(*args, **kwargs)
  File "C:\Python\Python36\lib\site-packages\MySQLdb\connections.py", line 204, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
_mysql_exceptions.OperationalError: (1045, "Access denied for user 'root'@'localhost' (using password: YES)")

아이피를 잘 못 치면 나오는 에러(검색 하느라고 멈춰있다가 에러가 뜸)

>>> conn = MySQLdb.connect(host='127.0.0.0', user='root', password='123456', database='sakila')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Python\Python36\lib\site-packages\MySQLdb\__init__.py", line 85, in Connect
    return Connection(*args, **kwargs)
  File "C:\Python\Python36\lib\site-packages\MySQLdb\connections.py", line 204, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
_mysql_exceptions.OperationalError: (2003, "Can't connect to MySQL server on '127.0.0.0' (10060)")

Disconnect하는 법

>>> conn.close()
>>> quit()

C:\Program Files\MySQL\MySQL Server 8.0\bin>

이클립스를 켜서 연동시켜 보자
Ex06.ex01

import MySQLdb

conn = MySQLdb.connect(host='127.0.0.1', user='root', password='123456', database='sakila')
print(conn)
conn.close()

import MySQLdb

config = {
	'host': '127.0.0.1',
	'user': 'root',
	'password': '123456',
	'database': 'sakila' 
	}

conn = MySQLdb.connect(**config)
print(conn)
conn.close()

아이피를 잘 못 적어서 에러가 나면 아래와 같다

try except구문을 사용해서 오류를 확인해보자 - 비번을 잘못 넣었을 때

import MySQLdb

config = {
	'host': '127.0.0.1',
	'user': 'root',
	'password': '12345',
	'database': 'sakila' 
	}
try:
	conn = MySQLdb.connect(**config)
	print(conn)
except Exception as err :
	print('오류 : ', err)
else:
	conn.close()

오류 :  (1045, "Access denied for user 'root'@'localhost' (using password: YES)")

import MySQLdb

config = {
	'host': '127.0.0.1',
	'user': 'root',
	'password': '12345',
	'database': 'sakila' 
	}
try:
	conn = MySQLdb.connect(**config)
	print(conn)
except MySQLdb.Error as err :
	print('오류 : ', err)
else:
	conn.close()

빈데이터가 들어간 actor2라는 테이블을 만들자

select * from actor where 1 != 1; 구문은 actor 테이블의 스키마를 다 가져오지만 데이터는 빈데이터로 가져오겠다 라는 뜻이다

mysql> use sakila
Database changed
mysql> create table actor2 as select * from actor where 1 != 1;
Query OK, 0 rows affected (0.51 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(*) from actor2;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> desc actor2;
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                 | Null | Key | Default           | Extra                       |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| actor_id    | smallint(5) unsigned | NO   |     | 0                 |                             |
| first_name  | varchar(45)          | NO   |     | NULL              |                             |
| last_name   | varchar(45)          | NO   |     | NULL              |                             |
| last_update | timestamp            | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.04 sec)

import MySQLdb

config = {
	'host': '127.0.0.1',
	'user': 'root',
	'password': '123456',
	'database': 'sakila',
	'charset': 'utf8',
	'use_unicode': True
	}
try:
	conn = MySQLdb.connect(**config)
	print('데이터베이스가 연결되었습니다.')
	
	cursor = conn.cursor()
	sql_insert = ("insert into actor2 values (1, '길동', '홍', now())")
	
	cursor.execute(sql_insert)
	
	conn.commit()
	
	print('처리가 완료되었습니다.')
	
except MySQLdb.Error as err :
	print('오류 : ', err)
else:
	conn.close()
	print('데이터베이스에 연결이 종료되었습니다.')

 

mysql> select count(*) from actor2;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> select * from actor2;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        1 | 길동       | 홍        | 2018-09-18 10:55:34 |
+----------+------------+-----------+---------------------+
1 row in set (0.02 sec)

1, '길동', '홍', now() 데이터가 입력된 것을 확인할 수 있다.

이름을 박문수로 바꿔보자

import MySQLdb

# MySQL Connection 연결
config = {
	'host': '127.0.0.1',
	'user': 'root',
	'password': '123456',
	'database': 'sakila',
	'charset': 'utf8',
	'use_unicode': True
	}
try:
	conn = MySQLdb.connect(**config)
	print('데이터베이스가 연결되었습니다.')
	
	# Connection으로부터 Cursor 생성
	cursor = conn.cursor()
	
	# SQL문 실행
	# sql_insert = ("insert into actor2 values (1, '길동', '홍', now())")
	sql_update = ("update actor2 set first_name = '문수', last_name='박' where actor_id = 1")
	cursor.execute(sql_update)
	
	# commit을 해주어 transaction을 해준다.
	conn.commit()
	
	print('처리가 완료되었습니다.')
	
except MySQLdb.Error as err :
	print('오류 : ', err)
else:
	# Connection 닫기
	conn.close()
	print('데이터베이스에 연결이 종료되었습니다.')

mysql> select * from actor2;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        1 | 문수       | 박        | 2018-09-18 11:00:54 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)

UPDATE 구문을 이용하여 박문수로 수정하였다.
"update actor2 set first_name = '문수', last_name='박' where actor_id = 1"

PreparedStatement처럼 해보자

import MySQLdb

# MySQL Connection 연결
config = {
	'host': '127.0.0.1',
	'user': 'root',
	'password': '123456',
	'database': 'sakila',
	'charset': 'utf8',
	'use_unicode': True
	}
try:
	conn = MySQLdb.connect(**config)
	print('데이터베이스가 연결되었습니다.')
	
	# Connection으로부터 Cursor 생성
	cursor = conn.cursor()
	
	# SQL문 실행
	sql_insert = ('insert into actor2 values (%s, %s, %s, now())')
	sql_data = ('2', '길동', '홍')
	
	cursor.execute(sql_insert, sql_data)
	
	# commit을 해주어 transaction을 해준다.
	conn.commit()
	
	print('처리가 완료되었습니다.')
	
except MySQLdb.Error as err :
	print('오류 : ', err)
else:
	# Connection 닫기
	conn.close()
	print('데이터베이스에 연결이 종료되었습니다.')

mysql> select * from actor2;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        1 | 문수       | 박        | 2018-09-18 11:00:54 |
|        2 | 길동       | 홍        | 2018-09-18 11:07:26 |
+----------+------------+-----------+---------------------+
2 rows in set (0.00 sec)

2번 홍길동이 추가 되었다.
sql_insert = ('insert into actor2 values (%s, %s, %s, now())') 
sql_data = ('2', '길동', '홍')
위와 같이 작성하면 자바에서 ?로 선언하고 여기에 원하는 값을 집어넣었던 것처럼 활용할 수 있다.

SELECT를 이용하여 데이터베이스 값을 가져와 보자

import MySQLdb

# MySQL Connection 연결
config = {
	'host': '127.0.0.1',
	'user': 'root',
	'password': '123456',
	'database': 'sakila',
	'charset': 'utf8',
	'use_unicode': True
	}
try:
	conn = MySQLdb.connect(**config)
	print('데이터베이스가 연결되었습니다.')
	
	# Connection으로부터 Cursor 생성
	cursor = conn.cursor()
	
	# SQL문 실행
	sql_select = ('select * from actor limit 0, 10')
	
	cursor.execute(sql_select)
	
	for(actor_id, first_name, last_name, last_update) in cursor:
		print('{0} / {1} / {2}'.format(actor_id, first_name, last_name))
	
except MySQLdb.Error as err :
	print('오류 : ', err)
else:
	# Connection 닫기
	conn.close()
	print('데이터베이스에 연결이 종료되었습니다.')

 

import MySQLdb

# MySQL Connection 연결
config = {
	'host': '127.0.0.1',
	'user': 'root',
	'password': '123456',
	'database': 'sakila',
	'charset': 'utf8',
	'use_unicode': True
	}
try:
	conn = MySQLdb.connect(**config)
	print('데이터베이스가 연결되었습니다.')
	
	# Connection으로부터 Cursor 생성
	cursor = conn.cursor()
	
	# SQL문 실행
	sql_select = ('select * from actor limit 0, 10')
	
	cursor.execute(sql_select)
	
# 	for(actor_id, first_name, last_name, last_update) in cursor:
# 		print('{0} / {1} / {2}'.format(actor_id, first_name, last_name))

	for record in cursor.fetchall():
		print('%s / %s / %s / %s' % record)
	
except MySQLdb.Error as err :
	print('오류 : ', err)
else:
	# Connection 닫기
	conn.close()
	print('데이터베이스에 연결이 종료되었습니다.')

import MySQLdb

# MySQL Connection 연결
config = {
	'host': '127.0.0.1',
	'user': 'root',
	'password': '123456',
	'database': 'sakila',
	'charset': 'utf8',
	'use_unicode': True
	}
try:
	conn = MySQLdb.connect(**config)
	print('데이터베이스가 연결되었습니다.')
	
	# Connection으로부터 Cursor 생성
	cursor = conn.cursor()
	
	# SQL문 실행
	sql_select = ('select * from actor limit 0, 10')
	
	cursor.execute(sql_select)
	
# 	for(actor_id, first_name, last_name, last_update) in cursor:
# 		print('{0} / {1} / {2}'.format(actor_id, first_name, last_name))

# 	for record in cursor.fetchall():
# 		print('%s / %s / %s / %s' % record)

	for r in cursor:
		print(r[0], r[1], r[2], r[3])
	
except MySQLdb.Error as err :
	print('오류 : ', err)
else:
	# Connection 닫기
	conn.close()
	print('데이터베이스에 연결이 종료되었습니다.')

import MySQLdb

# MySQL Connection 연결
config = {
	'host': '127.0.0.1',
	'user': 'root',
	'password': '123456',
	'database': 'sakila',
	'charset': 'utf8',
	'use_unicode': True
	}
try:
	conn = MySQLdb.connect(**config)
	print('데이터베이스가 연결되었습니다.')
	
	# Connection으로부터 Cursor 생성
	cursor = conn.cursor()
	
	# SQL문 실행
# 	sql_select = ('select * from actor limit 0, 10')
	sql_select = ("select * from actor where first_name like 'S%'")
# 	sql_data = ('S%')
	
	cursor.execute(sql_select)
	
# 	for(actor_id, first_name, last_name, last_update) in cursor:
# 		print('{0} / {1} / {2}'.format(actor_id, first_name, last_name))

	for record in cursor.fetchall():
		print('%s / %s / %s / %s' % record)

# 	for r in cursor:
# 		print(r[0], r[1], r[2], r[3])
	
except MySQLdb.Error as err :
	print('오류 : ', err)
else:
	# Connection 닫기
	conn.close()
	print('데이터베이스에 연결이 종료되었습니다.')

파일에서 읽어서 DB안에 넣는 작업 -> zipcode 데이터를 MySQL 테이블에 넣어보자.

create database sample;

CREATE TABLE zipcode (
	zipcode CHAR(7) NOT NULL,
	sido VARCHAR(4) NOT NULL,
	gugun VARCHAR(17),
	dong VARCHAR(26) NOT NULL,
	ri VARCHAR(45),
	bunji VARCHAR(17) NOT NULL,
	seq INT(5) UNSIGNED NOT NULL PRIMARY KEY
)

sample database를 사용해서 zipcode를 위와 같이 만들어 주자.

'''
Created on 2018. 9. 18.

@author: kitcoop
'''
import MySQLdb
from _csv import reader
import csv

# MySQL Connection 연결
config = {
	'host': '127.0.0.1',
	'user': 'root',
	'password': '123456',
	'database': 'sample',
	'charset': 'utf8',
	'use_unicode': True
	}

try:
	# zipcode_seoul_utf8_type2.csv 읽어오기
	f = open('C:/Python/zipcode_seoul_utf8_type2.csv', 'r', encoding='utf-8')
	rdr = csv.reader(f)
	
	conn = MySQLdb.connect(**config)
	print('데이터베이스가 연결되었습니다.')
	
	# Connection으로부터 Cursor 생성
	cursor = conn.cursor()

	# next(csv_reader)
	for line in rdr:
		#print(line)
		sql_insert = ("insert into zipcode values (%s, %s, %s, %s, %s, %s, %s)")
		
# 			zipcode = line[0]
# 			sido = line[1]
# 			gugun = line[2]
# 			dong = line[3]
# 			ri = line[4] if None == "" else line[4]
# 			bunji = line[5] if None == "" else line[5]
# 			seq = line[6]
		
		sql_data = (line[0], line[1], line[2], line[3], line[4], line[5], line[6])
		
		cursor.execute(sql_insert, sql_data)
	
	conn.commit()
	f.close()
	print('데이터베이스 처리가 완료되었습니다.')
		#csv_line = line.readline()
		#print(csv_line)
		#while line != "":
# 			a1 = line[0]
# 			print(a1)
# 				zipcode = line[0]
# 				sido = line[1]
# 				gugun = line[2]
# 				dong = line[3]
# 				ri = line[4] if None == "" else line[4]
# 				bunji = line[5] if None == "" else line[5]
# 				seq = line[6]
	
except MySQLdb.Error as err :
	print('오류 : ', err)
else:
	# Connection 닫기
	conn.close()
	print('데이터베이스에 연결이 종료되었습니다.')