一个小玩具：Python调用Mysql

1. ubuntu安装MySQL

how to install:
$ sudo apt-get install mysql-server
$ sudo apt-get install mysql-client
$ sudo apt-get install libmysqlclient-dev
#python DB API
$  sudo apt-get install python-mysqldb

check:
sudo netstat -tap | grep mysql

run:
mysql -u root -p

simple commands:
mysql> show databases;
mysql> use mysql    #use database mysql
mysql> show tables;

TIPs:
1. always ends an argument with a ';'
2. not case-sensitive except TABLE and DATABASE names

2. 跟着学一点简单命令"MySql CookBook 3rd"

> CREATE DATABASE cookbook;
> USE cookbook;
> CREATE TABLE limbs (thing VARCHAR(20), legs INT, arms INT);

> INSERT INTO limbs (thing, leg, arms) VALUES('insect',6, 0);
> INSERT INTO limbs (thing, leg, arms) VALUES('armchair', 4, 2);
> INSERT INTO limbs (thing, leg, arms) VALUES('human',2, 2);
> INSERT INTO limbs (thing, leg, arms) VALUES('tripod',3, 0);
> INSERT INTO limbs (thing, leg, arms) VALUES('squid', 0, 10);
> INSERT INTO limbs (thing, leg, arms) VALUES('fish', 0, 0);
> INSERT INTO limbs (thing, leg, arms) VALUES('centipede', 100, 0);
> INSERT INTO limbs (thing, leg, arms) VALUES('table', 4, 0);
> INSERT INTO limbs (thing, leg, arms) VALUES('armchair', 4, 2);
> INSERT INTO limbs (thing, leg, arms) VALUES('phonograph',0,1);
> INSERT INTO limbs (thing, leg, arms) VALUES('Peg Leg Pete',1,2);
> INSERT INTO limbs (thing, leg, arms) VALUES('space alien',NULL,NULL);

> SELECT * FROM limbs;
> SHOW COLUMNS FROM limbs;
> SHOW FULL COLUMNS FROM limbs;
> SHOW FULL COLUMNS FROM limbs \G;
> SHOW FULL COLUMNS FROM limbs LIKE 'thing';
> SHOW FULL COLUMNS FROM limbs LIKE 'thing'\G;
> SELECT COUNT(*) FROM limbs;

3. 修改默认的登录账户和密码

$ sudo vim /etc/mysql/my.cnf
    [client]
    user        = cbuser
    password    = cbpass
$ mysql --print-defaults
here you get:
mysql would have been started with the following arguments:
--user=root --password=****** --port=3306 --socket=/var/run/mysqld/mysqld.sock

$ mysql -e "SELECT COUNT(*) FROM limbs" cookbook
$ mysql -e "SELECT COUNT(*) FROM limbs;SELECT NOW()" cookbook
$ mysql -u root -p -e "SELECT COUNT(*) FROM limbs;SELECT NOW()" cookbook

4. 运行一个SQL 文件：
$mysql cookbook < limbs.sql
or:
    mysql> source limbs.sql;
    mysql> \. limbs.sql;

here limbs.sql is:

    DROP TABLE IF EXISTS limbs;
    CREATE TABLE limbs
    (
        thing VARCHAR(20), # what the thing is
        legs INT, # number of legs it has
        arms INT # number of arms it has
    );

    INSERT INTO limbs (thing, legs, arms) VALUES('human',2, 2);
    INSERT INTO limbs (thing, legs, arms) VALUES('insect',6, 0);
    INSERT INTO limbs (thing, legs, arms) VALUES('armchair', 4, 2);
    INSERT INTO limbs (thing, legs, arms) VALUES('tripod',3, 0);
    INSERT INTO limbs (thing, legs, arms) VALUES('squid', 0, 10);
    INSERT INTO limbs (thing, legs, arms) VALUES('fish', 0, 0);
    INSERT INTO limbs (thing, legs, arms) VALUES('centipede', 100, 0);
    INSERT INTO limbs (thing, legs, arms) VALUES('table', 4, 0);
    INSERT INTO limbs (thing, legs, arms) VALUES('armchair', 4, 2);
    INSERT INTO limbs (thing, legs, arms) VALUES('phonograph',0,1);
    INSERT INTO limbs (thing, legs, arms) VALUES('Peg Leg Pete',1,2);
    INSERT INTO limbs (thing, legs, arms) VALUES('space alien',NULL,NULL);
#=====================end of limbs.sql===================================

the mysqldump utility generates database backups by writing a set of SQL statements that re-create the database.

$ mysqldump cookbook > dump.sql

> SELECT * FROM limbs WHERE legs=0;
$ echo "SELECT * FROM limbs WHERE legs=0" | mysql cookbook

Producing HTML or XML output
$ mysql -H -e "SELECT * FROM limbs WHERE legs=0" cookbook > out.html
$ mysql -X -e "SELECT * FROM limbs WHERE legs=0" cookbook > out.xml

mysql -X -e "SELECT * FROM limbs WHERE legs=0" cookbook \
| xsltproc mysql-xml.xsl-

> SELECT @max_limbs := MAX(arms+legs) FROM limbs;
NB: here ':=' should not be =

> SELECT * FROM limbs WHERE arms+legs = @max_limbs;
> SELECT @name := thing FROM limbs WHERE legs = 0;
> SELECT @name

> SET @max_limbs = (SELECT MAX(arms+legs) FROM limbs);
> SET @x = 1, @X = 2; SELECT @x, @X; #User variable names are not case sensitive
> SELECT CONNECTION_ID();


5. Python DB API

#!/usr/bin/python
# connect.py: connect to the MySQL server
# please goto the belowing link for help:
#    MySQLdb User's Guide: http://mysql-python.sourceforge.net/MySQLdb.html 
import MySQLdb 
try:
    conn = MySQLdb.connect(host='localhost',db="cookbook",user='root', passwd='*******l', port=3306)
    print("Connected")
    cur=conn.cursor()
    count=cur.execute('select * from limbs')
    print 'there are '+str(count)+ 'in all'
    while 0 != count:
        result=cur.fetchone()
        print result
        count-=1
    cur.close()
except:
    print("Cannot connect to server")
else:
    conn.close()
    print("Disconnected")