【Python】MySQLに接続してデータを取得、表示してみる

2018年4月5日2020年10月11日Python,開発

おはようございます。

前回までに Ticker の情報を SQLite に登録してきたのですが、
やっぱり SQLite だとTicker みたいに激しくデータ登録をしてしまうと
データ検索でもロックが発生してしまってうまくないので MySQL を使おうかと思います。

プログラムは SQLite のサンプルプログラムを流用してとりあえずデータ表示をしてみます。

【VB.NET】SQLiteに接続してデータを取得してみる

1. 新規プロジェクトの作成
2. プログラムの移植
3. MySQL用パッケージのインストール
4. プログラムの修正
5. まとめ

新規プロジェクトの作成

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

1	importmysql.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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

classMySQLUtil:

"""

MySQL操作用クラス

"""

@staticmethod

defcreate_db():

"""

データベース、及び必要なテーブルを作成します.

"""

logging.info("create database")

withclosing(mysql.connector.connect(

host="localhost",

port="3306",

user="USER01",

password="USER01",

database="DB01"

))asconn:

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

definsert_initial_data():

"""

各テーブルに初期データを登録します

"""

logging.info("insert initial data")

withclosing(mysql.connector.connect(

host="localhost",

port="3306",

user="USER01",

password="USER01",

database="DB01"

))asconn:

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

defselect_kind_all():

"""

MST種別のデータを全て取得します.

:return:

"""

logging.info("select all kind")

withclosing(mysql.connector.connect(

host="localhost",

port="3306",

user="USER01",

password="USER01",

database="DB01"

))asconn:

c=conn.cursor()

sql="SELECT * FROM MSTKIND ORDER BY KIND_CD"

c.execute(sql)

returnc.fetchall()

@staticmethod

defselect_cat(name,kind_cd):

"""

猫を検索します.

:param name:

:param kind_cd:

:return:

"""

logging.info("select cat")

withclosing(mysql.connector.connect(

host="localhost",

port="3306",

user="USER01",

password="USER01",

database="DB01"

))asconn:

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)"

ifkind_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)

forrinc.fetchall():

result.append({

"no":r['NO'],

"name":r['NAME'],

"sex":r['SEX'],

"age":r['AGE'],

"kind_name":r['KIND_NAME'],

"favorite":r['FAVORITE'],

})

returnresult

起動してみる

無事に検索、データ表示することができました。

まとめ

ひとまず、データの表示までは簡単に移植できました。
次回は実際に Bitflyer の Ticker 情報を登録してみましょうかね。

ではでは。

2018年4月5日2020年10月11日Python,開発MySQL,Python,プログラミング

Posted by doradora