[python]day6. MSSQL - SELECT/INSERT/UPDATE/DELETE

SELECT 연습하기

masql_select.py

import pymssql

server = 'localhost'
# 데이터 베이스 이름
database = 'python'
# 접속 유저명
username = 'sa'
# 접속 유저 패스워드
password = 'pypy'

tds_version='8.0'

# MSSQL 접속
conn  =  pymssql.connect(server, username, password, database, tds_version) 
cursor = conn.cursor()


# SQL문 실행
cursor.execute('SELECT * FROM emp')

# 데이타를 하나씩 Fetch하여 출력
row = cursor.fetchone()

while row:
    print(row[0], row[1],  row[2])
    row = cursor.fetchone()

# 연결 끊기
conn.close()

mssql_select2.py

import pymssql


# MSSQL 접속
conn  =  pymssql.connect(server= '192.168.145.35', user= 'sa', password= 'python', database= 'python') 
cursor = conn.cursor()

# cursor=> 자바에선 Statement st=> cur로 쓰임 


# rs = st.executeQuery했던것처럼.
# SQL문 실행
cursor.execute('SELECT * FROM emp')

# 데이타를 하나씩 Fetch하여 출력
list = cursor.fetchall()
print(list)


# 연결 끊기

cursor.close()
conn.close()

---

INSERT 연습하기

mssql_insert.py

import pymssql

conn = pymssql.connect(host='localhost', user='sa', password='python', database='python')

cursor = conn.cursor()

sql = """insert into emp
        (e_id, e_name, sex, addr)
        values
        ('3','3','3','3')
"""

cursor.execute(sql)
conn.commit()


cursor.close()    
conn.close()

---

UPDATE 연습하기

mssql_update.py

import pymssql

conn = pymssql.connect(host='localhost', user='sa', password='python', database='python')

cursor = conn.cursor()

e_id = '3'
e_name = '5'
sex = '5'
addr = '8'

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


cursor.execute(sql)

# update된 행의 갯수확인하는방법 
print('rowcount: ', cursor.rowcount)
conn.commit()


cursor.close()    
conn.close()

---

DELETE 연습하기

mssql_delete.py

import pymssql

conn = pymssql.connect(host='localhost', user='sa', password='python', database='python')

cursor = conn.cursor()

e_id = '3'

sql = f"""
        delete from emp
           where e_id='{e_id}'
      """

cursor.execute(sql)
print(cursor.rowcount)
conn.commit()

cursor.close()    
conn.close()