MYSQLDB Operational Database (ii)

Querying Data

When you execute a query SQL statement using the Execute () function, you get only the number of rows affected, and you don't really get what we're querying. It doesn't matter, there are three ways to extract data in cursor cursors: Fetchone, Fetchmany, Fetchall, each of which causes the cursor to swim, so be aware of the cursor's position
Cursor. Fetchone ()
Gets the row of data at which the cursor is located, returns a tuple, and none returns none.
Cursor. Fetchmany (Size=none)
Receives a size bar that returns the result row. If the value of size is greater than the number of result rows returned, the Cursor.arraysize bar data is returned. The returned result is a tuple, the element of the tuple is also a tuple, consisting of each row of data;
Cursor. Fetchall ()
Receives all the returned result rows. The returned result is a tuple, the element of the tuple is also a tuple, consisting of each row of data;

Attention:
The result data returned by these functions is derived from the result set of the Exceute () function query. If there is no data in the Exceute () result set, an empty tuple will be returned.

Fetchone Example

import MySQLdbimport randomtry:    conn = MySQLdb.connect(        host = ‘127.0.0.1‘,        user = ‘root‘,        passwd = "123456",        port = 3306)    conn.select_db(‘python‘)#     cur = conn.cursor()# 获取游标    cur.execute("select * from user")    while 1:        res = cur.fetchone()        if res is None:        #表示已经取完结果集            break        print res    cur.close()    conn.commit()    conn.close()    print u"sql执行成功"except Exception, e:print e

Takes the data from the query results of the Execute () function, returns the data in the form of a tuple, and returns an empty tuple if there is no data at the cursor location, once the data is executed and the cursor moves down one position. The Fetchone () function must be used in conjunction with the Exceute () function and used after the Exceute () function

Fetchmany Example

import MySQLdbimport randomtry:    conn = MySQLdb.connect(        host = ‘127.0.0.1‘,        user = ‘root‘,        passwd = "123456",        port = 3306)    conn.select_db(‘python‘)# 选择pythonDB数据库    cur = conn.cursor()# 获取游标    cur.execute("select * from user")    resTuple = cur.fetchmany(2)#2表示取两条数据    print u"结果集类型：",type(resTuple)    for i in resTuple:        print i    cur.close()    conn.commit()    conn.close()    print u"sql执行成功"except Exception, e:print e

Gets the size bar data from the Exceute () function result, returned as a tuple, and each element of the tuple is also a tuple of one row of data, if size is greater than the number of valid result rows, The cursor.arraysize data will be returned, but if there is no data at the cursor location, the empty tuple will be returned. Querying a few data, the cursor will move down several positions. The Fetmany () function must be used in conjunction with the Exceute () function and used after the Exceute () function

Fetchall Example

import MySQLdbtry:    conn = MySQLdb.connect(        host = ‘127.0.0.1‘,        user = ‘root‘,        passwd = "123456",        port = 3306)    conn.select_db(‘python‘)# 选择pythonDB数据库    cur = conn.cursor()# 获取游标    cur.execute("select * from user limit 2")    resSet = cur.fetchall()    print u"共%s条数据:" %len(resSet)    print resSet#元组    cur.close()    conn.commit()    conn.close()    print u"sql执行成功"except Exception, e:    print e

Gets the data at the beginning of the cursor and all of the following, returned as tuples, each element of the tuple is also a tuple of rows of data, and if there is no data at the cursor, an empty tuple is returned. After this method is executed, the cursor moves to the end of the database table

Update data
Update single data

#conding=utf-8import MySQLdbimport randomtry:    conn = MySQLdb.connect(        host = ‘127.0.0.1‘,        user = ‘root‘,        passwd = "123456",        port = 3306)    conn.select_db(‘python‘)    cur = conn.cursor()# 获取游标    #更新一条数据    update = cur.execute("update user set password = ‘huhongqiang‘ where name=‘tom0‘")    print u"修改语句受影响额行数：", update#为更新成功的条数    #查询一条数据    cur.execute("select * from user where name=‘tom0‘;")    print cur.fetchone()    cur.close()    conn.commit()    conn.close()    print u"sql执行成功"except MySQLdb.Error,e:print e

Batch Update data

import MySQLdbimport randomtry:    conn = MySQLdb.connect(        host = ‘127.0.0.1‘,        user = ‘root‘,        passwd = "123456",        port = 3306)    conn.select_db(‘python‘)# 选择pythonDB数据库    cur = conn.cursor()# 获取游标      #批量更新数据sql = "update user set password = %s where name=%s"cur.executemany(sql, [(‘345‘, ‘tom1‘), (‘123‘, ‘tom2‘)])#此处传入一个列表，列表元素是元组#查看更新后的结果    query = cur.execute("select *  from user where name in (‘tom1‘,‘tom2‘)")    print u"表中所有数据："    for i in cur.fetchall():        print i    cur.close()    conn.commit()    conn.close()    print u"sql执行成功"except Exception, e:    print e

MYSQLDB Operational Database (ii)