engine = create_engine('mysql+mysqldb://root:password@127.0.0.1:3306/dbname')

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

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.pool import NullPool

engine = create_engine('mysql+mysqldb://root:password@127.0.0.1:3306/dbname', poolclass=NullPool)
Session = sessionmaker(bind=engine)
session = Session()
usr_obj_list = session.query(UsrObj).all()
print usr_obj_list[0].id
session.close()

engine = create_engine('postgresql://postgres@127.0.0.1/dbname')

ssh-keygen -t rsa

scp ~/.ssh/id_rsa.pub username@hostname:~/.ssh/authorized_keys
# or
ssh-copy-id -i ~/.ssh/id_rsa.pub username@hostname

cat id_rsa.pub >> .ssh/authorized_keys

vim /etc/ssh/sshd_config

ClientAliveInterval 30
ClientAliveCountMax 60

sudo service ssh restart

sudo apt-get install openssh-server

vim /etc/ssh/ssh_config

ServerAliveInterval 30
ServerAliveCountMax 60

ControlMaster auto
ControlPath ~/.ssh/connection-%r@%h:%p

ControlPersist 4h

ForwardAgent yes

Host *
	ForwardAgent yes
	ServerAliveInterval 3
	ServerAliveCountMax 20
	TCPKeepAlive no
	ControlMaster auto
	ControlPath ~/.ssh/connection-%r@%h:%p
	ControlPersist 4h
	Compression yes

sudo apt update
sudo apt-get install mysql-server
sudo mysql_secure_installation

sudo mysql_secure_installation

sudo mysql -u root -p

sudo /etc/init.d/mysql {start | stop | status | restart}

sudo service mysql {start | stop | status | restart}

net start mysql

net start mssqlserver
# 或者重启 tomcat
net start tomcat6

mysql -u [root] -p

SHOW DATABASES;

mysql> show DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| parker             |
| performance_schema |
| sys                |
| test               |
| youku              |
+--------------------+
7 rows in set (0.00 sec)

CREATE DATABASE dbname;

DROP DATABASE dbname;

USE dbname;

SHOW tables;

CREATE TABLE table_name (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) , signup_date DATE);

DESCRIBE table_name;

ALTER TABLE table_name **ADD COLUMN** [column] VARCHAR(40);

ALTER TABLE [table] ADD COLUMN [column] int NOT NULL AUTO_INCREMENT PRIMARY KEY;

ALTER TABLE table_name **ADD** email VARCHAR(40) AFTER name;

ALTER TABLE table_name DROP column

ALTER TABLE tablename **MODIFY COLUMN** column_name VARCHAR(20);
ALTER TABLE tablename **ALTER COLUMN** column_name VARCHAR(20);

ALTER TABLE [table]  ADD primary key(column1, column2);

ALTER TABLE [table]  DROP PRIMARY KEY, ADD primary key(column1, column2);

ALTER TABLE origin_table_name RENAME TO new_table_name

ALTER TABLE table_name AUTO_INCREMENT = 1000;

INSERT INTO `table_name` (`name`, `signup_date`) VALUES ("Verne", "2017-05-01");

UPDATE [table] SET [column] = 'Y' WHERE `potluck`.`name` ='Sandy';

DELETE from table_name where column_name = "value";

SELECT COUNT([column]) FROM [table];

SELECT * FROM [table] WHERE [column] LIKE '%value%';

SELECT * FROM [table] WHERE [column] ORDER BY [column] ASC LIMIT [value];

TRUNCATE table [table]

show create table [table_name];

SHOW INDEX FROM [table];

mysqldump -u [username] -p [database] > db_backup.sql
mysqldump -u [username] -p [database] [table_name] > db_backup.sql

mysql -u [username] -p [database] < db_backup.sql
mysql -u [username] -p -h localhost [database] < db_backup.sql

SELECT User,Host FROM mysql.user;

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

mysql> set global validate_password_policy=0;

