文章內(nèi)容

Python實(shí)戰(zhàn)之MySQL數(shù)據(jù)庫操作

發(fā)布時間: 2012/8/31 17:10:21

1. 要想使Python可以操作MySQL數(shù)據(jù)庫，首先需要安裝MySQL-python包，在CentOS上可以使用一下命令來安裝

[python]
1.$ sudo yum install MySQL-python
2. 啥都不說了，走兩步吧，下面的程序創(chuàng)建了一個到mysql數(shù)據(jù)庫的連接，然后執(zhí)行了一個簡單的查詢，并打印查詢結(jié)果 -

[python]
1.import MySQLdb
2.
3.conn = MySQLdb.connect (host = "172.17.23.121", user = "fkong", passwd = "fkong", db = "fkong") 4.cursor = conn.cursor ()
5.cursor.execute ("SELECT VERSION()") 6.row = cursor.fetchone ()
7.print "MySQL server version:", row[0] 8.cursor.close ()
9.conn.close ()
3. 下面看一個數(shù)據(jù)庫建表和插入操作

[python]
1.import MySQLdb
2.
3.conn = MySQLdb.connect (host = "172.17.23.121", user = "fkong", passwd = "fkong", db = "fkong") 4.cursor = conn.cursor ()
5.
6.cursor.execute (""" 7.    CREATE TABLE TEST
8.    (
9.        ID INT,
10.        COL1 VARCHAR(40),
11.        COL2 VARCHAR(40),
12.        COL3 VARCHAR(40)
13.    )
14.    """)
15.
16.cursor.execute (""" 17.    INSERT INTO TEST (ID, COL1, COL2, COL3)
18.    VALUES
19.        (1, 'a', 'b', 'c'),
20.        (2, 'aa', 'bb', 'cc'),
21.        (3, 'aaa', 'bbb', 'ccc')
22.    """)
23.
24.conn.commit()
25.cursor.close ()
26.conn.close ()
4. 下面再來看看查詢，查詢通常有兩種方式：一種是使用cursor.fetchall（）獲取所有查詢結(jié)果，然后再一行一行的迭代；另一種每次通過cursor.fetchone()獲取一條記錄，直到獲取的結(jié)果為空為止。看一下下面的例子：

[python]
1.import MySQLdb
2.
3.conn = MySQLdb.connect (host = "172.17.23.121", user = "fkong", passwd = "fkong", db = "fkong") 4.cursor = conn.cursor ()
5.
6.cursor.execute ("SELECT * FROM TEST") 7.rows = cursor.fetchall()
8.for row in rows: 9.    print "%d, %s, %s, %s" % (row[0], row[1], row[2], row[3]) 10.
11.print "Number of rows returned: %d" % cursor.rowcount 12.
13.cursor.execute ("SELECT * FROM TEST") 14.while (True): 15.    row = cursor.fetchone()
16.    if row == None: 17.        break 18.    print "%d, %s, %s, %s" % (row[0], row[1], row[2], row[3]) 19.
20.print "Number of rows returned: %d" % cursor.rowcount 21.
22.cursor.close ()
23.conn.close ()