Python對mysql和mssql資料庫的操作

 資料庫的操作在現在的Python裡面已經變得十分的好用,有了一套API標準。模組介面

connect(parameters...) 其中的參數格式如下:

dsn 資料來源名稱 user 使用者名稱(可選) password 密碼(可選) host 主機名稱(可選) database 資料庫名(可選) 舉個例子: connect(dsn='myhost:MYDB',user='guido',password='234$') 又或者 connect('218.244.20.22','username','password','databasename') 

連線物件

連線物件包含如下方法: 

.close() 關閉串連

.commit() 用於交易處理裡面的提交操作

.rollback() 用於交易處理裡面的復原操作

.cursor() 獲得一個遊標

遊標對象

遊標對象包含如下屬性和方法: 

.description 一個列表(name,type_code,display_size,internal_size,precision,scale,null_ok) 此屬性只有在取得了資料之後才有,不然會是null值

.rowcount 表示傳回值的行數.如果沒有執行executeXXX()方法或者此模組沒有實現這個方法,就會返回-1

.callproc(procname[,parameters]) (此為可選方法,應為不是所有的資料庫都支援預存程序的)

.close() 關閉遊標

.execute(operation[,parameters]) 準備並執行一個資料庫操作(包括查詢和命令)

.executemany(operation,seq_of_parameters) 準備一個資料庫命令,然後根據參數執行多次命令

.fetchone() 返回第一行的查詢結果

.fetchmany([size=cursor.arraysize]) 返回指定個多個行的值

.fetchall() 返回所有的查詢結果

.arraysize 這個參數值表示fetchmany預設情況之下擷取的行數

×××××××××××××××××××××××××××××××××××××

可以對mysql和mssql進行操作需要安裝相關python模組 

MSSQL	pymssql-0.8.0.win32-py2.N.exe
MySQL	MySQL-python-1.2.2.win32-py2.N.exe

×××××××××××××××××××××××××××××××××××× 對mysql的操作   連線物件

• db=_mysql.connect(host="localhost",user="joebob", passwd="moonpie",db="thangs") 

• default host is "localhost", and if your login name really was "joebob", you could shorten it to this: 

  db=_mysql.connect(passwd="moonpie",db="thangs") 

• UNIX sockets and named pipes don't work over a network, so if you specify a host other than localhost, TCP will be used, and you can specify an odd port if you need to (the default port is 3306):
  db=_mysql.connect(host="outhouse",port=3307,passwd="moonpie",db="thangs")
  
  
  查詢操作

• 
  To perform a query, you first need a cursor, and then you can execute queries on it: 

  c=db.cursor() max_price=5 c.execute("""SELECT spam, eggs, sausage FROM breakfast WHERE price < %s""", (max_price,)) 

• do a multi-row insert: 

  c.executemany( """INSERT INTO breakfast (name, spam, eggs, sausage, price) VALUES (%s, %s, %s, %s, %s)""", [ ("Spam and Sausage Lover's Plate", 5, 1, 8, 7.95 ), ("Not So Much Spam Plate", 3, 2, 0, 3.95 ), ("Don't Wany ANY SPAM! Plate", 0, 4, 3, 5.95 ) ] )  

   對MSSql的操作
   

  Example script

  import pymssql con = pymssql.connect(host='192.168.13.122',user='sa',password='',database='tempdb') cur = con.cursor() query="create table pymssql (no int, fno float, comment varchar(50));" cur.execute(query) print "create table: %d" % cur.rowcount for x in range(10): query="insert into pymssql (no,fno,comment) values (%d,%d.%d,'%dth comment');" % (x+1,x+1,x+1,x+1) ret=cur.execute(query) print "insert table: %d" % cur.rowcount for x in range(10): query="update pymssql set comment='%dth hahaha.' where no = %d" % (x+1,x+1) ret=cur.execute(query) prin  "update table: %d" % cur.rowcount query="EXEC sp_tables; select * from pymssql;" for x in range(10): cur.execute(query) while 1: print cur.fetchall() if 0 == cur.nextset(): break query="drop table pymssql;" cur.execute(query) print "drop table: %d" % cur.rowcount con.commit() con.close()  

函數映射 

MySQL C API function mapping

C API	_mysql
mysql_affected_rows()	conn.affected_rows()
mysql_autocommit()	conn.autocommit()
mysql_character_set_name()	conn.character_set_name()
mysql_close()	conn.close()
mysql_commit()	conn.commit()
mysql_connect()	_mysql.connect()
mysql_data_seek()	result.data_seek()
mysql_debug()	_mysql.debug()
mysql_dump_debug_info	conn.dump_debug_info()
mysql_escape_string()	_mysql.escape_string()
mysql_fetch_row()	result.fetch_row()
mysql_get_character_set_info()	conn.get_character_set_info()
mysql_get_client_info()	_mysql.get_client_info()
mysql_get_host_info()	conn.get_host_info()
mysql_get_proto_info()	conn.get_proto_info()
mysql_get_server_info()	conn.get_server_info()
mysql_info()	conn.info()
mysql_insert_id()	conn.insert_id()
mysql_num_fields()	result.num_fields()
mysql_num_rows()	result.num_rows()
mysql_options()	various options to _mysql.connect()
mysql_ping()	conn.ping()
mysql_query()	conn.query()
mysql_real_connect()	_mysql.connect()
mysql_real_query()	conn.query()
mysql_real_escape_string()	conn.escape_string()
mysql_rollback()	conn.rollback()
mysql_row_seek()	result.row_seek()
mysql_row_tell()	result.row_tell()
mysql_select_db()	conn.select_db()
mysql_set_character_set()	conn.set_character_set()
mysql_ssl_set()	ssl option to _mysql.connect()
mysql_stat()	conn.stat()
mysql_store_result()	conn.store_result()
mysql_thread_id()	conn.thread_id()
mysql_thread_safe_client()	conn.thread_safe_client()
mysql_use_result()	conn.use_result()
mysql_warning_count()	conn.warning_count()
CLIENT_*	MySQLdb.constants.CLIENT.*
CR_*	MySQLdb.constants.CR.*
ER_*	MySQLdb.constants.ER.*
FIELD_TYPE_*	MySQLdb.constants.FIELD_TYPE.*
FLAG_*	MySQLdb.constants.FLAG.*