mysql&pymysql

ubuntu系统环境

• Mysql安装

  $sudo apt-get install mysql-server

  安装过程中会有安装提示为root用户设置新密码

• 安装过程中会提示设置密码什么的，注意设置了不要忘了，安装完成之后可以使用如下命令来检查是否安装成功：
  $sudo netstat -tap | grep mysql

• 如何启动/停止/重启MySQL

  使用 service 启动：$service mysql start
  使用 service 停止：$service mysql stop
  使用 service 重启：$service mysql restart

• 登录mysql
  登陆mysql数据库可以通过如下命令：$mysql -u root -p
  -u 表示选择登陆的用户名， -p 表示登陆的用户密码，上面命令输入之后会提示输入密码，此时输入密码就可以登录到mysql。

• 查看端口号
  mysql> show global variables like 'port';
• 修改端口，编辑/etc/my.cnf文件，早期版本有可能是my.conf文件名，增加端口参数，并且设定端口，注意该端口未被使用，保存退出。

[root@test etc]# vi my.cnf  
[mysqld]  
port=3506  
datadir=/var/lib/mysql  
socket=/var/lib/mysql/mysql.sock  
user=mysql  
# Disabling symbolic-links is recommended to prevent assorted security risks  
symbolic-links=0  

[mysqld_safe]  
log-error=/var/log/mysqld.log  
pid-file=/var/run/mysqld/mysqld.pid  

"my.cnf" 11L, 261C written  
[root@test etc]#  

• 修改新密码
  在终端输入：mysql -u用户名 -p密码，回车进入Mysql。

use mysql; 
update user set password=PASSWORD(‘新密码’) where user=’用户名’; 
flush privileges; #更新权限 
quit; #退出

• 数据库操作
  显示所有的数据库 : mysql> show databases;
  创建数据库 :mysql> create database test;
  连接数据库 :mysql> use test;
  查看当前使用的数据库 :mysql> select database();
  当前数据库包含的表信息 :mysql> show tables;
  删除数据库:mysql> drop database test;

• 表操作
  建表 :命令:create table <表名> (<字段名 1> <类型 1> [,..<字段名 n> <类型 n>]);
  mysql> create table MyClass(id int(4) not null primary key auto_increment,
name char(20) not null,
sex int(4) not null default ‘0’,
degree double(16,2));

  获取表结构 :命令: desc 表名，或者show columns from 表名

mysql> describe MyClass;
or
mysql> desc MyClass; 
or
mysql> show columns from MyClass;

• 删除表 :命令:drop table <表名>
  删除表名为 MyClass 的表 mysql> drop table MyClass;

• 插入数据 :命令:insert into <表名> [( <字段名 1>[,..<字段名 n > ])] values ( 值 1 )[, ( 值 n )]
  mysql> insert into MyClass values(1,’Tom’,96.45),(2,’Joan’,82.99), (2,’Wang’, 96.59);
or
mysql> insert into pages (title, content) values ('Test page title',
'This is some test page content. It can be up to 10,000 characters long.');

• 查询表中的数据
  查询所有行 :mysql> select * from MyClass;

• 查询前几行数据
  查看表 MyClass 中前 2 行数据 :mysql> select * from MyClass order by id limit 0,2;
  或者
  mysql> select * from MyClass limit 0,2;

• 删除表中数据
  命令:delete from 表名 where 表达式
  删除表 MyClass 中编号为 1 的记录 :mysql> delete from MyClass where id=1;

• 修改表中数据
  命令:update 表名 set 字段=新值,… where 条件
  mysql> update MyClass set name=’Mary’ where id=1;

• 在表中增加字段
  命令:alter table 表名 add 字段 类型 其他;
  在表 MyClass 中添加了一个字段 passtest,类型为 int(4),默认值为 0
  mysql> alter table MyClass add passtest int(4) default ‘0’;

• 更改表名
  命令:rename table 原表名 to 新表名;
  在表 MyClass 名字更改为 YouClass :mysql> rename table MyClass to YouClass;

• 更新字段内容
  命令:update 表名 set 字段名 = 新内容
  update 表名 set 字段名 = replace(字段名, ‘旧内容’, ‘新内容’);
  例如:文章前面加入 4 个空格
  update article set content=concat(’ ‘, content);

---

• python整合
  安装PyMySQL:$ pip install PyMySQL

import pymysql
conn = pymysql.connect(host='127.0.0.1', unix_socket='/tmp/mysql.sock',
                       user='root', passwd=None, db='mysql')
cur = conn.cursor()
cur.execute("USE scraping")
cur.execute("SELECT * FROM pages WHERE id=1")
print(cur.fetchone())
cur.close()
conn.close()

如有报错pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on '127.0.0.1'
则unix_socket='/tmp/mysql.sock'修改为:
unix_socket='/var/run/mysqld/mysqld.sock'

---

• 让数据库支持Unicode:

alter database scraping character set = utf8mb4 collate = utf8mb4_unicode_ci;
alter table pages convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table pages change title title varchar(200) character set utf8mb4 collate utf8mb4_unicode_ci;
alter table pages change content content varchar(10000) character set utf8mb4 collate utf8mb4_unicode_ci;

