A little toy: Python calls MySQL

1. Ubuntu installation 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. Learn a little simple command "MYSQL CookBook 3rd"

> CREATE DATABASE Cookbook;
> Use cookbook;
> CREATE TABLE limbs (thing VARCHAR), 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 ');
> 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. Modify the default login account and password

$ sudo vim/etc/mysql/my.cnf
[Client]
user = Cbuser
Password = Cbpass
$ MySQL--print-defaults
Here you get:
MySQL would has 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. Run a SQL file:
$mysql Cookbook < Limbs.sql
Or
Mysql> source Limbs.sql;
mysql> \. Limbs.sql;

Here Limbs.sql is:

    DROP TABLE IF EXISTSlimbs; CREATE TABLELimbs (ThingVARCHAR( -), # What the Thing isLegsINT, # Number  oflegs it has armsINT# Number  ofarms it has); INSERT  intoLimbs (thing, legs, arms)VALUES('Human',2,2); INSERT  intoLimbs (thing, legs, arms)VALUES('Insect',6,0); INSERT  intoLimbs (thing, legs, arms)VALUES('Armchair',4,2); INSERT  intoLimbs (thing, legs, arms)VALUES('Tripod',3,0); INSERT  intoLimbs (thing, legs, arms)VALUES('Squid',0,Ten); INSERT  intoLimbs (thing, legs, arms)VALUES('Fish',0,0); INSERT  intoLimbs (thing, legs, arms)VALUES('Centipede', -,0); INSERT  intoLimbs (thing, legs, arms)VALUES('Table',4,0); INSERT  intoLimbs (thing, legs, arms)VALUES('Armchair',4,2); INSERT  intoLimbs (thing, legs, arms)VALUES('Phonograph',0,1); INSERT  intoLimbs (thing, legs, arms)VALUES('Peg Leg Pete',1,2); INSERT  intoLimbs (thing, legs, arms)VALUES('Space Alien',NULL,NULL);#=====================End  ofLimbs.sql===================================

the mysqldump utility generates database backups by writing a set of sql  s Tatements 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 is =

> 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 is 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.htmlImportMySQLdbTry: 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 is'+STR (count) +' in all'     while0! =Count:result=Cur.fetchone ()PrintResult Count-=1cur.close ()except:    Print("cannot connect to server")Else: Conn.close ()Print("Disconnected")

A small toy: Python calls MySQL