raspberrypi3にPyMySQLの導入

PyMySQLでpythonからMySQLを操作する。

PyMySQLインストール

• 下記コマンドを実行する。

$ sudo pip install PyMySQL

使い方

• 下記のようなテーブルを想定

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| email    | varchar(255) | NO   |     | NULL    |                |
| password | varchar(255) | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

• 上記テーブルのcreate文

CREATE TABLE `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `email` varchar(255) COLLATE utf8_bin NOT NULL,
    `password` varchar(255) COLLATE utf8_bin NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
AUTO_INCREMENT=1 ;

• DBに接続して切断する

sample.py

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='db',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

～下記のＩＮＳＥＲＴ文などの記述～

connection.close()

• INSERT文

with connection.cursor() as cursor:
    # Create a new record
    sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
    cursor.execute(sql, ('[e-mail]', 'very-secret'))

# connection is not autocommit by default. So you must commit to save
# your changes.
connection.commit()

• SELECT文

with connection.cursor() as cursor:
    # Read a single record
    sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
    cursor.execute(sql, ('[e-mail]',))
    result = cursor.fetchone()
    print(result)

参考

github readme