[Python] MySQLに接続

pymysqlを使用してPythonからMySQLを操作します。

  • インストール
pip install PyMySQL
  • スクリプト例
import pymysql.cursors

## connect
connection = pymysql.connect(
    host = 'localhost',
    user = 'hoge',
    password = 'hogehoge',
    db = 'hoge_db',
    charset = 'utf8',
    cursorclass = pymysql.cursors.DictCursor # 実行結果をDictで返す
)

## select
with connection.cursor() as cursor:
    sql = "SELECT * FROM hoge_table WHERE id = %s"
    cursor.execute(sql, 4)
    results = cursor.fetchall()

## insert
with connection.cursor() as cursor:
    sql = "INSERT INTO hoge_table (id, name) VALUES (%s, %s)"
    res = cursor.execute(sql, (5, "huga"))
    connection.commit() # 保存知るためには明示的に指定

## update
with connection.cursor() as cursor:
    sql = "UPDATE hoge_table SET name = %s WHERE id = %s"
    res = cursor.execute(sql, ("hugahuga", "5"))
    connection.commit()