Create a gist now

Instantly share code, notes, and snippets.

@megatk /index.py
Last active Jun 3, 2017

What would you like to do?
Python3 で、MySQL にアクセスする
#! /usr/bin/python3
# -*- coding: utf-8 -*-
from mySQLUtil import MySQLUtil
dbx = MySQLUtil(
host='localhost',
user='root',
password='******',
db='test',
charset='utf8'
)
bind = {}
bind['id'] = 1
sql = "SELECT * FROM post WHERE id = %(id)s"
dbdata = dbx.query(sql, bind)
print(dbdata)
#! /usr/bin/python3
# -*- coding: utf-8 -*-
import pymysql.cursors
class MySQLUtil:
def __init__(self, host, user, password, db, charset='utf8'):
self.con = pymysql.connect(
host=host,
user=user,
password=password,
db=db,
charset=charset,
# Selectの結果をディクショナリ形式で受け取る
cursorclass=pymysql.cursors.DictCursor
)
def query(self, sql, bind=None, use_transaction=False):
# pymysqlが内部的に%演算子を採用しているため
# 検索すると、タプルで指定するケース以下でも実行可能
# bind は、ディクショナリ形式 例) {'name':'dada','scd': 'tk'}
# 置換部分は、% 演算子の名前指定 例) SELECT * FROM list WHERE servicecd = %(scd)s AND name = %(name)s
with self.con.cursor() as cursor:
res = cursor.execute(sql, bind) # SELECT以外の更新系SQLは影響のあった件数を返す
self._executed = cursor._executed
if 'SELECT'== sql.strip()[:6].upper():
# 参照系SQL(結果をディクショナリ形式でレスポンス)
res = cursor.fetchall()
use_transaction = False # 参照系はトランザクションなし
if not use_transaction:
self.con.commit()
# cursor._executed で実行したSQL文を確認できる
# 何も指定しなくてもSQLの特殊文字がエスケープされる!!!
# 例)
# sql = 'SELECT * FROM post WHERE title LIKE %s'
# cursor.execute(sql, ("';DELETE FROM diaries'"))
# print(cursor._executed)
# > SELECT * FROM doc WHERE title LIKE '\';DELETE FROM diaries\'\'
return res
def close(self):
self.con.close()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment