lost and found ( for me ? )

conncect to MySQL with MySQL-Python



[ 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

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.