一個小玩具：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")

 

一個小玩具：Python調用Mysql