【 Python 】mysql-connector-pythonでMySQLにアクセスしよう！の記事では、Python から MySQLに接続するための方法について解説しました。
あわせて読みたい
【 Python 】mysql-connector-pythonでMySQLにアクセスしよう！
今回はPythonからMySQLデータベースに接続し、データを更新したり取得する方法について解説します。 MySQL は無料で使える本格的なデータベースであり、商用利用も盛ん...
今回は、以前 SQLite 向けに作成した、データの抽出や更新が簡単に出来る Python用のクラスを MySQL向けに修正しましたので、ソースコードの公開と解説をしたいと思います。
コピー＆ペーストで簡単に使えますので、皆さんの用途に合わせて追加／修正の上、ご活用頂ければと思います。
目次
クラスの使い方
今回は PostgreConnectというクラス名にしています。
使う際には、下記の通りパッケージのンポートが必要です。
mysqlclient ではなく MySQLdb であることにご注意ください。
|
1 |
import MySQLdb |
また、あかじめ mysqlclientのインストールが必要なので、方法を知りたい方は こちらの記事をご一読ください。
リファレンス（メソッドの名前と仕様）
今回のクラスに実装されているメソッドの一覧と仕様は以下の様になります。
| 機能 | メソッド名と引数 | 戻り値 | 補足 |
|---|---|---|---|
| コンストラクタ | MySQLConnect ( host, dbname, user, password, port=3306 ） | インスタンス | 接続先ホスト名、データベース名、ユーザー名、パスワード、ポートを指定する。 |
| 任意のSQLを実行 | execute(sql) | なし | 任意のSQLを１つだけ実行 |
| 複数のSQLをまとめて実行 | execute_all(sqls) | なし | リストに格納された複数のSQLをトランザクション付きで実行。 エラーが発生するとロールバックされる。 |
| 問い合わせSQLの実行 | execute_query(sql) | リスト | select等の問い合わせSQLを実行し、全ての結果をリストで返す。 例： [ (aa , 123 , True) , (bb , 567 , False) ] |
| １つの値を返すSQLの実行 | execute_scalor(sql) | 単一の値 | 1個の値を返す問い合わせSQLを実行し、結果を取得 |
| テーブルの作成 | create( tablename, columns, primarykey = '', isdrop=False ) | なし | テーブル名、カラム名、主キーを指定してテーブルを作成する。 既にテーブルがあればエラーとなるが、 isdrop=Trueにすることで、既にテーブルが存在していれば削除を実行する。 |
| テーブルの削除 | drop(tablename) | なし | テーブルが存在していれば削除する。 |
| テーブルの存在チェック | exists(tablename) | True/False | テーブルが存在していれば True、存在しなければ Falseを返す。 |
| テーブル名の変更 | rename ( old_tablename, new_tablename ) | なし | old_tablename を new_table_nameに変更する |
| カラムの追加 | add_column ( tablename, columns ) | なし | tablenameで指定したテーブルにcolumnsで指定した列を追加する。 columnsはカラム名をリスト形式で指定する。 カラム名の後に型を指定することも可能。 例：[ 'col1 varchar2(10)' , 'col2 datetime' ] |
| Create文の取得 | get_create_statment ( tablename ) | str | tablenameで指定したテーブルのCreate文を文字列で返す。 |
| テーブル一覧の取得 | get_table_list ( table_type=’’ ) | リスト | データベースに登録されているテーブル及びビューの名前をリストで返す。 例：[ 'table1' , 'table2' , 'table3' ] table_type に 'view' を指定するとビューのみ、'table'を指定するとテーブルのみの一覧が取得できる。 |
| カラムの名前と型の一覧を取得 | get_column_type( tablename ) | リスト | tablenameで指定したテーブルにおいて、カラム名と型をタプルに格納し、リストで返す。 例：[ ('col1' , 'text') , ('col2' , 'real') ] |
| カラムの名前の一覧を取得 | get_column_list( tablename ) | リスト | tablenameで指定したテーブルにおいて、カラム名をリストで返す。 例：[ 'col1' , 'col2' , 'col3' ] |
簡単な使い方のサンプル
test といテーブルを作成し、データの追加、検索、カラム追加など、一通りの処理を実行するサンプルです。
'host name' , 'db name' , 'user name' , 'password' は、ご自身の環境に合わせて修正をお願いします。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
#DBへの接続 db = MySQLConnect('host name','db name','user name','password') #指定したテーブルが存在すれば、テーブルを削除 db.drop('test2') #テーブルの新規作成 db.create('test','name nvarchar(10),num numeric(5,1)','name,num',isdrop=True) #Insert文の作成 sqls = [] for n in range(10): sqls.append("insert into test values('data{0}',{0})".format(n)) #１つのトランザクション内で複数SQLの実行 db.execute_all(sqls) #指定テーブルのデータを取得 data = db.execute_query('select * from test') print(data) #指定テーブルへの列追加 db.add_column('test',['add1 nvarchar(10)','add2 numeric(10,3)','add3 timestamp']) #テーブルの存在チェック print(db.exists('test')) print(db.exists('test9')) #テーブルとViewの一覧の取得 print(db.get_table_list()) #View の一覧を取得 print(db.get_table_list('view')) #テーブルの一覧を取得 print(db.get_table_list('table')) #指定したテーブルのカラム名の一覧を取得 print(db.get_column_list('test')) #指定したテーブルのカラム名とデータ型の取得 print(db.get_column_type('test')) #スカラ（結果が１つだけのSelect文）の実行 print(db.execute_scalor("select sum(num) as num from test")) #データベース名の変更 db.rename('test','test2') |
クラスのソースコード
以下が今回のクラスのソースコードとなります。
そのままコピー＆ペーストで張り付けて利用が可能です。
あとは、ご自由に変更の上お使いください。
Pythonで推奨される命名規則、コメントの書き方を使用しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 |
import mysql.connector class MySQLConnect: ''' MySQLのヘルパークラス ''' GET_TABLE_LIST_QUERY = "SELECT table_name,type FROM (SELECT table_name,table_schema,CASE table_type WHEN 'VIEW' THEN 'view' ELSE 'table' END AS type FROM information_schema.tables) t WHERE table_name LIKE '{0}' AND table_schema LIKE '{1}' AND type like '{2}'" GET_COLUMN_LIST_QUERY = "SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like LOWER('{0}') and TABLE_SCHEMA like LOWER('{1}') ORDER BY ORDINAL_POSITION" GET_ALTER_TABLE_QUERY = "ALTER TABLE {0} ADD {1}" GET_RENAME_TABLE_QUERY = "RENAME TABLE {0} TO {1}" def __init__(self,host,dbname,user,password,port=3306): ''' DBの接続情報を保持する Parameters ---------- host : str ホスト名 dbname : str DB名 user : str ユーザー名 password : str パスワード port : integer ポート ''' self.host = host self.dbname = dbname self.user = user self.password = password self.port = port def __connect(self): return mysql.connector.connect(host=self.host,port=self.port,db=self.dbname,user=self.user,passwd=self.password) def execute(self,sql): ''' SQLを実行し、結果を取得する Parameters ---------- columns : str 実行したいSQL ''' conn = self.__connect() cur = conn.cursor() cur.execute(sql) conn.commit() cur.close() conn.close() def execute_all(self,sqls): ''' 複数のSQLをトランザクション配下で実行する Parameters ---------- columns : strs 実行したいSQLのリスト ''' conn = self.__connect() cur = conn.cursor() try: for sql in sqls: cur.execute(sql) conn.commit() except MySQLdb.Error as e: conn.rollback() cur.close() conn.close() def execute_query(self,sql): ''' select 系のSQLを実行し、結果を全て取得する Parameters ---------- columns : str 実行したいSQL Returns ---------- data: list １行分をタプルとし、複数行をリストとして返す <例> [('RX100','Sony',35000),('RX200','Sony',42000)] ''' conn = self.__connect() cur = conn.cursor() cur.execute(sql) res = cur.fetchall() cur.close() conn.close() return res def execute_scalor(self,sql): ''' 結果の値が１つしかないSQLを実行し、結果を取得する Parameters ---------- columns : str 実行したいSQL Returns ---------- res: 実行結果により返された値 ''' conn = self.__connect() cur = conn.cursor() cur.execute(sql) res = cur.fetchone() cur.close() conn.close() return res[0] if res != None else None def create(self,tablename,columns,primarykey = '',isdrop=False): ''' テーブルを作成する Parameters ---------- columns : str 「列名」又は「列名＋型」をカンマ区切りで指定 <例> 'product text,price int,maker,year' primarykey: str プライマリーキーをカンマ区切りで指定 <例> 'product,year' ''' if isdrop : self.drop(tablename) pkey = ',primary key({0})'.format(primarykey) if primarykey != '' else '' sql = 'create table {0}({1} {2})'.format(tablename,columns,pkey) self.execute(sql) def drop(self,tablename): ''' 指定されたテーブルが存在すれば削除、無ければ何もしない Parameters ---------- tablename : str 削除したいテーブル名 ''' res = self.execute_query(self.GET_TABLE_LIST_QUERY.format(tablename,self.dbname,'%%')) if len(res) > 0: self.execute("drop {0} {1}".format(res[0][1],tablename)) def exists(self,tablename): ''' 指定したテーブル、又はビューの有無を判定する Parameters ---------- columns : str 実行したいSQL Returns ---------- テーブル又はビューが存在すればTrue 存在しなければ False ''' res = self.execute_scalor(self.GET_TABLE_LIST_QUERY.format(tablename,self.dbname,'%%')) return False if res == None else True def rename(self,old_tablename,new_tablename): ''' テーブル名を変更する Parameters ---------- old_tablename : str 変更前のテーブル名 new_tablename : str 変更後のテーブル名 ''' self.execute(self.GET_RENAME_TABLE_QUERY.format(old_tablename,new_tablename)) def add_column(self,tablename,columns): ''' テーブル名を変更する Parameters ---------- tablename : str テーブル名 columns : str 「列名」又は「列名＋型」をリスト形式で指定 <例> ['product text','price numeric(5,2)','maker integer'] ''' for column in columns: self.execute(self.GET_ALTER_TABLE_QUERY.format(tablename,column)) def get_table_list(self,table_type=""): ''' 登録されているテーブルの一覧を取得する Parameters ---------- table_type : str 'table' => テーブルのみ、'view' => ビューのみ、'' => テーブルとビューの両方 Returns ---------- res:str リスト形式のテーブル名一覧 <例> ['talbe1','table2','table3'] ''' res = self.execute_query(self.GET_TABLE_LIST_QUERY.format('%%',self.dbname,'%' + table_type + '%')) return [name[0] for name in res] def get_column_type(self,tablename): ''' 指定したテーブルのカラムと型を一覧で取得する Parameters ---------- tablename : str テーブル名 Returns ---------- res:str リスト形式でカラム名と型のタプルを返す <例> [('column1','int'),('column2','text'),('column3',real)] ''' res = self.execute_query(self.GET_COLUMN_LIST_QUERY.format(tablename,self.dbname)) return [(name[1],name[2]) for name in res] def get_column_list(self,tablename): ''' 指定したテーブルのカラム名を一覧で取得する Parameters ---------- tablename : str テーブル名 Returns ---------- res:str リスト形式のカラム名一覧 <例> ['column1','column2','column3'] ''' res = self.execute_query(self.GET_COLUMN_LIST_QUERY.format(tablename,self.dbname)) return [name[1] for name in res] |
補足説明
この章では、本クラスについての改良ポイント、課題について解説します。
再利用される場合は、この点についてご留意ください。
データベース固有のSQL定義
テーブルやカラムの一覧を取得するSQL はデータベースごとに異なる可能性があるため、クラスの先頭に定数（実際にはクラス変数）として定義しています。
新しいDBに対応する際は、ここを適宜書き換えてください。
| 定数名 | 内容 |
|---|---|
| GET_TABLE_LIST_QUERY | テーブルとViewの一覧を取得するためのSQLを記述 |
| GET_COLUMN_LIST_QUERY | カラムの一覧を取得するためのSQLを記述 |
| GET_ALTER_TABLE_QUERY | カラムの追加を行うためのSQLを記述 |
| GET_RENAME_TABLE_QUERY | テーブル名の変更を行うためのSQLを記述 |
テーブル一覧を取得するSQL
MySQLの場合、テーブルは INFORMATION_SCHEMA.TABLES というテーブルで管理されており、table_type でテーブルかビューを識別することが出来ます。
|
1 2 3 4 5 6 7 |
SELECT table_name,type FROM ( SELECT table_name,table_schema, CASE table_type WHEN 'VIEW' THEN 'view' ELSE 'table' END AS type FROM information_schema.tables ) t WHERE table_name LIKE '{0}' AND table_schema LIKE '{1}' AND type like '{2}' |
カラム一覧を取得するSQL
MySQLの場合、 カラム一覧は INFORMATION_SCHEMA.COLUMNS で管理されています。
|
1 2 3 4 |
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like LOWER('{0}') and TABLE_SCHEMA like LOWER('{1}') ORDER BY ORDINAL_POSITION |
カラムを追加するSQL
MySQLの場合、 カラム名は ALTER TABLE テーブル名 ADD カラム名 データ型 で追加可能です。
|
1 |
ALTER TABLE {0} ADD {1} |
テーブル名を変更するSQL
MySQLの場合、ALTER TABLE 旧テーブル名 TO 新テーブル名 で変更します。
|
1 |
ALTER TABLE {0} RENAME TO {1} |
まとめ
今回は Python で SQLite を扱うヘルパークラスを、 MySQL用に移植してみました。
テーブル一覧の取得やカラム一覧の取得、create文の取得など、たまに使いたくなる機能も合わせて実装しました。
このクラスがあれば、だいたいの事が出来ると思います。
後は皆さんの利用方法に合わせて拡張してお使いください。
この記事が皆さんの一助になれば幸いです。
コメント