• 用数据库存储维基百科的信息

from urllib.request import urlopen
from bs4 import BeautifulSoup
import re
import datetime
import random
import pymysql

conn = pymysql.connect(host='127.0.0.1', unix_socket='/var/run/mysqld/mysqld.sock', 
user='root', passwd=None, db='mysql', charset='utf8')
cur = conn.cursor()
cur.execute("USE scraping")

random.seed(datetime.datetime.now())

def store(title, content):
    cur.execute("INSERT INTO pages (title, content) VALUES (\"%s\",\"%s\")", (title, content))
    cur.connection.commit()

def getLinks(articleUrl):
    html = urlopen("http://en.wikipedia.org"+articleUrl)
    bsObj = BeautifulSoup(html, "html.parser")
    title = bsObj.find("h1").get_text()
    content = bsObj.find("div", {"id":"mw-content-text"}).find("p").get_text()
    store(title, content)
    return bsObj.find("div", {"id":"bodyContent"}).findAll("a", href=re.compile("^(/wiki/)((?!:).)*$"))

links = getLinks("/wiki/Kevin_Bacon")
try:
    while len(links) > 0:
         newArticle = links[random.randint(0, len(links)-1)].attrs["href"]
         print(newArticle)
         links = getLinks(newArticle)
finally:
    cur.close()
    conn.close()

---

• MySQL导入导出CSV格式数据
• MySQL中导出CSV格式数据的SQL语句样本如下：

select * from test_info into outfile '/tmp/test.csv'   
fields terminated by ',' optionally enclosed by '"' 
escaped by '"' lines terminated by '\r\n';   

• 运行上面代码会有如下报错：
  ERROR 1 (HY000): Can't create/write to file

• 解决办法：

Try the following:

mkdir /var/lib/mysql/tmp
chown mysql:mysql /var/lib/mysql/tmp
Add the following line into the [mysqld] section: tmpdir = /var/lib/mysql/tmp
Restart the server

or

1、mkdir /var/lib/mysql/tmp
2、chown mysql:mysql /var/lib/mysql/tmp     

3、修改/etc/my.cnf 配置文件，在[mysql]段增加：tmpdir = /var/lib/mysql/tmp
增加后，大致如下：

[mysqld]
port            = 3306
socket          = /var/run/mysql/mysql.sock
# Change following line if you want to store your database elsewhere
datadir = /var/lib/mysql
tmpdir = /var/lib/mysql/tmp
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

4、重启mysql服务。

https://stackoverflow.com/questions/981029/mysql-error-1-hy000-trouble-creating-file-errcode-2
http://blog.sina.com.cn/s/blog_9ffcd5dc01017wyz.html

• MySQL中导入CSV格式数据的SQL语句样本如下：

load data infile '/tmp/test.csv'   
into table test_info    
fields terminated by ','  optionally enclosed by '"' escaped by '"'   
lines terminated by '\r\n';   

里面最关键的部分就是格式参数

fields terminated by ',' optionally enclosed by '"' escaped by '"'   
lines terminated by '\r\n'   

这个参数是根据RFC4180文档设置的，其中详细描述了CSV格式，其要点包括：
(1)字段之间以逗号分隔，数据行之间以\r\n分隔；
(2)字符串以半角双引号包围，字符串本身的双引号用两个双引号表示。

• 文件：test_csv.sql

use test;  

create table test_info (  
    id  integer not null,  
    content varchar(64) not null,  
    primary key (id)  
);  

delete from test_info;  

insert into test_info values (2010, 'hello, line  
suped  
seped  
"  
end'  
);  

select * from test_info;  

select * from test_info into outfile '/tmp/test.csv' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';  

delete from test_info;  

load data infile '/tmp/test.csv' into table test_info  fields terminated by ','  optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';  

select * from test_info;  

• 文件：test.csv

2010,"hello, line  
suped  
seped  
""  
end" 

• ubuntu 下mysql导入出.sql文件

  1. 导出整个数据库
     　　mysqldump -u 用户名 -p 数据库名 > 导出的文件名
　　mysqldump -u root -p original_database > original.sql
     　　
     　　1. 导出一个表
     　　mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
　　mysqldump -u root -p original_database users> dc_users.sql
     　　
     　　2. 通过加‘-d’参数，可以实现只导出建表指令
     　　mysqldump -uroot -p -d original_database > original2.sql
     　　3. 通过加‘-t’参数，可以实现只导出插入数据的sql命令。
     　　mysqldump -uroot -p -t original_database > original3.sql
     　　
  2. 导入数据库
     　　1. 导入的话,有很多种方法,其中最单的就是source命令,先用连接上数据库.然后用source导入指定路径的文件就可以了.
     　　连接到MySQL：进入mysql数据库控制台mysql -u root -p
     　　2. 先创建好数据库,因为导出的文件里没有创建数据库的语句,如果数据库已经建好,则不用再创建。
     　　CREATE DATABASE example;（数据库名可以不一样）
     　　3. 切换数据库：
     　　mysql>use 数据库;
     　　4. 导入指定sql文件：
     　　mysql>source /home/zx/test.sql;
     　　5. 或直接导入命令为：
     　　mysql -u root -p target_database < original.sql

---