Last active
April 13, 2021 08:59
-
-
Save megatk/879d9a57b1e2bf1fddb9be5d7e80584e to your computer and use it in GitHub Desktop.
Python3 で、MySQL にアクセスする
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#! /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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#! /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