python数据导入sql_利用python简化sqlserver数据导入导出

#!/usr/bin/env python

# -*- coding: UTF-8 -*-

#-------------------------------------------------------------------------------

# Name:        导出数据库数据.py

# Purpose:

#

# Author:      SQ1000

#

# Created:     08-02-2012

#-------------------------------------------------------------------------------

import os

import pymssql

import sys

class MSSQLHelper:

"""

对pymssql的简单封装

pymssql库&＃xff0c;该库到这里下载&＃xff1a;http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql

使用该库时&＃xff0c;需要在Sql Server Configuration Manager里面将TCP/IP协议开启

用法&＃xff1a;

"""

def __init__(self,host,user,pwd,db):

self.host &＃61; host

self.user &＃61; user

self.pwd &＃61; pwd

self.db &＃61; db

def __GetConnect(self):

"""

得到连接信息

返回: conn.cursor()

"""

if not self.db:

raise(NameError,"没有设置数据库信息")

self.conn &＃61; pymssql.connect(host&＃61;self.host,user&＃61;self.user,password&＃61;self.pwd,database&＃61;self.db,charset&＃61;"utf8")

cur &＃61; self.conn.cursor()

if not cur:

raise(NameError,"连接数据库失败")

else:

return cur

def ExecQuery(self,sql):

"""

执行查询语句

返回的是一个包含tuple的list&＃xff0c;list的元素是记录行&＃xff0c;tuple的元素是每行记录的字段

调用示例&＃xff1a;

ms &＃61; MSSQLHelper(host&＃61;"localhost",user&＃61;"sa",pwd&＃61;"123456",db&＃61;"PythonWeiboStatistics")

resList &＃61; ms.ExecQuery("SELECT id,NickName FROM WeiBoUser")

for (id,NickName) in resList:

print str(id),NickName

"""

cur &＃61; self.__GetConnect()

cur.execute(sql.encode("utf8"))

resList &＃61;  cur.fetchall()

#查询完毕后必须关闭连接

self.conn.close()

return resList

def ExecNonQuery(self,sql):

"""

执行非查询语句

调用示例&＃xff1a;

cur &＃61; self.__GetConnect()

cur.execute(sql)

self.conn.commit()

self.conn.close()

"""

cur &＃61; self.__GetConnect()

cur.execute(sql.encode("utf8"))

self.conn.commit()

self.conn.close()

def RemoveDirectory (top):

while 1:

if os.path.exists(top):

if len(os.listdir(top)) &＃61;&＃61; 0:

os.rmdir (top)

break

else:

for root, dirs, files in os.walk(top, topdown&＃61;False):

for name in files:

os.remove(os.path.join(root, name))

for name in dirs:

os.rmdir(os.path.join(root, name))

else:

break

def export(db,user,password):

"""导出数据库"""

#得到当前脚本的执行目录

currentDirectory &＃61; os.getcwd()

#查看是否已经存在备份目录&＃xff0c;如果有则删除&＃xff0c;没有则新建目录

backUpDirectory &＃61; "%s\\%s" %( currentDirectory,db&＃43;"Backup")

if os.path.exists(backUpDirectory):

RemoveDirectory(backUpDirectory)

os.mkdir(backUpDirectory)

else:

os.mkdir(backUpDirectory)

#得到要到处的数据库的所有表

ms &＃61; MSSQLHelper(host&＃61;"localhost",user&＃61;user,pwd&＃61;password,db&＃61;db)

for (name,) in ms.ExecQuery("select name from sysobjects where xtype&＃61;&＃39;U&＃39;"):

currentTablePath &＃61; "%s\\%s.txt"%(backUpDirectory,name)

r &＃61; os.popen(&＃39;BCP %s..%s out %s -c -U"%s" -P"%s"&＃39; % (db,name,currentTablePath,user,password))

print r.read()

r.close()

def inport(db,user,password):

"""导入数据库"""

#得到当前脚本的执行目录

currentDirectory &＃61; os.getcwd()

#查看是否已经存在备份目录&＃xff0c;如果有则删除&＃xff0c;没有则新建目录

backUpDirectory &＃61; "%s\\%s" %( currentDirectory,db&＃43;"Backup")

if os.path.exists(backUpDirectory):

#得到要到处的数据库的所有表

ms &＃61; MSSQLHelper(host&＃61;"localhost",user&＃61;user,pwd&＃61;password,db&＃61;db)

for (name,) in ms.ExecQuery("select name from sysobjects where xtype&＃61;&＃39;U&＃39;"):

currentTablePath &＃61; "%s\\%s.txt"%(backUpDirectory,name)

r &＃61; os.popen(&＃39;BCP %s..%s in %s -c -U"%s" -P"%s"&＃39; % (db,name,currentTablePath,user,password))

print r.read()

r.close()

def main():

db &＃61; "PythonWeiboStatistics"

user &＃61; "sa"

password &＃61; "123456"

#这边可以根据不同的参数选择不同的操作

#我是使用了两个文件&＃xff0c;一个是导入一个导出

export(db,user,password)

if __name__ &＃61;&＃61; &＃39;__main__&＃39;:

main()

print u"\n导出完成...\n回车键退出"

raw_input()