MySQL and Python 코드

MySQL 접속 및 사용예제.

 

1. insert 구문

 

import datetime

import mysql.connector

 

conn_params = {

       "database" : "employees", "host" : "localhost",

       "user" : "oracle", "password" : "oracle"

}

 

conn = mysql.connector.connect(**conn_params)

cursor = conn.cursor()

 

query = ('''insert into salaries (emp_no, salary, from_date, to_date)

                   values(%s, %s, %s, %s)''')

from_date = datetime.date(2010, 10, 01)

to_date = datetime.date(2015, 09, 30)

 

data = (10000, 234234, from_date, to_date)

cursor.execute(query, data)

print("Number of rows updated: %d" % cursor.rowcount)

cursor.close()

conn.commit()

conn.close()

 

2. select 구문

import datetime

import mysql.connector

 

conn_params = {

       "database" : "employees", "host" : "localhost",

       "user" : "oracle", "password" : "oracle"

}

 

conn = mysql.connector.connect(**conn_params)       

# conn = mysql.connector.connect(pool_name="test", pool_size=5, **conn_params)  -- pool 사용시

cursor = conn.cursor()         # cursor = conn.cursor(buffered=True)

query = ('''select first_name, last_name, hire_date from 

                   employees where hire_date between %s and %s''')

from_date = datetime.date(2010, 10, 01)

to_date = datetime.date(2015, 09, 30)

 

cursor.execute(query, (from_date, to_date))

row = cursor.fetchone()

while row is not None :

      print("{}, {} was hired on {%d %b %Y}".format(row[0], row[1], row[2]})

      row = cursor.fetchone()

cursor.close()

conn.close()