A little toy: Python calls MySQL

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.