pymysql -22.06.08-22.06.09

파이썬 2022. 6. 9. 23:35

728x90

1. maria db검색해서 다운로드

https://mariadb.org/

2.heidiSQl설치

mariadb 먼저 설치하고 바탕화면에 있는 HeidiSQL먼저 삭제하고

HeidSQL설치하기

비밀번호: python

원래는 3306이지만 나중에 겹칠 수 도 있으니까 3305로 바꿔준다

+신규 누르고 127.0.0.1 하고 암호에 python 입력하고 열기 누르기(?)

포트는 3305로 바꿔준다.

관리자 권한으로 cmd열기

=>환경설정 가능

<기본키 지정방법>

import pymysql
 
# MySQL Connection 연결
conn = pymysql.connect(host='localhost', user='root', password='python',
                       db='python',port=3305, charset='utf8')
 
# Connection 으로부터 Cursor 생성
curs = conn.cursor()
 
# SQL문 실행
sql = "select * from emp"
curs.execute(sql)
 
# 데이타 Fetch
rows = curs.fetchall()
print(rows)     # 전체 rows

 
# Connection 닫기
conn.close()

resultset - cursor라고도 함

=>Json처럼 생긴 딕셔너리 형태!! MVC형태로 보내기 위한 준비작업이라 할 수 있음

<insert해보기>

import pymysql
 
conn = pymysql.connect(host='localhost', user='root', password='python',
                       db='python',port=3305, charset='utf8')
 
 
curs = conn.cursor()
sql = """insert into emp(e_id,e_name,sex,addr)
         values (%s, %s, %s, %s)"""
curs.execute(sql, ('4', '4', '4','4'))


conn.commit()
curs.close()
conn.close()

<다른 방법 insert>

import pymysql
 
conn = pymysql.connect(host='localhost', user='root', password='python',
                       db='python',port=3305, charset='utf8')
e_id='5'
e_name='5'
sex='5'
addr='5'
 
curs = conn.cursor()
sql = f"""insert into emp(e_id,e_name,sex,addr)
         values ('{e_id}', '{e_name}','{sex}','{addr}')"""

print('sql',sql)
curs.execute(sql)

conn.commit()
curs.close()
conn.close()

import pymysql
 
conn = pymysql.connect(host='localhost', user='root', password='python',
                       db='python',port=3305, charset='utf8')
e_id='5'
e_name='5'
sex='5'
addr='5'
 
curs = conn.cursor()
sql = f"""insert into emp(e_id,e_name,sex,addr)
         values ('{e_id}', '{e_name}','{sex}','{addr}')"""

print('sql',sql)
cnt =curs.execute(sql)
print("cnt",cnt)

conn.commit()
curs.close()
conn.close()

import pymysql
 
conn = pymysql.connect(host='localhost', user='root', password='python',
                       db='python',port=3305, charset='utf8')
e_id='5'
e_name='6'
sex='6'
addr='6'
 
curs = conn.cursor()


sql= f"""
    update emp 
    set 
        e_name='{e_name}',
        sex='{sex}',
        addr='{addr}'
     where
          e_id='{e_id}'"""

print('sql',sql)
cnt =curs.execute(sql)
print("cnt",cnt)