mysql> select @@validate_password_length;

GRANT ALL ON [database].* TO 'user'@'localhost';

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' WITH GRANT OPTION;

mysqladmin -u root -p old_password password new_password

DROP USER 'user1'@'localhost';

uninstall plugin validate_password;
// MySQL 8.0.4 以上
UNINSTALL COMPONENT 'file://component_validate_password';

alter table old_db.table_name rename new_db.table_name

mysql -h host -u user -p < batch-file

#bind-address = 127.0.0.1

mysql -u root –p
mysql>use mysql;
mysql>update user set host = '%' where user = 'root';
mysql>select host, user from user;

SHOW VARIABLES LIKE 'validate_password%';

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)

SET GLOBAL validate_password_policy=MEDIUM;        // LOW  MEDIUM HIGH
SET GLOBAL validate_password_policy=1;        // Low=0 Medium=1 High=2

mysql> CREATE USER 'einverne'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'einverne'@'localhost'
	->     WITH GRANT OPTION;
mysql> CREATE USER 'einverne'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'einverne'@'%'
	->     WITH GRANT OPTION;

FLUSH PRIVILEGES;

ERROR 1698 (28000): Access denied for user 'root'@'localhost'
MariaDB [(none)]> SELECT User, Host, plugin FROM mysql.user;
+------+-----------+-------------+
| User | Host      | plugin      |
+------+-----------+-------------+
| root | localhost | unix_socket |
+------+-----------+-------------+
1 row in set (0.00 sec)

sudo mysql -u root
mysql> USE mysql;
mysql> UPDATE user set plugin='mysql_native_password' WHERE user='root';
mysql>FLUSH PRIVILEGES;
mysql>exit

sudo service mysql restart

$ sudo mysql -u root # I had to use "sudo" since is new installation

mysql> USE mysql;
mysql> CREATE USER 'YOUR_SYSTEM_USER'@'localhost' IDENTIFIED BY 'YOUR_PASSWD';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'YOUR_SYSTEM_USER'@'localhost';
mysql> UPDATE user SET plugin='auth_socket' WHERE User='YOUR_SYSTEM_USER';
mysql> FLUSH PRIVILEGES;
mysql> exit;

$ sudo service mysql restart

mysql> help
  ...
  \g  go  Send command to mysql server.
  \G  ego Send command to mysql server, display result vertically.
  ...

man mysql...
  ...
  --vertical, -E
  Print query output rows vertically (one line per column value).
  Without this option, you can specify vertical output for individual
  statements by terminating them with \G.
  ...

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

import MySQLdb

"""
pip install MySQL-python
MySQLdb 是用于 Python 链接 Mysql 数据库的接口，它实现了 Python 数据库 API 规范 V2.0，基于 MySQL C API 上建立的。

在使用 Python 连接之前确保已经有数据表建立

> mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.7.18-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2017, 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> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| spy                |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY '12345678';
Query OK, 0 rows affected (0.01 sec)

mysql> USE testdb;
Database changed
mysql> GRANT ALL ON testdb.* TO 'testuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

"""

con = MySQLdb.connect('localhost', 'testuser', '12345678', 'testdb', charset='utf8')


def create_table():
    with con:
        cur = con.cursor()
        cur.execute("DROP TABLE IF EXISTS Users")
        cur.execute("CREATE TABLE Users(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50))")


def insert(name):
    with con:
        cur = con.cursor()
        cur.execute("INSERT INTO Users(name) VALUES('" + name + "')")


def get():
    with con:
        cur = con.cursor()
        cur.execute("SELECT * FROM Users")

        rows = cur.fetchall()
        for row in rows:
            print row


def update(old_name, new_name):
    with con:
        cur = con.cursor()
        cur.execute("UPDATE Users SET name = %s WHERE name = %s", (new_name, old_name))


