【Python】MySQLに接続してデータを取得、表示してみる
おはようございます。
前回までに Ticker の情報を SQLite に登録してきたのですが、
やっぱり SQLite だとTicker みたいに激しくデータ登録をしてしまうと
データ検索でもロックが発生してしまってうまくないので MySQL を使おうかと思います。
プログラムは SQLite のサンプルプログラムを流用してとりあえずデータ表示をしてみます。
スポンサーリンク
新規プロジェクトの作成
PyCharmを起動し、上部メニューの「File」＞「New Project」を選択し、
ウィザードに沿ってプロジェクトを新規作成します。
（今回は「MySqlSample」プロジェクトとしました。）
プログラムの移植
SQLiteのサンプルプログラムを流用します。
一通り（フォルダ構成）含む、ファイルを移植します。
フォルダ構成：
MySqlSample
│ server.py
├─static
│ ├─css
│ │ style.css
│ └─js
│ script.js
└─templates
index.html
MySQL用パッケージのインストール
上部メニューの「File」＞「Default Settings」を選択します。
Default Setting画面が表示されるので、右上の対象プロジェクト環境を今回作成した環境に変更し、右側にある「＋」ボタンをクリックします。
パッケージ検索画面で、「mysql」と検索します。
一覧に表示された「mysql-connector-python-rf」を選択して「Install Package」ボタンをクリックします。
同様に、「Tornado」パッケージもインストールしてください。
プログラムの修正
インポート宣言の追加
server.py
import mysql.connector
接続処理等の修正
DBへの接続方法、SQL文法、カラムの型など、
SQLiteとMySQLで違う箇所を修正します。
server.py（SQLiteUtil → MySQLUtilに変更）
class MySQLUtil:
"""
MySQL操作用クラス
"""
@staticmethod
def create_db():
"""
データベース、及び必要なテーブルを作成します.
"""
logging.info("create database")
with closing(mysql.connector.connect(
host="localhost",
port="3306",
user="USER01",
password="USER01",
database="DB01"
)) as conn:
c = conn.cursor()
# 種別テーブル
sql = "CREATE TABLE IF NOT EXISTS MSTKIND ("
sql += " KIND_CD CHAR(2) NOT NULL"
sql += " , KIND_NAME VARCHAR(20)"
sql += " , PRIMARY KEY (KIND_CD)"
sql += ")"
c.execute(sql)
# 猫テーブル
sql = "CREATE TABLE IF NOT EXISTS TBLCAT ("
sql += " NO INTEGER(2) NOT NULL"
sql += " , NAME VARCHAR(20) NOT NULL"
sql += " , SEX CHAR(3) NOT NULL"
sql += " , AGE INTEGER(1) DEFAULT 0 NOT NULL"
sql += " , KIND_CD CHAR(2) DEFAULT '00' NOT NULL"
sql += " , FAVORITE VARCHAR(40)"
sql += " , PRIMARY KEY (NO)"
sql += ")"
c.execute(sql)
c.close()
@staticmethod
def insert_initial_data():
"""
各テーブルに初期データを登録します
"""
logging.info("insert initial data")
with closing(mysql.connector.connect(
host="localhost",
port="3306",
user="USER01",
password="USER01",
database="DB01"
)) as conn:
c = conn.cursor()
# 種別マスタ
sql = "REPLACE INTO MSTKIND (KIND_CD, KIND_NAME) VALUES (%s,%s)"
kinds = [
('01', 'キジトラ'),
('02', '長毛種（不明）'),
('03', 'ミケ（っぽい）'),
('04', 'サビ'),
('09', 'その他'),
]
c.executemany(sql, kinds)
# 猫データ
sql = "REPLACE INTO TBLCAT VALUES (%s,%s,%s,%s,%s,%s)"
cats = [
(1, "そら", "♂", 6, "01", "犬の人形"),
(2, "りく", "♂", 5, "02", "人間"),
(3, "うみ", "♀", 4, "03", "高級ウェットフード"),
(4, "こうめ", "♀", 2, "04", "横取りフード"),
]
c.executemany(sql, cats)
c.close()
conn.commit()
@staticmethod
def select_kind_all():
"""
MST種別のデータを全て取得します.
:return:
"""
logging.info("select all kind")
with closing(mysql.connector.connect(
host="localhost",
port="3306",
user="USER01",
password="USER01",
database="DB01"
)) as conn:
c = conn.cursor()
sql = "SELECT * FROM MSTKIND ORDER BY KIND_CD"
c.execute(sql)
return c.fetchall()
@staticmethod
def select_cat(name, kind_cd):
"""
猫を検索します.
:param name:
:param kind_cd:
:return:
"""
logging.info("select cat")
with closing(mysql.connector.connect(
host="localhost",
port="3306",
user="USER01",
password="USER01",
database="DB01"
)) as conn:
c = conn.cursor(dictionary=True)
# SQL組み立て
sql = "SELECT C.NO, C.NAME, C.SEX, C.AGE, C.KIND_CD, K.KIND_NAME, C.FAVORITE FROM TBLCAT C"
sql += " LEFT OUTER JOIN MSTKIND K ON ( C.KIND_CD = K.KIND_CD)"
if kind_cd == "":
sql += " WHERE C.NAME LIKE '" + name + "%'"
else:
sql += " WHERE C.NAME LIKE '" + name + "%' AND C.KIND_CD = '" + kind_cd + "'"
sql += " ORDER BY C.NO"
result = []
row = {}
c.execute(sql)
for r in c.fetchall():
result.append({
"no": r['NO'],
"name": r['NAME'],
"sex": r['SEX'],
"age": r['AGE'],
"kind_name": r['KIND_NAME'],
"favorite": r['FAVORITE'],
})
return result
起動してみる
無事に検索、データ表示することができました。
まとめ
ひとまず、データの表示までは簡単に移植できました。
次回は実際に Bitflyer の Ticker 情報を登録してみましょうかね。
ではでは。