conn.commit()
curs.close()
conn.close(

<delete하기>

import pymysql
 
conn = pymysql.connect(host='localhost', user='root', password='python',
                       db='python',port=3305, charset='utf8')
e_id='5'
e_name='5'
sex='5'
addr='5'
 
curs = conn.cursor()
sql = f"""
      delete
      from emp
      where e_id='{e_id}'
      """

print('sql',sql)
cnt =curs.execute(sql)
print("cnt",cnt)

conn.commit()
curs.close()
conn.close()

""" : 엔터를 쳐도 인식할 수 있음

from flask import Flask
 
app = Flask(__name__)
 
@app.route('/') #/ :왤켐파일과 같은 역할을 한다.
def hello_world():
    return 'Hello World!'

 
if __name__=='__main__':
    app.run()

from flask import Flask
 
app = Flask(__name__)
 
@app.route('/') #/ :왤켐파일과 같은 역할을 한다.
def hello_world():
    return 'Hello World!'

@app.route('/para') #/ :왤켐파일과 같은 역할을 한다.
def para():
    return 'para' 
 
if __name__=='__main__':
    app.run()

<파라미터>

from flask import Flask
from flask.globals import request

 
app = Flask(__name__)
 
@app.route('/') #/ :왤켐파일과 같은 역할을 한다.
def hello_world():
    return 'Hello World!'

@app.route('/para') #/ :왤켐파일과 같은 역할을 한다.
def para():
    param = request.args.get('a', "하하하")
    return 'para'+param 
 
if __name__=='__main__':
    app.run()

위와 같이 파라미터 값을 이용하기 위해서는 flask에 request모듈이 필요하다. 사용방법은 간단하다. 아래와 같이 request모듈을 import하고, request.args를 사용하면 된다. request.args는 url파라미터의 값을 키=값 쌍으로 가지고 있는 딕셔너리이다.

위와 같이 get함수를 이용하면, 딕셔너리에 해당하는 키의 값이 없어도 에러가 나지 않는다. 키에 해당하는 값이 없는 경우 두 번째 입력인자를 사용하게 된다.

<post방식>

@app.route('/post',methods=['POST'])
def post():
    value=request.form['a']
    return "post:"+value

<post.html>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="http://127.0.0.1:5000/post" method="post">
   <input type="text" name="a" value="888">
   <input type="submit" value="전송">
</form>
</body>
</html>

DAO(Data Access Object) 는 데이터베이스의 data에 접근하기 위한 객체입니다. DataBase에 접근 하기 위한 로직 & 비지니스 로직을 분리하기 위해 사용합니다.

<Select하기>

import pymysql

class DaoEmp:
    def __init__(self):
        self.conn = pymysql.connect(host='localhost', user='root', password='python',
                       db='python',port=3305, charset='utf8')

        self.curs = self.conn.cursor(pymysql.cursors.DictCursor)
        
    def myselects(self):
        sql = "select * from emp"
        self.curs.execute(sql)

        rows = self.curs.fetchall()
        return rows
    def myselect(self,e_id):
       
        sql =f"""
        select 
            e_id,e_name,sex,addr
        from emp 
        where
             e_id='{e_id}'
        """ 
        self.curs.execute(sql)  
        
        rows = self.curs.fetchall()
        return rows
         
    def __del__(self):
        self.curs.close()
        self.conn.close()

if __name__ =='__main__' :    
    de =DaoEmp()
    rows=de.myselects()
    emp =de.myselect('1')
    print("rows:",rows)
    print("emp:",emp)

<insert하기>

import pymysql

class DaoEmp:
    def __init__(self):
        self.conn = pymysql.connect(host='localhost', user='root', password='python',
                       db='python',port=3305, charset='utf8')

        self.curs = self.conn.cursor(pymysql.cursors.DictCursor)
        
    
    def myinsert(self,e_id,e_name,sex,addr):
        sql =  f"""
        insert into emp(e_id,e_name,sex,addr)
        values ('{e_id}', '{e_name}','{sex}','{addr}')"""
        
        cnt=self.curs.execute(sql)
        self.conn.commit()
        return cnt

  
    def __del__(self):
        self.curs.close()
        self.conn.close()

if __name__ =='__main__' :    
    de =DaoEmp()
    cnt =de.myinsert('5','5','5','5')
    print(cnt)

<update하기>

import pymysql

class DaoEmp:
    def __init__(self):
        self.conn = pymysql.connect(host='localhost', user='root', password='python',
                       db='python',port=3305, charset='utf8')

        self.curs = self.conn.cursor(pymysql.cursors.DictCursor)
        
    def myupdate(self,e_name,sex,addr,e_id):
        sql=f"""
        update emp 
        set 
        e_name='{e_name}',
        sex='{sex}',
        addr='{addr}'
        where
        e_id='{e_id}'"""
        
        cnt=self.curs.execute(sql)
        self.conn.commit()
        return cnt
       
    def __del__(self):
        self.curs.close()
        self.conn.close()

if __name__ =='__main__' :    
    de =DaoEmp()
    cnt =de.myupdate('6','6','6','5')
    print(cnt)

import pymysql

class DaoEmp:
    def __init__(self):
        self.conn = pymysql.connect(host='localhost', user='root', password='python',
                       db='python',port=3305, charset='utf8')

        self.curs = self.conn.cursor(pymysql.cursors.DictCursor)
        
    def mydelete(self,e_id):
        sql=f"""
        delete
        from emp
        where e_id='{e_id}'
        """  
        cnt=self.curs.execute(sql)
        self.conn.commit()
        return cnt
    
    def __del__(self):
        self.curs.close()
        self.conn.close()

if __name__ =='__main__' :    
    de =DaoEmp()
    cnt =de.mydelete('5')
    print(cnt)

<html로 보내기>

flask render_template

from flask import Flask
from flask.globals import request
from flask.templating import render_template

 
app = Flask(__name__)
 
@app.route('/') #/ :왤켐파일과 같은 역할을 한다.
def hello_world():
    return 'Hello World!'

@app.route('/para') #/ :왤켐파일과 같은 역할을 한다.
def para():
    param = request.args.get('a', "하하하")
    return 'para'+param 

@app.route('/post',methods=['POST'])
def post():
    value=request.form['a']
    return "post:"+value

@app.route('/list')
def list():
    a="홍길동"
    b=["한국","브라질","칠레"]
    return render_template('list.html',a=a,b=b)

if __name__=='__main__':
    app.run()

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	리스트
	{{a}}
	{{b}}
</body>
</html>

from flask import Flask
from flask.globals import request
from flask.templating import render_template

 
app = Flask(__name__)
 
@app.route('/') #/ :왤켐파일과 같은 역할을 한다.
def hello_world():
    return 'Hello World!'

@app.route('/para') #/ :왤켐파일과 같은 역할을 한다.
def para():
    param = request.args.get('a', "하하하")
    return 'para'+param 

@app.route('/post',methods=['POST'])
def post():
    value=request.form['a']
    return "post:"+value

@app.route('/list')
def list():
    a="홍길동"
    b=["한국","브라질","칠레"]
    c=[
            {'e_id':'1', 'e_name':'1', 'sex':'1', 'addr':'1'},
            {'e_id':'2', 'e_name':'2', 'sex':'2', 'addr':'2'}
      ]
    return render_template('list.html',a=a,b=b,c=c)

if __name__=='__main__':
    app.run()

<list.html>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	리스트
	{{a}}
	
	{%for i in b%}
	{{i}}
	{% endfor %}
	
	{%for i in c%}
	{{i.e_id}}
	{{i.e_name}}
	{{i.sex}}
	{{i.addr}}
	{% endfor %}
	
</body>
</html>

<myflask_emp>

from flask import Flask
from flask.globals import request
from flask.templating import render_template

 
app = Flask(__name__)
 

@app.route('/')
def list():
  
    c=[
            {'e_id':'1', 'e_name':'1', 'sex':'1', 'addr':'1'},
            {'e_id':'2', 'e_name':'2', 'sex':'2', 'addr':'2'}
      ]
    return render_template('emp.html',c=c)

if __name__=='__main__':
    app.run()

<emp.html>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>

<table border='1px'>
   <tr>
      <td>사번</td>
      <td>이름</td>
      <td>성별</td>
      <td>주소</td>
   </tr>
{% for i in c %}
   <tr>
      <td>{{i.e_id}}</td>
      <td>{{i.e_name}}</td>
      <td>{{i.sex}}</td>
      <td>{{i.addr}}</td>
   </tr>
{% endfor %}
</table>

   
</body>
</html

db로 하기!!

from flask import Flask
from flask.globals import request
from flask.templating import render_template
from day08.dao_emp import DaoEmp

 
app = Flask(__name__)
 

@app.route('/')
def list():
  
    de=DaoEmp()
    emps =de.myselects()
    return render_template('emp.html',emps=emps)

if __name__=='__main__':
    app.run()

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>

<table border='1px'>
   <tr>
      <td>사번</td>
      <td>이름</td>
      <td>성별</td>
      <td>주소</td>
   </tr>
{% for e in emps %}
   <tr>
      <td>{{e.e_id}}</td>
      <td>{{e.e_name}}</td>
      <td>{{e.sex}}</td>
      <td>{{e.addr}}</td>
   </tr>
{% endfor %}
</table>

   
</body>
</html>

<insert하기>

<myflask_emp>

from flask import Flask
from flask.globals import request
import pymysql
from flask.templating import render_template
from day9.dao_emp import DaoEmp

app = Flask(__name__)

@app.route('/')
@app.route('/emp')
def emp():
    de = DaoEmp()
    emps = de.myselects()
    return render_template('emp.html', emps=emps)


@app.route('/emp_add')
def emp_add():
    return render_template('emp_add.html')


@app.route('/emp_add_act', methods=['POST'])
def emp_add_act():
    de = DaoEmp()
    
    e_id     = request.form['e_id']
    e_name   = request.form['e_name']
    sex      = request.form['sex']
    addr     = request.form['addr']
    
    cnt = de.myinsert(e_id,e_name,sex,addr)
    
    return render_template('emp_add_act.html', cnt=cnt)

if __name__ == '__main__':
    app.run(debug=True)

<emp_html>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>

<table border='1px'>
   <tr>
      <td>사번</td>
      <td>이름</td>
      <td>성별</td>
      <td>주소</td>
   </tr>
{% for e in emps %}
   <tr>
      <td>{{e.e_id}}</td>
      <td>{{e.e_name}}</td>
      <td>{{e.sex}}</td>
      <td>{{e.addr}}</td>
   </tr>
{% endfor %}
</table>

   
</body>
</html>

<emp_add.html>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="emp_add_act"  method="post">
<table>
	<tr>
		<td>사번</td>
		<td><input type="text" name="e_id" /></td>		
	</tr>
	<tr>
		<td>이름</td>
		<td><input type="text" name="e_name" /></td>
		
	</tr>
	<tr>
		<td>성별</td>
		<td><input type="text" name="sex" /></td>
		
	</tr>
	<tr>
		<td>주소</td>
		<td><input type="text" name="addr" /></td>
	</tr>
	<tr>
		<td colspan="2">
			<input type="submit" value="추가">
		</td>
	</tr>
</table>
</form>

</body>
</html>

<emp_add_act.html>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript">
var cnt ='{{cnt}}';

if(cnt=='1'){
	alert("정상적으로 추가 되었습니다.");
	location.href="emp"
}else{
	history.back();
}
</script>
</head>
<body>
	추가실행
</body>
</html>

<수정,삭제해보기>

from flask import Flask
from flask.globals import request
import pymysql
from flask.templating import render_template
from day9.dao_emp import DaoEmp

app = Flask(__name__)
de = DaoEmp()

@app.route('/')
@app.route('/emp')
def emp():
    emps = de.myselects()
    return render_template('emp.html',emps=emps)


@app.route('/emp_del_act',methods=['GET'])
def emp_del_act():
    e_id = request.args.get('e_id','')
    cnt = -1
    try:
        cnt = de.mydelete(e_id)
    except:
        print("dao : error")
    
    return render_template('emp_del_act.html',cnt=cnt)

@app.route('/emp_mod')
def emp_mod():
    e_id = request.args.get('e_id','')
    emp = de.myselect(e_id)
    print("emp",emp)
    return render_template('emp_mod.html',emp=emp)

@app.route('/emp_mod_act',methods=['POST'])
def emp_mod_act():
    e_id    = request.form['e_id']
    e_name  = request.form['e_name']
    sex     = request.form['sex']
    addr    = request.form['addr']
    cnt = -1
    try:
        cnt = de.myupdate(e_name, sex, addr,e_id)
    except:
        print("dao : error")
    
    return render_template('emp_mod_act.html',cnt=cnt)

@app.route('/emp_add')
def emp_add():
    return render_template('emp_add.html')

@app.route('/emp_add_act',methods=['POST'])
def emp_add_act():
    e_id    = request.form['e_id']
    e_name  = request.form['e_name']
    sex     = request.form['sex']
    addr    = request.form['addr']
    cnt = -1
    try:
        cnt = de.myinsert(e_id, e_name, sex, addr)
    except:
        print("dao : error")
    
    return render_template('emp_add_act.html',cnt=cnt)


if __name__ == '__main__':
    app.run(debug=True)

-수정

<emp_mod.html>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="emp_mod_act" method="post">
<table border="1px">
	<tr>
		<td>사번</td>
		<td><input type="text" name="e_id" value="{{emp.e_id}}" /></td>
	</tr>
	<tr>
		<td>이름</td>
		<td><input type="text" name="e_name" value="{{emp.e_name}}" /></td>
	</tr>
	<tr>
		<td>성별</td>
		<td><input type="text" name="sex" value="{{emp.sex}}" /></td>
	</tr>
	<tr>
		<td>주소</td>
		<td><input type="text" name="addr" value="{{emp.addr}}" /></td>
	</tr>
	<tr>
		<td colspan="2" >
			<input type="submit" value="수정" />
		</td>
	</tr>	
</table>
</form>
	
</body>
</html>

<emp_mod_act.html>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript">
var cnt ='{{cnt}}';

if(cnt=='1'){
	alert("정상적으로 수정 되었습니다.");
	location.href="emp"
}else{
	history.back();
}
</script>
</head>
<body>

</body>
</html>

-삭제

<emp_del_act.html>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript">
var cnt ='{{cnt}}';

if(cnt=='1'){
	alert("정상적으로 삭제 되었습니다.");
	location.href="emp"
}else{
	history.back();
}
</script>
</head>
<body>
	추가실행
</body>
</html>

삭제 confirm 추가!!

728x90

'파이썬' 카테고리의 다른 글

Crawling 2 , fast (8)	2022.06.16
Python Flask CORS ,Crawling (0)	2022.06.14
Flask에서 Ajax ,axios예제 (0)	2022.06.13
오목게임 만들기-22.06.03~22.06.07 (0)	2022.06.07
파이썬 -pyqt (0)	2022.06.03

ABOUT ME

하루복습 하루복습

'파이썬' 카테고리의 다른 글

티스토리툴바

ABOUT ME

'파이썬' 카테고리의 다른 글

관련글 관련글 더보기

티스토리툴바