def transaction():
    try:
        con.cursor()
        insert("CC")
        insert("David")
        insert("Einstein")
        con.commit()
    except MySQLdb.Error, e:
        if con:
            con.rollback()
        print "Error %d: %s" % (e.args[0],e.args[1])


if __name__ == '__main__':

    try:

        cur = con.cursor()
        sql = "SELECT VERSION()"
        cur.execute(sql)

        version = cur.fetchone()
        print "MySQL version : %s " % version
    except MySQLdb.Error, e:
        print "Error %d: %s" % (e.args[0], e.args[1])
        if con:
            con.close()

    create_table()
    insert("Alex")
    insert("Verne")
    get()
    update("Alex", "Bob")
    get()
    transaction()
    get()
    if con:
        con.close()

pip install mycli

mycli [OPTIONS] [DATABASE]

Options:
  -h, --host TEXT               Host address of the database.
  -P, --port INTEGER            Port number to use for connection. Honors
                                $MYSQL_TCP_PORT
  -u, --user TEXT               User name to connect to the database.
  -S, --socket TEXT             The socket file to use for connection.
  -p, --password TEXT           Password to connect to the database
  --pass TEXT                   Password to connect to the database
  --ssl-ca PATH                 CA file in PEM format
  --ssl-capath TEXT             CA directory
  --ssl-cert PATH               X509 cert in PEM format
  --ssl-key PATH                X509 key in PEM format
  --ssl-cipher TEXT             SSL cipher to use
  --ssl-verify-server-cert      Verify server's "Common Name" in its cert
                                against hostname used when connecting. This
                                option is disabled by default
  -v, --version                 Version of mycli.
  -D, --database TEXT           Database to use.
  -R, --prompt TEXT             Prompt format (Default: "\t \u@\h:\d> ")
  -l, --logfile FILENAME        Log every query and its results to a file.
  --defaults-group-suffix TEXT  Read config group with the specified suffix.
  --defaults-file PATH          Only read default options from the given file
  --myclirc PATH                Location of myclirc file.
  --auto-vertical-output        Automatically switch to vertical output mode
                                if the result is wider than the terminal
                                width.
  -t, --table                   Display batch output in table format.
  --csv                         Display batch output in CSV format.
  --warn / --no-warn            Warn before running a destructive query.
  --local-infile BOOLEAN        Enable/disable LOAD DATA LOCAL INFILE.
  --login-path TEXT             Read this path from the login file.
  -e, --execute TEXT            Execute query to the database.
  --help                        Show this message and exit.

pip install scrapy-redis

from scrapy_redis.spiders import RedisSpider

class MySpider(RedisSpider):
    name = 'myspider'

    def parse(self, response):
        # do stuff
        pass

apt-get update && apt-get upgrade	# On Debian Systems

wget 'http://www.vmware.com/go/tryworkstation-linux-64'

chmod +x VMware-Workstation-Full-12.5.5-5234757.x86_64.bundle

./VMware-Workstation-Full-12.5.5-5234757.x86_64.bundle

pip install Flask-RQ2

from flask_rq2 import RQ

rq = RQ()

@rq.job
def add(x, y):
    return x + y

flask rq worker

job = add.queue(1, 2)
job2 = add.queue(3, 4, queue='high', timeout=60 * 2)

@rq.job
def add(x, y):
    return x + y

# queue job in 60 seconds
add.schedule(timedelta(seconds=60), 1, 2)

# queue job at a certain datetime (UTC!)
add.schedule(datetime(2016, 12, 31, 23, 59, 59), 1, 2)

# queue job in 14 days and then repeat once 14 days later
add.schedule(timedelta(days=14), 1, 2, repeat=1)

# queue job in 12 hours with a different queue
add.schedule(timedelta(hours=12), 1, 2, queue='high', timeout=60 * 2)

# queue job every day at noon (UTC!)
add.cron('0 0 12 * * ?', 'add-one-two', 1, 2)

# queue job every minute with a different queue
add.cron('* * * * *', 'add-one-two', 1, 2, queue='high', timeout=55)

