Python DB 연결

인스톨 두가지 방법 [pipinstall, condainstall]

 

 

conda를 사용하여 python db 설치

- cmd 실행 : ctrl + R

conda install pymysql

cmd로 실행이 안되는 경우 anaconda를 사용

 

이클립스에서 python mysql select

import pymysql
 
conn = pymysql.connect(host='localhost', user='root', password='java',
                       db='python', charset='utf8')
 
curs = conn.cursor()
 
sql = "select col01,col02,col03 from hello"
curs.execute(sql)
 
rows = curs.fetchall()
for row in rows:
    print(row[1])    

conn.close()

 

 

이클립스에서 python mysql insert

import pymysql
 
conn = pymysql.connect(host='localhost', user='root', password='java',
                       db='python', charset='utf8')
 
curs = conn.cursor() # java에서의 statement
 
data = [
    (4, '4', '4'),
    (5, '5', '5'),
    (6, '6', '6'),
]

data.append(('1','1','1'))

sql = """insert into hello(col01,col02,col03)
         values (%s, %s, %s)"""
cnt = curs.executemany(sql, data)
print("cnt : ",cnt);
conn.commit()
 
conn.close()

이클립스에서 python mysql update

import pymysql
 
conn = pymysql.connect(host='localhost', user='root', password='java',
                       db='python', charset='utf8')
 
curs = conn.cursor()
 
sql = """
        update hello 
        set 
        col02 = '2'
        , col03 = '2'
        where col01 = '3'
    """

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

conn.commit()
 
conn.close()

이클립스에서 python mysql delete

import pymysql
 
conn = pymysql.connect(host='localhost', user='root', password='java',
                       db='python', charset='utf8')
 
curs = conn.cursor() # java에서의 statement
 
sql = """DELETE FROM hello WHERE col01 = 1"""
cnt = curs.execute(sql)
print("cnt : ",cnt);
conn.commit()
 
conn.close()

insert 구문 메서드화

import pymysql

def insertChicken(tups):
    conn = pymysql.connect(host='localhost', user='root', password='java',
                           db='python', charset='utf8')
     
    curs = conn.cursor() # java에서의 statement
    
    sql = """insert into hello(col01,col02,col03)
             values (%s, %s, %s)"""
    cnt = curs.executemany(sql, tups)
    
    conn.commit()
    conn.close()
    return cnt

if __name__ == '__main__': # java에서의 main 처럼 실행해줌
    tups = []
    
    tups.append(('1','1','1'))
    tups.append(('2','1','1'))
    tups.append(('3','1','1'))
    tups.append(('4','1','1'))
    cnt = insertChicken(tups)
    print("cnt:",cnt)