Contents
MySQL をインストール
ローカルの環境(Mac)に MySQL をインストール
ChenLab-MacBookAir-3:~ chen$ brew install mysql
==> Installing dependencies for mysql: openssl
==> Installing mysql dependency: openssl
==> Downloading https://homebrew.bintray.com/bottles/openssl-1.0.2o_2.high_sierra.bottle.tar.gz
######################################################################## 100.0%
==> Pouring openssl-1.0.2o_2.high_sierra.bottle.tar.gz
==> Caveats
A CA file has been bootstrapped using certificates from the SystemRoots
keychain. To add additional certificates (e.g. the certificates added in
the System keychain), place .pem files in
/usr/local/etc/openssl/certs
and run
/usr/local/opt/openssl/bin/c_rehash
This formula is keg-only, which means it was not symlinked into /usr/local,
because Apple has deprecated use of OpenSSL in favor of its own TLS and crypto libraries.
If you need to have this software first in your PATH run:
echo ‘export PATH=”/usr/local/opt/openssl/bin:$PATH”‘ >> ~/.bash_profile
For compilers to find this software you may need to set:
LDFLAGS: -L/usr/local/opt/openssl/lib
CPPFLAGS: -I/usr/local/opt/openssl/include
==> Summary
? /usr/local/Cellar/openssl/1.0.2o_2: 1,792 files, 12.3MB
==> Installing mysql
==> Downloading https://homebrew.bintray.com/bottles/mysql-8.0.11.high_sierra.bottle.tar.gz
######################################################################## 100.0%
==> Pouring mysql-8.0.11.high_sierra.bottle.tar.gz
==> /usr/local/Cellar/mysql/8.0.11/bin/mysqld –initialize-insecure –user=chen –basedir=/usr/local/Cell
==> Caveats
We’ve installed your MySQL database without a root password. To secure it run:
mysql_secure_installation
MySQL is configured to only allow connections from localhost by default
To connect run:
mysql -uroot
To have launchd start mysql now and restart at login:
brew services start mysql
Or, if you don’t want/need a background service you can just run:
mysql.server start
==> Summary
? /usr/local/Cellar/mysql/8.0.11: 254 files, 232.6MB
ChenLab-MacBookAir-3:~ chen$
インストールが終わったら、内容を見てみる
ChenLab-MacBookAir-3:~ chen$ brew info mysql
mysql: stable 8.0.11 (bottled)
Open source relational database management system
https://dev.mysql.com/doc/refman/8.0/en/
Conflicts with:
mariadb (because mysql, mariadb, and percona install the same binaries.)
mariadb-connector-c (because both install plugins)
mysql-cluster (because mysql, mariadb, and percona install the same binaries.)
mysql-connector-c (because both install MySQL client libraries)
percona-server (because mysql, mariadb, and percona install the same binaries.)
/usr/local/Cellar/mysql/8.0.11 (254 files, 232.6MB) *
Poured from bottle on 2018-06-25 at 13:33:16
From: https://github.com/Homebrew/homebrew-core/blob/master/Formula/mysql.rb
==> Dependencies
Build: cmake ✘
Required: openssl ✔
==> Requirements
Required: macOS >= 10.10 ✔
==> Options
–with-debug
Build with debug support
–with-embedded
Build the embedded server
–with-local-infile
Build with local infile loading support
–with-memcached
Build with InnoDB Memcached plugin
–with-test
Build with unit tests
==> Caveats
We’ve installed your MySQL database without a root password. To secure it run:
mysql_secure_installation
MySQL is configured to only allow connections from localhost by default
To connect run:
mysql -uroot
To have launchd start mysql now and restart at login:
brew services start mysql
Or, if you don’t want/need a background service you can just run:
mysql.server start
ChenLab-MacBookAir-3:~ chen$
MySQL動作確認
MySQLを起動して動作確認する。付いてにテスト用のデータベースを用意しておく。
ChenLab-MacBookAir-3:~ chen$ mysql.server start
Starting MySQL
.. SUCCESS!
ChenLab-MacBookAir-3:~ chen$ $ mysql -uroot
-bash: $: command not found
ChenLab-MacBookAir-3:~ chen$ mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.11 Homebrew
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> CREATE DATABASE mysqltest DEFAULT CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.01 sec)
mysql> CREATE USER hoge@localhost IDENTIFIED BY ‘password’;
Query OK, 0 rows affected (0.10 sec)
mysql> GRANT ALL ON mysqltest.* TO hoge@localhost;
Query OK, 0 rows affected (0.01 sec)
mysql> \q
Bye
ChenLab-MacBookAir-3:~ chen$
MySQLに接続するパッケージ
Python3でMySQLに接続する方法についてですが、mysqlclinetを使う方法がオススメらしい。さっそくpipでインストールしましょう。
pip install mysqlclient
しかしインストールがエラーで終わらなかった。違う方法で挑戦。
ChenLab-MacBookAir-3:work-Python chen$ pip install mysql-connector-python-rf
Collecting mysql-connector-python-rf
Downloading https://files.pythonhosted.org/packages/21/79/2ff01ab7aa08db3a16b70b990c579c1024c6b2a734263cc7513a758867de/mysql-connector-python-rf-2.2.2.tar.gz (11.9MB)
100% |████████████████████████████████| 11.9MB 777kB/s
Building wheels for collected packages: mysql-connector-python-rf
Running setup.py bdist_wheel for mysql-connector-python-rf … done
Stored in directory: /Users/chen/Library/Caches/pip/wheels/87/58/fb/d95c84fad7e1bebfed324c13e107ebb08e1997c9226532859a
Successfully built mysql-connector-python-rf
Installing collected packages: mysql-connector-python-rf
Successfully installed mysql-connector-python-rf-2.2.2
ChenLab-MacBookAir-3:work-Python chen$
ChenLab-MacBookAir-3:work-Python chen$ pip list
Package Version
————————- ——-
mysql-connector-python-rf 2.2.2
nose 1.3.7
numpy 1.14.3
pip 10.0.1
setuptools 39.1.0
TBB 0.1
wheel 0.31.0
ChenLab-MacBookAir-3:work-Python chen$
PythonからMySQLに接続
Pythonのプログラムは、上記で作成したデーターベースに「booklist」というテーブルを作成し、そこにPythonという本のデーターを追加するという内容だ。
# -*- coding: utf-8 -*-
#import MySQLdb
#
#conn = MySQLdb.connect(db='mysqltest',user='hoge',passwd='password',charset='utf8mb4')
import mysql.connector
conn = mysql.connector.connect(user='root', password='', host='localhost', database='mysqltest')
c = conn.cursor()
#tableが既にある場合は一回削除します
c.execute('DROP TABLE IF EXISTS booklist')
#tableを作成します
c.execute('''
CREATE TABLE booklist(
id integer,
name text,
kakaku integer
)
''')
#tableにデータを入れます
c.execute('INSERT INTO booklist VALUES(%s,%s,%s)',(1,'Python',2400))
conn.commit()
c.execute('SELECT * FROM booklist')
for row in c.fetchall():
print(row)
conn.close()
出力も短い一行だけ。
ChenLab-MacBookAir-3:work-Python chen$ python mysql-test.py
(1, u’Python’, 2400)