redis.exceptions.ResponseError: Command # 7 (EXPIRE rq:worker:ev.22880 None) of pipeline caused error: value is not an integer or out of range

tcpdump

tcpdump -i eth1 port 25

tcpdump -i eth1 net 192.168

tcpdump -vv -i eth0

tcpdump tcp

tcpdump udp or port 80

tcpdump -i eth1 host 192.168.1.1

tshark -i eth0

tshark tcp dst port 80

python -V
Python 2.7.12

sudo pip install virtualenv
source ./bin/active # 开启
# 此后再使用 pip install 时会安装在独立的目录下

sudo apt-get install libxml2-dev libxslt1-dev python-dev
pip install scrapy

scrapy startproject demo

$ tree demo
demo
├── demo
│   ├── __init__.py
│   ├── items.py
│   ├── middlewares.py
│   ├── pipelines.py
│   ├── settings.py
│   └── spiders
│       └── __init__.py
└── scrapy.cfg

2 directories, 7 files

cd demo
scrapy genspider example example.com   # 使用该命令安装模板生成 Spider

import scrapy
from scrapy.pipelines.images import ImagesPipeline

class ImagePipeline(ImagesPipeline):
    default_headers = {
        'user-agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36'
    }

    # 对各个图片 URL 返回一个 Request
    def get_media_requests(self, item, info):
        try:
            for image_url in item['image_urls']:
                f = image_url.split('.')[-1]
                yield scrapy.Request(image_url, meta={'image_name': item['image_name'], 'format': f}, headers=self.default_headers)
        except Exception as error:
            print error

    # 当一个单独项目中的所有图片请求完成时 (success, image_info_or_failure)
    def item_completed(self, results, item, info):
        image_paths = [x['path'] for ok, x in results if ok]
        if not image_paths:
            # raise DropItem("Item contains no images")
            print "Image path no exist"
        return item


    # Override the convert_image method to disable image conversion

    # scrapy convert image to jpg 重写此方法，可以下载自定的图片格式，不过可能需要特殊处理格式
    # def convert_image(self, image, size=None):
    #     buf = StringIO()
    #     try:
    #         image.save(buf, image.format)
    #     except Exception, ex:
    #         raise ImageException("Cannot process image. Error: %s" % ex)
    #
    #     return image, buf

    # 默认情况下，使用 ImagePipeline 组件下载图片的时候，图片名称是以图片 URL 的 SHA1 值进行保存的。
    # scrapy 0.12 可以覆盖 image_key 方法，在此后版本中 使用 file_path 来自定义下载图片名称
    # def image_key(self, url):
    #     image_guid = hashlib.sha1(url).hexdigest()
    #     return 'full/%s.jpg' % (image_guid)

    # http://stackoverflow.com/questions/6194041/scrapy-image-download-how-to-use-custom-filename/22263951#22263951
    def file_path(self, request, response=None, info=None):
        name = request.meta['image_name']
        f = request.meta['format']
        return 'full/%s.jpg' % name

import random

from scrapy.downloadermiddlewares.useragent import UserAgentMiddleware

from scrapy.conf import settings


class RandomUserAgentMiddleware(UserAgentMiddleware):

    def __init__(self, user_agent=''):
        self.user_agent = user_agent

    # 每一请求都会走这个函数，在这里随机挑选 UA
    def process_request(self, request, spider):
        ua = random.choice(settings.get('USER_AGENT_LIST'))
        if ua:
            print "******Current UserAgent: %s **************" % ua

            request.headers.setdefault("User-Agent", ua)


class ProxyMiddleware(object):
    def process_request(self, request, spider):
        request.meta['proxy'] = random.choice(settings.get('HTTP_PROXY_LIST'))

ITEM_PIPELINES = {
    'imdb.pipelines.MoviePipeline': 300,
    'imdb.image_pipeline.ImagePipeline': 300
}

