MySQL database operations in Python practice

1. To enable Python to operate the MySQL database, first install the MySQL-python package. You can run the following command on CentOS to install the package:

[Python]

1. $ Sudo yum install MySQL-python

2. Leave nothing to say. Take two steps. The following program creates a connection to the mysql database, executes a simple query, and prints the query result.

[Python]

1. ImportMySQLdb
3. Conn = MySQLdb. connect (host ="172.17.23.121", User ="Fkong", Passwd ="Fkong", Db ="Fkong")
4. Cursor = conn. cursor ()
5. Cursor.exe cute ("Select version ()")
6. Row = cursor. fetchone ()
7. Print "MySQL server version :", Row [0]
8. Cursor. close ()
9. Conn. close ()

3. The following is a database table creation and insertion Operation.
[Python]

1. ImportMySQLdb
3. Conn = MySQLdb. connect (host ="172.17.23.121", User ="Fkong", Passwd ="Fkong", Db ="Fkong")
4. Cursor = conn. cursor ()
6. Cursor.exe cute (""" 
7. CREATE TABLE TEST 
8. ( 
9. Id int, 
10. COL1 VARCHAR (40 ), 
11. COL2 VARCHAR (40 ), 
12. COL3 VARCHAR (40) 
13. ) 
14. """)
16. Cursor.exe cute (""" 
17. Insert into test (ID, COL1, COL2, COL3) 
18. VALUES 
19. (1, 'A', 'B', 'C '), 
20. (2, 'A', 'bb ', 'cc '), 
21. (3, 'aaa', 'bbb ', 'ccc ') 
22. """)
24. Conn. commit ()
25. Cursor. close ()
26. Conn. close ()

4. next let's look at the query. there are usually two ways to query: one is to use cursor. fetchall () is used to obtain all query results and then iterate over one row. fetchone () gets a record until the obtained result is null. Let's take a look at the following example:

[Python]

1. ImportMySQLdb
3. Conn = MySQLdb. connect (host ="172.17.23.121", User ="Fkong", Passwd ="Fkong", Db ="Fkong")
4. Cursor = conn. cursor ()
6. Cursor.exe cute ("SELECT * from test")
7. Rows = cursor. fetchall ()
8. ForRowInRows:
9. Print "% D, % s"% (Row [0], Row [1], Row [2], Row [3])
11. Print "Number of rows returned: % d"% Cursor. rowcount
13. Cursor.exe cute ("SELECT * from test")
14. While(True):
15. Row = cursor. fetchone ()
16. IfRow =None:
17. Break
18. Print "% D, % s"% (Row [0], Row [1], Row [2], Row [3])
20. Print "Number of rows returned: % d"% Cursor. rowcount
22. Cursor. close ()
23. Conn. close ()