84일차 2023-07-06
2023. 7. 6. 19:15ㆍPython
PYTHON
Database10폴더 생성
sqlite개념
[데이타베이스 연동하기]
1.SQLite
- 파이썬 설치시 기본적으로 설치되는 DBMS(Database Management System )이다
64비트인 경우 위치는
C:\Users\<사용자이름>\AppData\Local\Programs\Python\Python36\Lib\sqlite3
- DBMS는 데이터를 저장하거나 조회할 수 있도록 도와주는 시스템 또는 소프트웨어이다.
- SQLite는 별도의 DB 서버 필요 없이 파일에 기초하여 데이터베이스 처리를 구현한
Embedded SQL DB 엔진으로 SQLite는 별도의 복잡한 서버 설치가 필요 없고,
쉽고 편리하게 사용할 수 있는 경량(Lite)의 관계형 데이타베이스이다
- SQLite공식 사이트:http://www.sqlite.org
- SQLite GUI용 Tool: DB Browser for SQLite(http://sqlitebrowser.org/)
-SQLIte 기본
테이블의 생성]
CREATE [TEMP] TABLE <테이블명> (Column정의, [테이블제약]);
-TEMP(또는 TEMPORARY) 키워드를 지정하면 임시 테이블을 작성할 수 있다.
-이 테이블은 데이터베이스를 close했을 때에 자동적으로 삭제된다.
SQLite에서 지원하는 컬럼 타입]
※데이타 타입은 참조용 일뿐이다.
레코드를 입력할때 어떤 타입의 레코드 뿐만 아니라 자료형 크기보다 큰 데이타를
입력해도 오류가 나지 않는다.
왜냐하면 SQLite는 컬럼별로 입력되는 데이타 타입을 다르게 할 수 있다.
text- 문자열
integer - 정수
real - 부동소수
boolean - true 또는 false
datetime - 날짜
※위의 5가지 외에 데이터 타입을 사용하더라도 내부적으로 위의 5가지 데이터 타입으로 변경된다
테이블의 작성 예]
Create table Member(
_id integer primary key autoincrement,
name varchar(10) not null,
tel char(12) not null default 'unknown',
joindate datetime default CURRENT_TIMESTAMP,
/*unique (name, tel),*/
check(length(tel)>=3)
)
_id: primary key라는 걸 알려주기 위해 일반적으로 _id형태로 표시
테이블 삭제]
drop table 테이블명;
테이블 수정]
이름변경-alter table 테이블명 rename to 새로운 테이블명;
column추가-alter table 테이블명 add column 컬럼 정의;
데이타 입력/조회 /수정/삭제]
표준 SQL문 사용.
단,문자열은 ' 나 "으로 감싸면 된다.
또한 데이타 삭제시에는 반드시 delete 다음에 from을 추가 해야 한다.
SQLite의 내장 함수]
min(X,Y,…,)-최소값을 반환
max(X,Y,…,)-최대값을 반환
typeof(X)-데이터의 형을 반환
length(X)-문자열의 길이를 반환
substr(X,Y,Z)-X의 문자열의 Y번째에서 Z문자의 길이의 문자열을 반환
abs(X)-절대값을 반환
round(X,Y)-실수 X를 소수점 Y+1자리에서 반올림
upper(X)-대문자로 변환
lower(X)-소문자로 변환
coalesce(X,Y,…,)-최초의 NULL이 아닌 값을 반환
hex(X)-문자열을 16진수 표기로 반환
random()-Radom한 값을 반환
nullif(X,Y)-2개의 값이 다르면 최초의 값을 반환. 같다면 NULL을 반환
sqlite_version()-SQLite의 버전을 반환
last_insert_rowid()-마지막에 삽입한 레코드의 ID를 반환
sum(X,Y,…,)-NULL이 아닌 행의 값의 합계를 반환.행이 없는 경우 NULL을 반환
total(X,Y,…,)-NULL이 아닌 행의 값의 합계를 반환.행이 없는 경우 0를 반환.
avg(X,Y,…,)-NULL이 아닌 행의 값의 평균을 반환.행이 없는 경우 NULL을 반환
count(X)-그륩 안에서 X가 NULL이 아닌 행의 행수를 반환합니다.
count(*)-그룹 별의 행수를 반환합니다.
datetime():
현재시간 얻기
GMT기준으로 시간 표시.우리나라는 GMT보다 9시간 빠름으로
select datetime('now','+9 hours');
예] 년월일 시 분 추가시
select datetime('now','+9 hours','+1 minute','+1 year','+1 month','+1 day')
-파이썬의 데이타베이스 관련 주요 함수
connect() : 데이터베이스에 연결하는 함수로 Connection객체 반환.
계정명, 비밀번호, 서버 주소 등의 인자를 포함.
cursor() : Connection객체의 메소드로 데이터를 저장하고 조회할 수 있는 즉 SQL질의를 수행 할 수 있는 Cursor 객체 반환
execute(), executemany() : Cursor 객체의 메소드로 데이터베이스에 하나 이상의 SQL 명령을 실행하는 함수.
fetchone(), fetchmany(조회할 개수), fetchall(): Cursor 객체의 메소드로 execute()계열 함수에 의해 실행된 결과를 가져오는 함수.
fetchone()는 하나의 데이터를 튜플로 반환.
fetchmany(일정 개수)는 일정 개수의 데이터를 리스트로 반환.
fetchall()은 모든 데이터를 리스트로 반환.
또한
fetchone()은 데이터가 없을 경우 None을 반환.
fetchmany()과 fetchall()는 데이터가 없을 경우 []을 반환한다.
-파이썬에서 SQLite 사용하기
STEP 0. DB Browser for SQLite를 시용하여 sample.db를 만들고
위의 테이블 생성 쿼리문을 복사하여 Member테이블을 만들자
STEP 1. sqlite3 모듈을 import
import sqlite3
STEP 2. connect 함수로 데이타베이스 파일를 생성하고 Connection 객체를 생성.즉 SQLite DB에 연결
conn = sqlite3.connect('./sample.db')#현재 디렉토리 데이타베이스 파일 생성 혹은 오픈
-sample.db라는 데이타베이스 파일을 생성, 파일이 없으면 새로 만들고 파일이 있으면 기존 데이터베이스 파일을 로드한다.
또한, ':memory:'를 파일명으로 사용하면 데이타베이스를 메모리에 생성한다.
STEP 3. Connection 객체의 cursor() 메서드로 Cursor 객체 얻거나 execute()메소드로 얻을 수도 있다.
cur = conn.cursor()
혹은
cur=conn.execute('쿼리문')
STEP 4. Cursor 객체의 execute()계열 메소드로 SQL 쿼리 실행
cur.execute("Parameterized 쿼리문")
혹은
cur.execute("Parameterized 쿼리문",튜플 혹은 리스트)
위의 경우 쿼리문에 데이터를 넣는 경우에 데이타가 들어갈 자리에 '?'(Parameter Placeholder)
나 혹은 :파라미터명(Named Placeholder) 를 넣고 나중에 실행할 때 데이터를 넣는 방식이다(동적 쿼리).
데이터는 반드시 튜플 또는 리스트 타입 형태의 데이터여야 하며,
execute는 하나의 레코드 데이터만 들어가는데, executemany를 이용하면 다수의 레코드
데이터를 입력할 수 있다.
※Named Placeholder방식에서 파라미터명은 임의의 이름.보통 컬럼명을 사용한다
예]
sql = '''
INSERT INTO 테이블명 VALUES
(?, ?, ?)
'''
호은
sql = '''
INSERT INTO 테이블명 VALUES
(:컬럼명1, :컬럼명2, :컬럼명3)
'''
cur = conn.cursor()
cur.execute(sql, ('데이타1', 숫자데이타2, '데이타3'))
data = [
('데이타1', 숫자데이타2, '데이타3'))),
('데이타4', 숫자데이타5, '데이타6')),
('데이타7', 숫자데이타8, '데이타9')),
]
cur.executemany(sql, data)
conn.commit()
STEP 5. 쿼리문이 SELECT 인 경우 fetch계열 메소드로 데이터 Fetch
fetchall():모든 레코드를 한꺼번에 읽어올 때 사용
fetchone():한번 호출에 하나의 레코드 만을 가져올 때 사용
fetchone()을 여러 번 호출하여, 호출 할 때 마다 한 레코드 씩
가져 올 수 있다
fetchmany(n):n개 만큼의 데이타를 한꺼번에 읽어올 때 사용
쿼리문이 Insert, Update, Delete 등의 DML(Data Manipulation Language) 문장인 경우
Connection 객체의 commit()메소드로 실제 데이타베이스 파일에 반영
취소시는 Connection 객체의 rollback()메소드 호출
STEP 6. Cusor객체 및 Connection 객체의 close() 메서드로 닫기
cur.close()
conn.close()
※SQLite버전번호 확인
>>>import sqlite3
>>>sqlite3.version
sqlite1.py
#1. sqlite3 패키지를 import
import sqlite3
print(sqlite3.__file__)
print(dir(sqlite3))
print(sqlite3.sqlite_version)
try:
# 2. SQLite DB에 연결. 데이타베이스 파일은 현재 디렉토리에서 오픈 혹은 생성
conn=sqlite3.connect('sample.db')
print(f'value:{conn},type:{type(conn)}')#<class 'sqlite3.Connection'>
# 방법1:커넥션객체.cursor()로 Cursor객체 생성후 쿼리 실행
# 3.커넥션 객체의 cursor()메소드로 Cursor 객체생성
#cursor=conn.cursor()
#print(f'value:{cursor},type:{type(cursor)}') # <class 'sqlite3.Cursor'>
# 4.Cursor객체의 execute() 명령으로 SQL쿼리를 실행(SELECT/INSERT/DELETE/UPDATE등)
#cursor.execute('SELECT * FROM member ORDER BY _id DESC')
#방법2:커서객체=커넥션객체.execute()로 바로 쿼리 실행
cursor=conn.execute('SELECT * FROM member ORDER BY _id DESC')
# 5.데이타 Fetch(SQL문이 SELECT일때)
# 테이블의 쿼리결과는 [(),(),(),..] 형태로 반환(리스트)된다. 즉 하나의 레코드는 튜플로 만들어진다
rows=cursor.fetchall()
print(f'value:{rows},type:{type(rows)}')
for _id,name,tel,joindate in rows:
print(f'번호:{_id},이름:{name},연락처:{tel},가입일:{joindate[0:10]}')
except Exception as e:
print(e)
finally:
# Cusor객체 및 Connection 객체의 close() 메서드로 닫기
# ※생성한 순서 반대로 닫는다
if cursor:
cursor.close()
if conn:
conn.close()결과)
sqlite2.py
#1. sqlite3 패키지를 import
import sqlite3
import time
try:
# 2. SQLite DB에 연결. 데이타베이스 파일은 현재 디렉토리에서 오픈 혹은 생성
conn=sqlite3.connect('sample.db')
# 3.커넥션 객체의 cursor()메소드로 Cursor 객체생성
cursor=conn.cursor()
# 4.Cursor객체의 execute() 명령으로 SQL쿼리를 실행(SELECT/INSERT/DELETE/UPDATE등)
# 방법1:Parameter Placeholder방식
#cursor.execute('INSERT INTO member(name,tel,joindate) VALUES(?,?,datetime("now","localtime"))',['이길동','010-1111-2222'])
# 방법2:Named Placeholder ?대신 ":컬럼명"으로
joindate = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime())
cursor.execute('INSERT INTO member(name,tel,joindate) VALUES(:name,:tel,:jondate)',
('라길동', '010-222-333',joindate))
# 5. commit:실제 테이블에 반영(쿼리문이 Insert/Delete/Update일때)
conn.commit()
print(f'{cursor.rowcount}행이 입력되었어요')
except Exception as e:
conn.rollback()
print(e)
finally:
# Cusor객체 및 Connection 객체의 close() 메서드로 닫기
# ※생성한 순서 반대로 닫는다
if cursor:
cursor.close()
if conn:
conn.close()결과)
sqlite3.py
#v파일명은 반드시 sqlite3_.py
#1. sqlite3 패키지를 import
import sqlite3
try:
# 2. SQLite DB에 연결. 데이타베이스 파일은 현재 디렉토리에서 오픈 혹은 생성
conn=sqlite3.connect('sample.db')
print('conn:',conn)
# 3.커넥션 객체의 cursor()메소드로 Cursor 객체생성
cursor=conn.cursor()
print('cursor:',cursor)
# 4.Cursor객체의 execute() 명령으로 SQL쿼리를 실행(SELECT/INSERT/DELETE/UPDATE등)
#여러행 한번에 입력하기
records=[('코스모1','010-1111-1111'),('코스모2','010-1111-1112'),('코스모3','010-1111-1113')]
'''
for record in records:
cursor.execute("INSERT INTO member(name,tel,joindate) VALUES(?,?,strftime('%Y-%m-%d %H:%M:%S','now','localtime'))",record)
'''
# 여러개의 레코드 입력시
cursor.executemany("INSERT INTO member(name,tel,joindate) VALUES(?,?,strftime('%Y-%m-%d %H:%M:%S','now','localtime'))",records)
# 5. commit:실제 테이블에 반영(쿼리문이 Insert/Delete/Update일때)
conn.commit()
print(f'{cursor.rowcount}행이 입력되었어요')
except Exception as e:
conn.rollback()
print(e)
finally:
# Cusor객체 및 Connection 객체의 close() 메서드로 닫기
# ※생성한 순서 반대로 닫는다
if cursor:
cursor.close()
if conn:
conn.close()
결과)
sqlite4.py
#1. sqlite3 패키지를 import
import sqlite3
try:
# 2. SQLite DB에 연결. 데이타베이스 파일은 현재 디렉토리에서 오픈 혹은 생성
conn=sqlite3.connect('sample.db')
# 3.커넥션 객체의 cursor()메소드로 Cursor 객체생성
cursor=conn.cursor()
# 4.Cursor객체의 execute() 명령으로 SQL쿼리를 실행(SELECT/INSERT/DELETE/UPDATE등)
# 데이타 Fetch:쿼리문이 SELECT일때
# 방법1:fetchall()-리스트 반환 [(),(),()....] 튜플이() 하나의 레코드에 해당
'''
cursor.execute("SELECT * FROM member ORDER BY _id DESC")
rows=cursor.fetchall()
print(rows)
for row in rows:
print(f'{row[0]} {row[1]} {row[2]} {row[3][0:10]}')
'''
# 방법2:fetchone()
'''
_id = int(input('번호를 입력하세요?'))
cursor.execute("SELECT * FROM member WHERE _id=?",[_id])
row = cursor.fetchone()#하나의 레코드를 튜플로 반환:(컬럼1,컬럼2,...)
print(row)#없는 번호인 경우 None
if row:
print(f'{row[0]} {row[1]} {row[2]} {row[3][0:10]}')
else:
print(_id,'로 조회한 레코드가 없습니다',sep='번으')
'''
# 방법3:fetchmany(가져올 레코드 수) 갯수 미 지정시 디폴트는 1개
cursor.execute("SELECT * FROM member ORDER BY _id DESC")
rowcount = int(input('조회할 레코드 수를 입력하세요?'))
rows = cursor.fetchmany(rowcount)#0일때는 전체(fetchmany메서드의 특성)
for row in rows:
print(f'{row[0]} {row[1]} {row[2]} {row[3][0:10]}')
except Exception as e:
print(e)
finally:
# Cusor객체 및 Connection 객체의 close() 메서드로 닫기
# ※생성한 순서 반대로 닫는다
if cursor:
cursor.close()
if conn:
conn.close()
결과)
mysql 개념
2. MySql
-MySql은 오라클사에서 제공하는 오픈 소스 RDBMS로 무료이다
-MySql은 불확실한 라이선스 정책으로 인해 동일 소스를 기반으로 MariaDB 가 파생되었다
그래서 MariaDB는 오픈소스 라이센스 정책을 따르고있다
2-1. MySql 설치
-Windows 계열
1. http://dev.mysql.com/downloads로 이동
2. MySQL Community Server 항목에서 DOWNLOAD 항목 클릭
3. MySQL Installwe for Windows 의 Go To Download Page클릭
4. Windows(x86, 32-bit), MSI Installer((mysql-installer-community-8.0.11.0.msi))를 다운
. Begin Your Download 화면에서 No thanks, just take me to the Downloads! 클릭
MySql삭제
1.제어판에서 MySQL관련 프로그램 삭제된다
2.C:\ProgramData\MySQL의 MySQL폴더 삭제
3.C:\Program Files\MySQL의 MySQL폴더 삭제
-리눅스 계열
1. sudo apt-get update
2. sudo apt-get install mysql-server
-만약 vim 명령이 없다면, 다음과 같이 vim 설치
-sudo apt-get install vim
3. sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
- 다음 설정을 [mysqld] 에 추가
-mysql 원격 접속 허용
bind-address 0.0.0.0 로 변경()
-mysql 한글 설정 추가
collation-server = utf8_unicode_ci
character-set-server = utf8
skip-character-set-client-handshake
4. sudo service mysql start (mysql 서비스 시작)
2-2. MySql 접속하기
1. 명령 프롬프트에서 MySql 설치 드라이브로 이동(C:\Program Files\MySQL\MySQL Server 버전번호\bin)
2. mysql -u root -p 입력하여 root 계정으로 MySql에 접속
-mysql [-h 호스트명] -u 계정명 -p [비밀번호] [데이타베이스명]
[-h 호스트명]: mysql 서버 주소 (-h 옵션 사용 안하면, 현재 PC)
[데이타베이스명] : 해당 데이타베이스로 바로 접속하는 명령어.
접속후 use 데이타베이스명; 명령어로 해당 데이타베이스 사용가능
2-3. MySql기본 명령어 및 사용절차
-show databases; : 데이터베이스 목록 보기
-create database 데이타베이스명; : 데이타베이스 생성
-drop database [if exists] 데이타베이스명; : 데이타베이스 삭제
[if exists] 옵션은 해당 데이타베이스명이 없더라도 오류를 발생시키지 말라는 의미
-use 데이타베이스명; : 데이타베이스 사용
-show tables; : 테이블 목록 확인
-create table if not exists 테이블명
(컬럼명1 자료형1,
컬럼명2 자료형2,
컬럼명3 자료형3,
...
primary key(컬러명1,컬럼명2,..)
); : 테이블 생성
※ MySql에서 지원하는 컬럼 타입
-숫자형
정수형:
tinyint : 1바이트로 -128 ~ 128 혹은 unsigned옵션 추가시 0~255
smallint : 2바이트로 -32768~32767 또는 unsigned옵션 추가시 0 ~65536
mediumint: 3바이트로 -8388608~8388607 또는 unsigned옵션 추가시0 ~16777215
int : 4바이트로 -2147483648~2147483647 또는 unsigned옵션 추가시0 ~4294967295
bigint : 8바이트로 무제한수 표현가능
실수형:
float(정수부길이,소수부 자릿수) : 4바이트.부동 소수점 데이타
double(정수부길이,소수부 자릿수) : 8바이트.부동 소수점 데이타
decimal(정수부길이,소수부 자릿수) : 고정 소수점 데이타(길이+1바이트)
-문자형
char(n) : 고정길이 데이타 (n <=255)
varchar(n) : 가변길이 데이타 (n <=66535)
tinytext(n) : 문자열 데이타(n <=255)
text(n) : 문자열 데이타(n <=65535)
mediumtext(n) : 문자열 데이타(n <=16777215)
longtext(n) : 문자열 데이타(n <=4294967295)
-날짜및시간
date : 날짜(YYYY-MM-DD)형태의 데이타(3바이트)
time : 시간(hh:mm:ss)형태의 데이타(3바이트)
datetime : 날짜외 시간형태(YYYY-MM-DD hh:mm:ss) 의 데이타
timestamp : 1970-01-01 00:00:00 이후부터 시스템 현재 시간까지의
지난시간 초로 환산하여 숫자로 표현
테이블의 작성 예]
Create table Member(
_id int primary key auto_increment,
name varchar(10) not null,
tel char(12) not null default 'unknown',
unique (name, tel),
check(length(tel)>=3)
);
-desc 테이블명; : 테이블 스키마 확인]
-create user '사용자명'@'localhost' identified by '비밀번호'; : 사용자계정 생성
-grant all privileges on 데이타베이스명.* TO '사용자명'@'localhost'; : 권한 부여
1.로컬에서만 접속 허용시
grant all privileges 데이타베이스명.테이블명 to '사용자명'@'localhost' ;
2.특정 호스트에만 접속 허용시
grant all privileges 데이타베이스명.테이블명 to '사용자명'@'특정호스트(아이피 혹은 도메인' ;
3.모든 호스트에서 접속 허용시
grant all privileges 데이타베이스명.테이블명 to '사용자명'@'%' ;
옵션 상세
all – 모든 권한 / SELECT, UPDATE – 조회, 수정 권한등으로 권한 제한 가능
예] grant insert,update,select on *.* to '사용자명'@'localhost' ;
데이타베이스명.테이블명 – 특정 데이터베이스에 특정 테이블에만 권한부여. *.*는 모든 데이터베이스에 모든 테이블 권한을 부여
예]
grant all privileges on sampledb.* to 'sample'@'localhost'
- flush privileges; : 권한설정 적용
- exit; : 데이타베이스 연결 종료
2-4. MySql 쿼리 명령어
-테이블 삭제
drop table [if exists] 테이블명;
-테이블에 새로운 컬럼 추가
alter table 테이블명 add column [추가할 컬럼명] [추가할 컬럼 데이터형]
-테이블 컬럼 타입 변경
alter table 테이블명 modify column [변경할 컬럼명][변경할 컬럼 타입]
-테이블 컬럼 이름 변경
alter table 테이블명 change column [기존 컬럼 명][변경할 컬럼 명][변경할 컬럼 타입]
-테이블 컬럼 삭제
alter table 테이블명 drop column 삭제할 컬럼 명
-데이타 입력
-테이블 모든 컬럼에 대응하는 값을 모두 넣을때
insert into 테이블명 values(값1, 값2, ...); :
-테이블 특정 컬럼에 대응하는 값만 넣을때
insert into 테이블명 (컬러명1, 컬러명2, ...) values(값1, 값2, ...);
-데이타 검색
-테이블 모든 컬럼의 데이터 읽기
SELECT * FROM 테이블명;
-테이블 특정 컬럼의 데이터만 읽기
select 컬럼명1, 컬럼명2, ... from 테이블명;
-테이블 특정 컬럼의 데이터조횟 표시할 컬럼명에 별칭 부여하기
select 컬럼명1 as 바꿀컬럼명 FROM 테이블명;
-데이타 정렬하기
order by 정렬할 기준 컬럼명 desc|asc
descC는 내림차순 asc는 오름차순
select * from 테이블명 order by 정렬할기준컬럼명 desc;
select * from 테이블명 order by 정렬할기준컬럼명 asc;
-특정 조건에 맞는 데이터만 검색하기
비교연산자 ,논리연산자(and ,or), LIKE연산자를 사용해 where 조건문 으로 조건 검색
select * from 테이블명 where 컬럼명 = '값'
select * from 테이블명 where (컬럼명='값') or ( 컬럼명 ='값');
select * from 테이블명 where (컬럼명='값') and ( 컬럼명 ='값');
-찾을문자가 포함된 모든 레코드
select * from 테이블명 where 컬럼명 like '%찾을문자%';
-찾을문자로 시작하는 모든 레코드
select * from 테이블명 where 컬럼명 like '찾을문자%';
-찾을문자로 끝나는 모든 레코드
select * from 테이블명 where 컬럼명 like '%찾을문자';
※_(언더바는 문자의 자리수를의미)
예]찾을문자가 두번째에 들어 있는 레코드 검색시
select * from 테이블명 where 컬럼명 like '_찾을문자%';
-조회된 레코드에서 일부만 가져오기
-찾은 결과중 지정한 갯수만큼 레코드를 가져올 경우
select * from 테이블명 order by 컬럼명 limit 가져올갯수;
-찾은 결과중 가져올 순서번째부터 지정한 갯수만큼 레코드를 가져올 경우
select * from 테이블명 order by 컬럼명 limit 가져올순서,가져올갯수;
-데이타 수정
update 테이블명 set 컬럼명1 = '값1',컬럼명2 = '값2' where 수정조건;
-데이타 삭제
delete from 테이블명 where 삭제조건;
-사용자 계정 추가 및 삭제
1.mysql -u root -p : root로 접속
2.use mysql : mysql데이타베이스 사용
3.select * from user; : 사용자 확인
4.create user '아이디'@'localhost' identified by '비밀번호'; : 로컬에서만 접속 가능한 userid 생성
5.ALTER USER '아이디'@'localhost' identified by '신규비밀번호'; : 사용자 비밀번호 변경
6.drop user '아이디'@'localhost' : 사용자 삭제파이썬에서 mysql 사용하기
2-4. 파이썬에서 MySql사용하기
- 파이썬은 기본적으로 많은 내장 라이브러리들을 지원하고 있지만 MySql을 지원하지 않는다
그래서 사용자가 직접 라이브러리를 설치해줘야 한다.
- 파이썬에서 사용할 수 있는 라이브러리는 pymysql, MySQLdb(Mysql-pytion), MySQL connector 등 다양한 라이브러리들 있다
- 파이썬이 설치된 폴더(C:\Users\kosmo6_01\AppData\Local\Programs\Python\Python36-32\) 하위의 Scripts 폴더로 이동후 패키지 설치 명령인 pip 명령을 사용하여 pymysql을 설치
pip install PyMySQL
※수동으로 설치하려면 https://pypi.org/project/PyMySQL/ 에 접속하여 PyMySQL-0.8.1-py2.py3-none-any.whl파일을 다운후
whl 파일이 있는 곳으로 이동후 pip install [파일.whl] 하면 설치가 된다.
- 설치확인
>>>import pymysql
>>>pymysql
- MySql 연동 순서
STEP1. PyMySql 모듈 import
import pymysql
STEP2. pymysql.connect() 메소드를 사용하여 MySql에 연결
-호스트명, 포트, 접속계정정보, 접속할 데이타베이스 등을 파라미터로 지정
-주요 파라미터
host : 접속할 mysql server 주소
port : 접속할 mysql server 의 포트 번호
user : MySql 아이디
password : MySql 비밀번호
db : 접속할 데이터베이스
charset='utf8' : MySql에서 select하여 데이타를 가져올 때 한글이 깨질 수 있으므로 연결 설정에 넣어준다
conn = pymysql.connect(
host='localhost',
user='아이디',
password='비밀번호',
db='데이타베이스명',
charset='utf8',cursorclass=pymysql.cursors.DictCursor)
※cursorclass=pymysql.cursors.DictCursor는 딕셔너리 형태로 출력되고
이 부분을 생략하면 결과가 튜플 형태로 출력된다.
STEP3. MySql 접속이 성공하면, Connection 객체로부터 cursor() 메서드를 호출하여 Cursor 객체를 가져옴
cur = conn.cursor()
STEP 4. Cursor 객체의 execute()계열 메소드로 SQL 쿼리 실행
cur.execute("쿼리문")
혹은
cur.execute("쿼리문",튜플 혹은 리스트)
위의 경우 쿼리문에 데이터를 넣는 경우에 데이타가 들어갈 자리에 '%s'를
넣고 나중에 실행할 때 데이터를 넣는 방식이다.
데이터는 반드시 듀플 또는 리스트 타입 형태의 데이터여야 하며,
execute는 하나의 데이터만 들어가는데, executemany를 이용하면 다수의 데이터를 입력할 수 있다.
예]
sql = '''
INSERT INTO 테이블명 VALUES
(%s, %s, %s)
'''
cur = conn.cursor()
cur.execute(sql, ('데이타1', 숫자데이타2, '데이타3'))
data = [
('데이타1', 숫자데이타2, '데이타3'))),
('데이타4', 숫자데이타5, '데이타6')),
('데이타7', 숫자데이타8, '데이타9')),
]
cur.executemany(sql, data)
conn.commit()
STEP 5. 쿼리문이 SELECT 인 경우 fetch계열 메소드로 데이터 Fetch
fetchall():모든 레코드를 한꺼번에 읽어올 때 사용
fetchone():한번 호출에 하나의 레코드 만을 가져올 때 사용
fetchone()을 여러 번 호출하여, 호출 할 때 마다 한 레코드 씩
가져 올 수 있다
fetchmany(n):n개 만큼의 데이타를 한꺼번에 읽어올 때 사용
쿼리문이 Insert, Update, Delete 등의 DML(Data Manipulation Language) 문장인 경우
Connection 객체의 commit()메소드로 실제 데이타베이스 파일에 반영
취소시는 Connection 객체의 rollback()메소드 호출
STEP 6. Cusor객체 및 Connection 객체의 close() 메서드로 DB연결 닫기
cur.close()
conn.close()
2. Oracle
STEP1.cx_Oracle로 검색
https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html
STEP2.
https://www.oracle.com/technetwork/database/database-technologies/instant-client/overview/index.html
Oracle Instant Client설치 ,단 오라클 설치된 경우 불필요
STEP3.
C:\Users\KSM06-16\Downloads\instantclient-basic-windows.x64-18.5.0.0.0dbru\instantclient_18_5 Path등록
STEP4.
C:\Users\KSM06-16\AppData\Local\Programs\Python\Python37\Scripts경로 Path등록
(등록하지 않은 경유에 한해)
STEP5.pip install cx_Oracle설치
※파이썬 3.11버전에 cx_Oracle 설치시
which is required to install pyproject.toml-based projects 와 같은 오류 발생
(단,파이썬 3.10 이하 버전에는 정상적으로 설치된다)
해결책:
https://visualstudio.microsoft.com/ko/visual-cpp-build-tools/ 에서
vs_BuildTools.exe 다운 후
c++를 사용한 테스트톱 개발 체크 ->설치
STEP5.
import cx_Oracle
# Connect as user "hr" with password "welcome" to the "oraclepdb" service running on this computer.
connection = cx_Oracle.connect("MAVEN", "MAVEN", "localhost:1521/orcl")#"localhost/orcl"
cursor = connection.cursor()
print('cursor',cursor)
cursor.execute("""
SELECT ID,PWD
FROM ONEMEMBER""")
for id, pwd in cursor:
print("Values:", id, pwd)
※단 파이썬 프로그램 64비트면 오라클도 64비트 여야한다
mysql1.py
'''
1. root로 로그인
C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u root -p
2. 데이타베이스 생성
mysql> create database kosmodb;
3. 사용자 생성
mysql> create user 'kosmo'@'localhost' identified by '1234';
4. 권한부여 및 적용
mysql> grant all privileges on kosmodb.* to 'kosmo'@'localhost';
mysql> flush privileges;
5. 접속끊기
mysql> exit;
'''
#1.pymysql 모듈 import
import pymysql
print(dir(pymysql))
try:
#2.pymysql.connect() 메소드를 사용하여 MySql에 연결
conn = pymysql.connect(
host='localhost',
user='kosmo',
password='1234',
db='kosmodb',
charset='utf8',#반드시 UTF8(utf8), UTF-8는 에러
# ※cursorclass=pymysql.cursors.DictCursor는 딕셔너리 형태로 출력되고
# 이 부분을 생략하면 결과가 튜플 형태로 출력된다.
cursorclass=pymysql.cursors.DictCursor
)
# 3.커넥션 객체의 cursor()메소드로 Cursor 객체생성(쿼리 실행용)
cursor=conn.cursor()
# 4. Cursor 객체의 execute() 명령으로 SQL 쿼리를 실행
cursor.execute("DROP TABLE IF EXISTS member")
cursor.execute('''
CREATE TABLE IF NOT EXISTS member(
no INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10) NOT NULL,
age TINYINT UNSIGNED DEFAULT 1,
joindate DATETIME DEFAULT NOW()
)
''')
except Exception as e:
print(e)
finally:
if cursor:
cursor.close()
if conn:
conn.close()
결과)
mysql2.py
#1.pymysql 모듈 import
import pymysql
try:
#2.pymysql.connect() 메소드를 사용하여 MySql에 연결
conn = pymysql.connect(
host='localhost',
user='kosmo',
password='kosmo1234',
db='kosmodb',
charset='utf8',
cursorclass=pymysql.cursors.DictCursor
)
# 3.커넥션 객체의 cursor()메소드로 Cursor 객체생성(쿼리 실행용)
cursor=conn.cursor()
# 4. Cursor 객체의 execute() 명령으로 SQL 쿼리를 실행
# ※Parameter Placeholder방식:%s사용 (sqlite3는 ?,pymysql는 %s)
# executemany()로 여러 레코드 입력
users=[]
persons = int(input('인원수를 입력하세요?'))
for _ in range(persons):
name = input('이름 입력?')
age = input('나이 입력?')
users.append((name,age))
cursor.executemany('INSERT INTO member(name,age) VALUES(%s,%s)',users)
conn.commit()
print(cursor.rowcount,'행이 입력되었습니다',sep='')
except Exception as e:
conn.rollback()
print(e)
finally:
if cursor:
cursor.close()
if conn:
conn.close()결과)
oracle.ini
[section]
key=value
[ORACLE]
user=RESTAPI
password=RESTAPI
url=localhost:1521/xe
[DATABASE]
engine=oracle결과)
oracle1.py
#configparser모듈을 사용한 중요 정보 관리
#섹션명([])은 대소문자 구분.
#섹션안의 키=값은 구분하지 않는다
#user = maven 혹은 user=maven 는 같다 즉 자동으로 앞뒤 공백 제거
#https://docs.python.org/ko/3/library/configparser.html
import configparser
#1. ConfigParser객체 생성
config=configparser.ConfigParser()
print(f'value:{config},type:{type(config)}')
#2.ini파일 읽기
print(config.read('oracle.ini'))#['oracle.ini']
#3. 모든 섹션명 얻기
sections=config.sections()
print(sections)#['section', 'ORACLE', 'DATABASE']
#4.items() : ItemsView[str, SectionProxy] 반환 즉 ('섹션명',SectionProxy)를 요소로 갖는 ItemsView객체 반환
items=config.items()
print(items)#ItemsView(<configparser.ConfigParser object at 0x000002BE79AA6210>)
print(list(items))#[('DEFAULT', <Section: DEFAULT>), ('section', <Section: section>), ('ORACLE', <Section: ORACLE>), ('DATABASE', <Section: DATABASE>)]
print(type(list(items)[0][1]))#<class 'configparser.SectionProxy'> 즉 <Section: DEFAULT>는 SectionProxy타입
print('SectionProxy타입의 이름공간:',dir(list(items)[0][1]))
for 섹션명,섹션프락시 in items:
print(섹션명)
if 섹션명 == 'ORACLE':
user = 섹션프락시.get('user')
password = 섹션프락시.get('password')
url = 섹션프락시.get('url')
print(f'아이디:{user},비번:{password},주소:{url}')
#ConfigParser객체['섹션명']['키값'] : 섹션명은 대소문자 구분.키값은 대소문자 구분 필요없다
user = config['ORACLE']['USER']
password = config['ORACLE']['PASSWORD']
url = config['ORACLE']['URL']
print(f'아이디:{user},비번:{password},주소:{url}')
#1.모듈 import
import cx_Oracle
#2.데이타베이스 연결
with cx_Oracle.connect(user=user, password=password,dsn=url,encoding="UTF-8") as conn:
print(f'value:{conn},type:{type(conn)}')
#3.쿼리 실행을 위한 커서객체 얻기
cursor=conn.cursor()
#4.쿼리 실행
#방법1:numbered placeholder - :번호(번호는 1부터 시작)-데이타는 tuple 혹은 list로
#cursor.execute('INSERT INTO users VALUES(:1,:2,:3,SYSDATE)',['KIM','1234','김길동'])
# 방법2:named placeholder -(데이타는 tuple/list/딕셔너리로:키값은 컬럼명으로)
#cursor.execute('INSERT INTO users VALUES(:username,:password,:name,SYSDATE)', ['LEE', '1234', '이길동'])
#cursor.execute('INSERT INTO users VALUES(:username,:password,:name,SYSDATE)', username='PARK',password='1234',name='박길동')
cursor.execute('INSERT INTO users VALUES(:username,:password,:name,SYSDATE)', {'username':'KOSMO','password':'1234','name':'박길동'})
#5.commit
conn.commit()
#6.연결해제
cursor.close()
#conn.close()결과)
oracle2.py
import configparser
#1. ConfigParser객체 생성
config=configparser.ConfigParser()
#2.ini파일 읽기
config.read('oracle.ini',encoding='utf8')#한글이 포함된 경우(encoding='utf8'추가)
#1.모듈 import
import cx_Oracle
#2.데이타베이스 연결
with cx_Oracle.connect(user=config['ORACLE']['USER'],
password=config['ORACLE']['PASSWORD'],
dsn=config['ORACLE']['URL'],
encoding="UTF-8") as conn:
#3.쿼리 실행을 위한 커서객체 얻기
cursor=conn.cursor()
#4.쿼리 실행
cursor.execute('SELECT * FROM users ORDER BY joindate DESC')
# 5.패치
rows = cursor.fetchall()
print(rows)
for user,password,name,joindate in rows:
print(f'아이디:{user},비번:{password},이름:{name},가입일:{str(joindate)[0:10]}')
#6.연결해제
cursor.close()결과)
view.py
def showMenu():
print('=' * 31)
print('1.입력 2.수정 3.삭제 4.조회 9.종료')
print('=' * 31)
return int(input('메뉴번호를 입력하세요?'))
def inputData(*args):#사용자로부터 가변적인 항목을 입력받기 위한 UI
list_=[]
for arg in args:
list_.append(input(f'{arg}을(를) 입력하세요?'))
return list_#입력받은 값을 리스트로 반환
if __name__ =='__main__':
showMenu()결과)
controller.py
from view import *
from model import *
#데이타베이스 연결
conn=connectdb()
while True:
# '1.입력 2. 수정 3. 삭제 4. 조회 9. 종료'
menu=showMenu()
print(menu)
if menu == 9:
print('프로그램을 종료합니다')
close(conn)#데이타베이스 닫기
break
elif menu == 1:
#뷰(화면) 호출
list_ = inputData(*['아이디','비번','이름'])
#print(list_)
# 모델 호출
affected=insert(conn,list_)
print(f'{str(affected)+"행이 입력되었습니다" if affected==1 else "입력시 오류발생"}')
결과)
model.py
from cx_Oracle import connect
from configparser import ConfigParser
def connectdb():#데이타베이스 연결:Connection 객체 반환
config=ConfigParser()
config.read('oracle.ini')
#데이타베이스 연결
return connect(user=config['ORACLE']['USER'],
password=config['ORACLE']['PASSWORD'],
dsn=config['ORACLE']['URL'],
encoding="UTF-8")
def close(conn):#커넥션객체 닫기
if conn:
conn.close()
#list_는 입력 데이타
def insert(conn,list_):#입력처리:성공시 1,실패시 0
with conn.cursor() as cursor:
try:
cursor.execute('INSERT INTO users VALUES(:1,:2,:3,SYSDATE)',list_)
conn.commit()
return 1
except Exception as e:
#print(e)
return 0
if __name__ =='__main__':
conn=connectdb()
print(insert(conn,['KIM','1234','최길동']),'행 입력')
'Python' 카테고리의 다른 글
| 86일차 2023-07-10 (0) | 2023.07.11 |
|---|---|
| 85일차 2023-07-07 (0) | 2023.07.07 |
| 83일차 2023-07-05 (0) | 2023.07.06 |
| 82일차 2023-07-04 (0) | 2023.07.04 |
| 81일차 2023-07-03 (0) | 2023.07.03 |