[Python] mySQL과 연동 - select, insert, update, delete

cmd 창 열어서 pip install pymySQL 입력

SELECT

import pymysql

conn = pymysql.connect(host='localhost', user='root', password='python',
                       db='python', port=3305, charset='utf8')

curs=conn.cursor()

sql="select m_id, m_name, tel from member"
curs.execute(sql)

rows = curs.fetchall()

print(rows)
print(rows[1][2])

curs.close()
conn.close()

INSERT

여러개는 executemany / 한 개는 execute

import pymysql

conn = pymysql.connect(host='localhost', user='root', password='python',
                       db='python', port=3305, charset='utf8')

curs=conn.cursor()

sql="insert into member(m_id, m_name, tel) values(%s, %s, %s)"

cnt = curs.executemany(sql, (('3','3','3'), ('4','4','4')))
#curs.execute(sql, ('4','4','4'))
print(cnt)

#conn.commit()


curs.close()
conn.close()

UPDATE

import pymysql
from sphinx.ext.autodoc import member_order_option

conn = pymysql.connect(host='localhost', user='root', password='python',
                       db='python', port=3305, charset='utf8')

curs=conn.cursor()
sql ="""
UPDATE member
SET
    m_name=%s,
    tel=%s  
where 
    m_id=%s
    """
    
cnt = curs.execute(sql, ('9','9','3')) 


print(cnt)

conn.commit()


curs.close()
conn.close()

DELETE

import pymysql
from sphinx.ext.autodoc import member_order_option

conn = pymysql.connect(host='localhost', user='root', password='python',
                       db='python', port=3305, charset='utf8')

curs=conn.cursor()
sql ="""
delete from member
where 
    m_id=%s
    """
    
cnt = curs.execute(sql, ('3')) 

print(cnt)

conn.commit()

curs.close()
conn.close()