Python3(MySQLと連携メモ)

インストール

sudo apt-get install mysql-server
sudo pip3 install PyMySQL

コマンド

#ユーザ「root」、パスワード「password」で接続
mysql -u root -ppassword

MySQLコマンド

mysql> CREATE DATABASE scraping;
mysql> USE scraping;
mysql> CREATE TABLE pages ( id BIGINT(7) NOT NULL AUTO_INCREMENT ,
                            title VARCHAR(200) ,
                            content VARCHAR(10000) ,
                            created TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
                            PRIMARY KEY(id));
mysql> DESCRIBE pages;
+---------+----------------+------+-----+-------------------+----------------+
| Field   | Type           | Null | Key | Default           | Extra          |
+---------+----------------+------+-----+-------------------+----------------+
| id      | bigint(7)      | NO   | PRI | NULL              | auto_increment |
| title   | varchar(200)   | YES  |     | NULL              |                |
| content | varchar(10000) | YES  |     | NULL              |                |
| created | timestamp      | NO   |     | CURRENT_TIMESTAMP |                |
+---------+----------------+------+-----+-------------------+----------------+

mysql> INSERT INTO pages (title,content) VALUES ("Test page title" , "This is some test page content.");
mysql> SELECT * FROM pages;
+----+-----------------+---------------------------------+---------------------+
| id | title           | content                         | created             |
+----+-----------------+---------------------------------+---------------------+
|  1 | Test page title | This is some test page content. | 2016-10-16 21:41:49 |
+----+-----------------+---------------------------------+---------------------+

mysql> SELECT * FROM pages WHERE id = 1;
+----+-----------------+---------------------------------+---------------------+
| id | title           | content                         | created             |
+----+-----------------+---------------------------------+---------------------+
|  1 | Test page title | This is some test page content. | 2016-10-16 21:41:49 |
+----+-----------------+---------------------------------+---------------------+

mysql> SELECT id,title FROM pages WHERE content LIKE "%page content%";
+----+-----------------+
| id | title           |
+----+-----------------+
|  1 | Test page title |
+----+-----------------+

Python3による操作

import pymysql

conn = pymysql.connect(host='127.0.0.1',
                       user='root',
                       password='password',
                       db='mysql',
                       charset='utf8')

cur = conn.cursor()
cur.execute("USE scraping")
cur.execute("SELECT * FROM pages WHERE id = 1")
print(cur.fetchone())
cur.close()
conn.close()

# (1, 'Test page title', 'This is some test page content.', datetime.datetime(2016, 10, 16, 21, 41, 49))