mysql はじめてのインストールからの起動

１：パソコン設定より、mysql をオンにする。

 ２：「mysql -u usernameをここにかく -p 」とTerminalで打つ

 ３：quitで閉じる

www.dbonline.jp

３：データベースの操作を練習

1. データベースの操作

データベース一覧の表示

データベースの一覧を表示するクエリ。

mysql> SHOW DATABASES;

データベースの作成

twitterという名前のデータベースを作成するクエリ。

mysql> CREATE DATABASE twitter;

利用データベースの選択

twitterという名前のデータベースを利用データベースに選択するクエリ。

mysql> USE twitter;

データベースの削除

twitterという名前のデータベースを削除するクエリ。

mysql> DROP DATABASE twitter;

2. テーブルの操作

テーブル一覧の表示

テーブル一覧を表示するクエリ。

mysql> SHOW TABLES;

テーブルの作成

tweetsという名前のテーブルを作成するクエリ。
カラムは

• tweet_id(bigint)
• user_id(bigint)
• text(longtext)
• created_at(datetime)

の4つとする。

mysql> CREATE TABLE tweets (
    tweet_id BIGINT, 
    user_id BIGINT,
    text LONGTEXT,
    created_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

テーブル構造の表示

tweetsという名前のテーブル構造を表示するクエリ。

mysql> SHOW FIELDS FROM tweets;

テーブルの修正

tweetsという名前のテーブルのカラムtextの型を変更するクエリ。

mysql> ALTER TABLE tweets MODIFY text VARCHAR(1023);

カラム名を修正するクエリ。

mysql> ALTER TABLE tweets CHANGE tweet_id status_id BIGINT;

カラム名を追加するクエリ。

mysql> ALTER TABLE tweets ADD retweeted_count INT;

インデックスを追加するクエリ。

mysql> ALTER TABLE tweets ADD INDEX(user_id);

テーブルの初期化

tweetsという名前のテーブルをなかみやパラメタを削除し初期化するクエリ。

mysql> TRUNCATE TABLE tweets;

テーブル作成クエリの表示

tweetsという名前のテーブルを作成するクエリを表示するクエリ。

mysql> SHOW CREATE TABLE tweets;

同テーブル構造のテーブルの作成

tweetsという名前のテーブル構造と同じ構造のtweets2というテーブルを作成するクエリ。

mysql> CREATE TABLE tweets2 LIKE tweets;

テーブルの削除

tweetsという名前のテーブルを削除するクエリ。

mysql> DROP TABLE tweets;

 など

ーーーーーーーーーーーー

Python とつなぐためにmysql-connector をインストールする

成功したイメージ：

PythonでMySQLを操作しようと思ったら

まずドライバをインストールしなければいけません。
いろんな種類があり、紹介マニアどらふと版: Python の MySQL ドライバはどれを利用すれば良いのか に分かりやすくまとめられています。

僕はmysql-connector-pythonをチョイスしました。
特徴はMySQL公式のドライバだという点ですね。
公式がいいのか！と言われるとケースバイケースだと思いますが、最近使っているMySQL Workbenchという公式のGUIツールが使いやすかったので。

インストール

pipでいけます。

$ pip install mysql-connector-python

基本的な使い方

次のようなデータをinsertし、それをselectするコードを書いてみます。
（テーブルは作成済みとする）

student_id	name	sex
1	山田太郎	男

test.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-

# ドライバをimport
import mysql.connector

if __name__ == '__main__':
    # データベースに接続
    connect = mysql.connector.connect(user='hoge', password='hoge', host='hoge', database='hoge', charset='utf8')
    cursor = connect.cursor()

    name = '山田太郎'
    sex = '男'
などなど・・・

yuk.hatenablog.com

ーーーーーーーーーーーーーーーー

以下の参考：

http://rainingtacos.blogspot.jp/2012/09/how-to-install-mysql-python-django-and.html

エラー：Permission denied.

類似：Unable to use easy_install to install Python modules - Stack Overflow

解決方法：

https://bootstrap.pypa.io/get-pip.py これをコピーして以下の文の通り、貼り付ける。

Installation — pip 7.0.3 documentation

Installing Python Modules — Python 3.4.3 documentation

 

python get_pip.py

/var/folders/g_/tbmcnbwn6dd1y3crc7v2_ttr0000gn/T/tmpTgWaBZ/pip.zip/pip/_vendor/requests/packages/urllib3/util/ssl_.py:90: InsecurePlatformWarning: A true SSLContext object is not available. This prevents urllib3 from configuring SSL appropriately and may cause certain SSL connections to fail. For more information, see https://urllib3.readthedocs.org/en/latest/security.html#insecureplatformwarning.

Collecting pip

/var/folders/g_/tbmcnbwn6dd1y3crc7v2_ttr0000gn/T/tmpTgWaBZ/pip.zip/pip/_vendor/requests/packages/urllib3/util/ssl_.py:90: InsecurePlatformWarning: A true SSLContext object is not available. This prevents urllib3 from configuring SSL appropriately and may cause certain SSL connections to fail. For more information, see https://urllib3.readthedocs.org/en/latest/security.html#insecureplatformwarning.

  Downloading pip-7.0.3-py2.py3-none-any.whl (1.1MB)

    100% |████████████████████████████████| 1.1MB 265kB/s

Collecting wheel

  Downloading wheel-0.24.0-py2.py3-none-any.whl (63kB)

    100% |████████████████████████████████| 65kB 2.1MB/s

Installing collected packages: pip, wheel

エラー：OSError: [Errno 13] Permission denied: '/Library/Python/2.7/site-packages/pip'

解決方法：

Permission denied: '/Library/Python/2.7/site-packages/pync/vendor' · Issue #1 · sindresorhus/weechat-notification-center · GitHub

$ sudo chown -R $USER /Library/Python/2.7/site-packages    

$ python get_pip.py                                        

Collecting pip

  Using cached pip-7.0.3-py2.py3-none-any.whl

Collecting wheel

  Using cached wheel-0.24.0-py2.py3-none-any.whl

Installing collected packages: pip, wheel

Successfully installed pip-7.0.3 wheel-0.24.0

 $ pip install mysql-python                             

Collecting mysql-python

/Library/Python/2.7/site-packages/pip/_vendor/requests/packages/urllib3/util/ssl_.py:90: InsecurePlatformWarning: A true SSLContext object is not available. This prevents urllib3 from configuring SSL appropriately and may cause certain SSL connections to fail. For more information, see https://urllib3.readthedocs.org/en/latest/security.html#insecureplatformwarning.

  InsecurePlatformWarning

  Downloading MySQL-python-1.2.5.zip (108kB)

    100% |████████████████████████████████| 110kB 945kB/s

Building wheels for collected packages: mysql-python

  Running setup.py bdist_wheel for mysql-python

  Stored in directory: /Users/HOkaniwa/Library/Caches/pip/wheels/8c/0d/11/d654cad764b92636ce047897dd2b9e1b0cd76c22f813c5851a

Successfully built mysql-python

Installing collected packages: mysql-python

Successfully installed mysql-python-1.2.5

 

pipでmysql-connector-pythonをインストール

$ pip install --allow-external mysql-connector-python mysql-connector-python

blog.livedoor.jp

 

 

最後に　プログラムを書いてみる

#coding: utf8                                                                                   

import MySQLdb

import mysql.connector

config = {

    'database': 'testtwitter',

    'user': 'root',

    'password': 'passwordformysql',

    'host': 'localhost'

}

dbcon = mysql.connector.connect(

    database=config.database,

    user=config.user,

    password=config.password,

    host=config.host

)

dbcur = dbcon.cursor()

 -----------------------------

 

 

 発展編：パス、virtual env

SEP

9

 

How to install MySQL-python, Django and Virtualenv on Mac OS X 10.8 (Mountain Lion)

I spent many hours piecing together a solution to setup a Django development environment, here are the results of my research, hopefully this can save you some time.

This guide will help you install:

• Homebrew
• Python (Separate from the default installation that comes with OS X, I'll explain why this is recommend in a sec.)
• MySQL
• Django
• Virtualenv
• Yolk (A nice plugin that you might like)

Read past the jump.

Installing Homebrew

Open up Terminal and enter this command:

ruby <(curl -fsSkL raw.github.com/mxcl/homebrew/go)

Tip: Make sure you have XCode installed, and the "Command Line Tools" from XCode > Preferences > Downloads. It is required to install Python.

Installing Python 2.7.x
Why you should have a separate python installation, quoted straight from python.org, "Python comes pre-installed on Mac OS X, but due to Apple's release cycle, it's often one or even two years old. The overwhelming recommendation of the "MacPython" community is to upgrade your Python by downloading and installing a newer version from the Python standard release page."

Tip: If you are on Mountain Lion, you may be required to have XQuartz installed. Go download and install it from here if the commands below gives you that error.

The following command with install all the prerequisites for Python:

brew install readline sqlite gdbm

Now, to install Python with Homebrew, type this into the terminal:

brew install python --universal --framework

Switching from the default Python to brewed Python
Now that we have a separate Python installation from Homebrew, let's use it! Right now, using the command "python" in Terminal will utilize the default python installation. To use the one we just installed with Homebrew, we have to change the $PATH variable. The default Python installation is at "/usr/bin", the one we want to use is at "/usr/local/bin". We have to tell OS X to look in "/usr/local/bin" first, when we run "python".

Run this command:

sudo nano /etc/paths

Now, you should see something like this:

/usr/bin
/bin
/usr/sbin
/sbin
/usr/local/bin

Move "/usr/local/bin" up to the top so that it is infront of "/usr/bin". It should look something like this:

/usr/local/bin
/usr/bin
/bin
/usr/sbin
/sbin

Press "^X" and "Y" then "Enter" to save and exit.

Log out of your computer and log back in so it refreshes the PATH variable.

Run this command to make sure everything is set:

which python

It should return "/usr/local/bin/python"
X　ここが変更できない。

 

Installing Pip
Pip is pretty much a improved version of easy_install, allowing you to manage Python packages. Run this command into the Terminal to install:

easy_install pip

Installing Virtualenv
Virtualenv allows you to have a isolated environment where you can install Python packages without contaminating your main Python installation. Run this command:

pip install virtualenv

Move to a directory where you want to setup a environment, I am going to use "~/tutorial" as our environment directory. It can be located wherever you want and be called whatever you want. 

Assuming "~/tutorial" doesn't exist yet, we are going to type a list of commands:

cd ~
mkdir tutorial
cd tutorial

To create a enviornment, run this command:

virtualenv new_environment --no-site-packages

You can change "new_environment" to whatever you like. Now we have to activate the environment, run this command:

source new_environment/bin/activate

Tip: To leave the environment, just type "deactivate".

Installing MySQL
Go to http://dev.mysql.com/downloads/mysql/ and download the DMG archive. (I prefer this over installing via Homebrew because this also gives you the PrefPane which I find quite useful.)

After MySQL is installed, we have to add it to our PATH. Run this command:

sudo nano /etc/paths

This should open the familiar file we had eariler. Add "/usr/local/mysql/bin" to the end of the file, it should look something like this:

/usr/local/bin
/usr/bin
/bin
/usr/sbin
/sbin
/usr/local/mysql/bin

Log out and back in to refresh the PATH variable.  Run "which mysql_config" in Terminal, if it returns "/usr/local/mysql/bin/mysql_config", then you are good to go.

Installing MySQL-python
As you probably already know, to use MySQL in python you need MySQL-python. Go activate the environment again("source new_environment/bin/activate") and run this command:

pip install mysql-python

To test the installation, run "python", then "import MySQLdb"(case sensitive!). If no errors show up, congratulations  You finished installing everything you need to develop a Django application!

 

 

 

 

 他のリンク：

Installing Python Modules — Python 3.4.3 documentation

 

qiita.com