python操作MySQL

標籤：mode   value   開始   rac   lex   sts   use   utf8   var   

#coding=utf-8 import MySQLdb conn = MySQLdb.connect(host=‘localhost‘,user=‘root‘,passwd=‘123456‘,charset=‘utf8‘)cursor = conn.cursor()try:    #建立資料庫    DB_NAME = ‘test‘    cursor.execute(‘DROP DATABASE IF EXISTS %s‘ %DB_NAME)    cursor.execute(‘CREATE DATABASE IF NOT EXISTS %s‘ %DB_NAME)    conn.select_db(DB_NAME)     #建立表    TABLE_NAME = ‘t_user‘    cursor.execute(‘CREATE TABLE %s(id int primary key,name varchar(30))‘ %TABLE_NAME)     #插入單條資料    value = [1,‘alexzhou1‘]    cursor.execute(‘INSERT INTO t_user values(%s,%s)‘,value)     #批量插入資料    values = []    for i in range(2,10):        values.append((i,‘alexzhou%s‘ %(str(i))))    cursor.executemany(‘INSERT INTO t_user values(%s,%s)‘,values)     #查詢記錄數量    count = cursor.execute(‘SELECT * FROM %s‘ %TABLE_NAME)    print ‘total records: %d‘,count     #查詢一條記錄    print ‘fetch one record:‘    result = cursor.fetchone()    print result    print ‘id: %s,name: %s‘ %(result[0],result[1])     #查詢多條記錄    print ‘fetch five record:‘    results = cursor.fetchmany(5)    for r in results:        print r     #查詢所有記錄    #重設遊標位置，位移量:大於0向後移動;小於0向前移動，mode預設是relative    #relative:表示從當前所在的行開始移動,absolute:表示從第一行開始移動    cursor.scroll(0,mode=‘absolute‘)    results = cursor.fetchall()    for r in results:        print r     cursor.scroll(-2)    results = cursor.fetchall()    for r in results:        print r     #更新記錄    cursor.execute(‘UPDATE %s SET name = "%s" WHERE id = %s‘ %(TABLE_NAME,‘zhoujianghai‘,1))    #刪除記錄    cursor.execute(‘DELETE FROM %s WHERE id = %s‘ %(TABLE_NAME,2))     #必須提交，否則不會插入資料    conn.commit()except:    import traceback    traceback.print_exc()    conn.rollback()finally:    cursor.close()    conn.close()

 

python操作MySQL