sudo apt-get update; sudo apt-get install make build-essential libssl-dev zlib1g-dev \
libbz2-dev libreadline-dev libsqlite3-dev wget curl llvm \
libncursesw5-dev xz-utils tk-dev libxml2-dev libxmlsec1-dev libffi-dev liblzma-dev libedit-dev

export PATH="$HOME/.pyenv/bin:$PATH"
eval "$(pyenv init -)"
eval "$(pyenv virtualenv-init -)"

curl -L https://raw.githubusercontent.com/yyuu/pyenv-installer/master/bin/pyenv-installer | bash

sudo apt-get install -y make build-essential libssl-dev zlib1g-dev \
libbz2-dev libreadline-dev libsqlite3-dev wget curl llvm libncurses5-dev \
libncursesw5-dev xz-utils tk-dev libffi-dev liblzma-dev python-openssl

sudo yum install zlib-devel bzip2 bzip2-devel readline-devel sqlite \
sqlite-devel openssl-devel xz xz-devel libffi-devel

brew install openssl readline sqlite3 xz zlib

export PATH="$HOME/.pyenv/bin:$PATH"
eval "$(pyenv init -)"
eval "$(pyenv virtualenv-init -)"

source ~/.bashrc

pyenv install 2.7.3   # 安装 python

ls ~/.pyenv/versions/

pyenv uninstall 2.7.3 # 卸载 python

rm -rf ~/.pyenv/versions/2.7.3

pyenv versions

pyenv install -l

pyenv global 2.7.3  # 设置全局的 Python 版本，通过将版本号写入 ~/.pyenv/version 文件的方式。
pyenv local 2.7.3 # 设置 Python 本地版本，通过将版本号写入当前目录下的 .python-version 文件的方式。通过这种方式设置的 Python 版本优先级较 global 高。

shell > local > global

pyenv shell 2.7.3 # 设置面向 shell 的 Python 版本，通过设置当前 shell 的 PYENV_VERSION 环境变量的方式。这个版本的优先级比 local 和 global 都要高。`--unset` 参数可以用于取消当前 shell 设定的版本。

pyenv shell --unset

pyenv rehash  # 创建垫片路径（为所有已安装的可执行文件创建 shims，如：~/.pyenv/versions/*/bin/*，因此，每当你增删了 Python 版本或带有可执行文件的包（如 pip）以后，都应该执行一次本命令）

cd ~/.pyenv/plugins
ll
total 24K
drwxr-xr-x 4 einverne einverne 4.0K Apr 22 10:55 pyenv-doctor
drwxr-xr-x 5 einverne einverne 4.0K Apr 22 10:55 pyenv-installer
drwxr-xr-x 4 einverne einverne 4.0K Apr 22 10:55 pyenv-update
drwxr-xr-x 7 einverne einverne 4.0K Apr 22 10:55 pyenv-virtualenv
drwxr-xr-x 4 einverne einverne 4.0K Apr 22 10:55 pyenv-which-ext
drwxr-xr-x 5 einverne einverne 4.0K Apr 22 10:54 python-build

pyenv virtualenv 2.7.15 env-name

pyenv virtualenvs
pyenv activate env-name  # 激活虚拟环境
pyenv deactivate #退出虚拟环境，回到系统环境

pyenv uninstall env-name
rm -rf ~/.pyenv/versions/env-name  # 或者删除其真实目录

$ pyenv commands
activate
commands
completions
deactivate
doctor
exec
global
help
hooks
init
install
installer
local
offline-installer
prefix
rehash
root
shell
shims
uninstall
update                 # 更新 pyenv 及插件
version
--version
version-file
version-file-read
version-file-write
version-name
version-origin
versions
virtualenv
virtualenv-delete
virtualenv-init
virtualenv-prefix
virtualenvs
whence
which

[global]
index-url = http://mirrors.aliyun.com/pypi/simple/

[install]
trusted-host=mirrors.aliyun.com