conncect to MySQL with MySQL-Python

just referred to http://zetcode.com/databases/mysqlpythontutorial/

[ MySQL configuration ]

DB name : testdb

table name : test

# mysql -u root -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.1.61 Source distribution Copyright (c) 2000, 2011, 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              | | testdb             | +--------------------+ 3 rows in set (0.00 sec) mysql> use testdb Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +------------------+ | Tables_in_testdb | +------------------+ | test             | +------------------+ 1 row in set (0.00 sec) mysql> select * from test; +------+------+ | num  | name | +------+------+ |    1 | foo  | |    2 | bar  | +------+------+ 2 rows in set (0.00 sec) mysql> quit Bye

[ MySQL-python example ]

install MySQL-python module.

# yum install -y MySQL-python

[ retrieve data ]

# cat retrieve_data_mySQL.py #!/usr/bin/env python import MySQLdb import sys conn = None try:        conn = MySQLdb.connect('localhost','test','pass','testdb')        cur = conn.cursor()        cur.execute("SELECT * FROM test") #       cur.execute("SELECT * FROM test where num=1")        numrows = int(cur.rowcount)        for i in range(numrows):                row = cur.fetchone()                print row[0], row[1] except:        print "error"        sys.exit(1) cur.close() conn.close()

# ./retrieve_data_mySQL.py 1 foo 2 bar

[ insert data ]

# egrep -v ^# insert_data_mySQL.py import MySQLdb import sys conn = None try:        conn = MySQLdb.connect('localhost','test','pass','testdb')        cur = conn.cursor()        sql = u"insert into test values('3','hoge')"        cur.execute(sql)        sql = u"insert into test values('4','hogehoge')"        cur.execute(sql)        conn.commit() except:        print "error"        sys.exit(1) cur.close() conn.close()

before inserting data

# ./retrieve_data_mySQL.py 1 foo 2 bar

insert data

# ./insert_data_mySQL.py # echo $? 0

after inserting data

# ./retrieve_data_mySQL.py 1 foo 2 bar 3 hoge 4 hogehoge

[ delete data ]

# cat delete_data_mySQL.py #!/usr/bin/env python import MySQLdb import sys conn = None try:        conn = MySQLdb.connect('localhost','test','pass','testdb')        cur = conn.cursor() #       cur.execute("SELECT * FROM test") #       cur.execute("SELECT * FROM test where num=1")        cur.execute("DELETE FROM test WHERE num = 3")        cur.execute("DELETE FROM test WHERE num = 4")        conn.commit() except:        print "error"        sys.exit(1) cur.close() conn.close()

# ./delete_data_mySQL.py # ./retrieve_data_mySQL.py 1 foo 2 bar

[ update data ]

mysql> select * from test; +------+----------+ | num  | name     | +------+----------+ |    1 | foo      | |    2 | bar      | |    3 | hoge     | |    4 | hogehoge | +------+----------+ 4 rows in set (0.00 sec) mysql>

I’ll update name “foo” to “foobar”

mysql> select name from test where num=2; +------+ | name | +------+ | bar  | +------+ 1 row in set (0.00 sec) mysql>

# vi update_data_mySQL.py #!/usr/bin/env python import MySQLdb import sys conn = None try:        conn = MySQLdb.connect('localhost','test','pass','testdb')        cur = conn.cursor() #       cur.execute("SELECT * FROM test") #       cur.execute("SELECT * FROM test where num=1")        cur.execute("UPDATE test SET name = %s WHERE num = %s",("foobar","2"))        conn.commit() except:        print "error"        sys.exit(1) cur.close() conn.close()

before updating

# ./retrieve_data_mySQL.py 1 foo 2 bar 3 hoge 4 hogehoge

update

# ./update_data_mySQL.py

after updating

# ./retrieve_data_mySQL.py 1 foo 2 foobar 3 hoge 4 hogehoge

[ create table and insert data ]

# cat create_table_insert_data_mySQL.py #!/usr/bin/env python import MySQLdb import sys conn = None try:        conn = MySQLdb.connect('localhost','test','pass','testdb')        cur = conn.cursor()        cur.execute("CREATE TABLE IF NOT EXISTS Testtable(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(20))")        sql = u"INSERT INTO Testtable VALUES('1','foo')"        cur.execute(sql)        sql = u"INSERT INTO Testtable VALUES('2','zzz')"        cur.execute(sql)        conn.commit() except:        print "error"        sys.exit(1) cur.close() conn.close()

# ./create_table_insert_data_mySQL.py

# mysql -u root -p mysql> use testdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +------------------+ | Tables_in_testdb | +------------------+ | Testtable        | | test             | +------------------+ 2 rows in set (0.00 sec) mysql> select * from Testtable; +----+------+ | Id | Name | +----+------+ |  1 | foo  | |  2 | zzz  | +----+------+ 2 rows in set (0.00 sec) mysql> quit Bye

[ monitor DB ]

DB : testdb

table : Testtable

mysql> select * from Testtable; +----+-------+ | Id | Name  | +----+-------+ |  1 | Alive | +----+-------+ 1 row in set (0.00 sec)

If the string of row #1 matches Alive , this script regards MySQL as Alive.

# cat monitor_mySQL.py #!/usr/bin/env python import MySQLdb import sys conn = None try:        conn = MySQLdb.connect(                host = "localhost",                user = "test",                passwd = "pass",                db ="testdb",                port = 3306)        cur = conn.cursor()        cur.execute("SELECT * FROM  Testtable")        numrows = int(cur.rowcount) except MySQLdb.Error, e:        print "Can't connect to MySQL";        sys.exit(1) for i in range(numrows):        row = cur.fetchone() if ( "Alive" == row[1]):        print "Alive"        cur.close()        conn.close()        sys.exit(0) else:        print "String does not match"        cur.close()        conn.close()        sys.exit(1)

when the string is Alive

mysql> select * from Testtable where Id=1; +----+-------+ | Id | Name  | +----+-------+ |  1 | Alive | +----+-------+ 1 row in set (0.00 sec)

# ./monitor_mySQL.py Alive

when the string is not “Alive”

mysql> select * from Testtable where Id=1; +----+--------+ | Id | Name   | +----+--------+ |  1 | foobar | +----+--------+ 1 row in set (0.00 sec)

# ./monitor_mySQL.py String does not match

when MySQL is down

# /etc/init.d/mysqld stop Stopping mysqld:  [  OK  ]

# ./monitor_mySQL.py Can't connect to MySQL