MySQL database operations in Python practice

Address: http://blog.csdn.net/kongxx/article/details/7094018

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:

$ Sudo Yum install mysql-Python2. Let's take two steps.ProgramCreate a connection to the MySQL database, execute a simple query, and print the query results

<textarea readonly name="code" class="python">Import mysqldb </P> <p> conn = mysqldb. connect (host = "172.17.23.121", user = "fkong", passwd = "fkong", DB = "fkong") <br/> cursor = Conn. cursor () <br/> cursor.exe cute ("select version ()") <br/> ROW = cursor. fetchone () <br/> Print "MySQL Server version:", row [0] <br/> cursor. close () <br/> Conn. close ()</textarea> 3. The following is a database table creation and insertion Operation.
<textarea readonly name="code" class="python">Import mysqldb </P> <p> conn = mysqldb. connect (host = "172.17.23.121", user = "fkong", passwd = "fkong", DB = "fkong") <br/> cursor = Conn. cursor () </P> <p> cursor.exe cute ("<br/> Create Table Test <br/> (<br/> ID int, <br/> col1 varchar (40), <br/> col2 varchar (40), <br/> col3 varchar (40) <br/>) <br/> ") </P> <p> cursor.exe cute (" <br/> insert into test (ID, col1, col2, col3) <br/> values <br/> (1, 'A', 'B', 'C'), <br/> (2, 'A', 'bb ', 'cc'), <br/> (3, 'aaa', 'bbb ', 'ccc') <br/> """) </P> <p> Conn. commit () <br/> cursor. close () <br/> Conn. close ()</textarea> 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:

Import mysqldb </P> <p> conn = mysqldb. connect (host = "172.17.23.121", user = "fkong", passwd = "fkong", DB = "fkong") <br/> cursor = Conn. cursor () </P> <p> cursor.exe cute ("select * from test") <br/> rows = cursor. fetchall () <br/> for row in rows: <br/> Print "% d, % s" % (row [0], row [1], row [2], row [3]) </P> <p> Print "number of rows returned: % d" % cursor. rowcount </P> <p> cursor.exe cute ("select * from test") <br/> while (true): <br/> ROW = cursor. fetchone () <br/> If ROW = none: <br/> Break <br/> Print "% d, % s, % s, % s "% (row [0], row [1], row [2], row [3]) </P> <p> Print" number of rows returned: % d "% cursor. rowcount </P> <p> cursor. close () <br/> Conn. close ()