[Python] MySQL connect , 간단 사용법

python 으로 MySQL 사용
CRUD

 

python terminal 에서 install

pip install mysql-connector-python

 

준비끝.

import

import mysql.connector

 

연결정보

mydb = mysql.connector.connect(
    host="input_some.host.com_or_ip",
    user="input_username",
    passwd="input_password",
    database="input_dbname"
)

 

select 문

cur = mydb.cursor()

cur.execute("select * from tbl_something where idx in (46,88,89,103,107)")

myresult = cur.fetchall()

for x in myresult:
  print(x)

 

 

전체 소스

import mysql.connector

mydb = mysql.connector.connect(
    host="192.168.1.23",
    user="root",
    passwd="mypassword",
    database="testdb"
)

cur = mydb.cursor()

cur.execute("select * from tbl_something where tidx in (46,88,89,103,107)")

myresult = cur.fetchall()

for x in myresult:
  print(x)

 

역시 파이썬은 간단하네요.

 

======= 예제들

하나만 조회하기

myresult = mycursor.fetchone()
print(myresult)

 

Insert 문

'''
연결정보생략
'''

mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record inserted.")

 

Delete 문

'''
연결정보생략
'''

mycursor = mydb.cursor()

sql = "DELETE FROM customers WHERE address = 'Mountain 21'"

mycursor.execute(sql)

mydb.commit()

print(mycursor.rowcount, "record(s) deleted")

 

Update 문

mycursor = mydb.cursor()

sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Valley 345'"

mycursor.execute(sql)

mydb.commit()

print(mycursor.rowcount, "record(s) affected")