python个人学习小技巧 数据库Mssql 操作

MssqlConn

import pymssql
con = pymssql.connect(host="computerName\\SQLEXPRESS",user="sa",password="telesys",database="jadsTest")

print con
cursor = con.cursor( )
print  cursor
# Execute an SQL string
sql = "SELECT * FROM test1"
cursor.execute(sql)
# Fetch all results from the cursor into a sequence and close the connection
results = cursor.fetchall( )
columns = results[0:1000000]
MAX_ROWS = len(results[0])
print columns
print MAX_ROWS
print "<table>"
for field in columns:
print "<tr>"
for k in range(MAX_ROWS):
print "<td>", field[k], "</td>"
print "</tr>"
print "</table>"
for des in cursor.description: print des

one = cursor.fetchone()
print one
con.close( )

 

MssqlCreate

#import mssql
import pymssql,cPickle
con = pymssql.connect(host="SHARDSERVER\\SQLEXPRESS",user="sa",password="telesys",database="jadsTest")
print con

cur= con.cursor()
print  cur

query="create table pymssql (no int, fno float, comment varchar(50));"
print 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 ret
print "insert table: %d" % cur.rowcount

con.commit()

 

Mssqldrop

import pymssql,cPickle
con = pymssql.connect(host="SHARDSERVER\\SQLEXPRESS",user="sa",password="telesys",database="jadsTest")
print con

cur= con.cursor()
print  cur

query="drop table test2;"
cur.execute(query)
print "drop table: %d" % cur.rowcount

con.commit()