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

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

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

[python]
1.$ sudo yum install MySQL-python
2. 啥都不說(shuō)了，走兩步吧，下面的程序創(chuàng)建了一個(gè)到mysql數(shù)據(jù)庫(kù)的連接，然后執(zhí)行了一個(gè)簡(jiǎn)單的查詢，并打印查詢結(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. 下面看一個(gè)數(shù)據(jù)庫(kù)建表和插入操作

[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. 下面再來(lái)看看查詢，查詢通常有兩種方式：一種是使用cursor.fetchall（）獲取所有查詢結(jié)果，然后再一行一行的迭代；另一種每次通過(guò)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 ()