python写数据到sql server

环境:

python:3.6.5

pymssql:2.1.5

db:sql server 2012

#!/usr/bin/env python
#coding=utf-8
import os, json, urllib, datetime, shutil
import time
import pymssql

gl_mysql_server="192.168.1.104"
gl_user_name="hxl"
gl_password= "mysql"
gl_db_name="db_test"

##create table tb_test
##(
##id bigint not null identity(1,1) primary key,
##name1 varchar(32),
##name2 varchar(32),
##name3 varchar(32),
##name4 varchar(32),
##name5 varchar(32),
##name6 varchar(32),
##createtime datetime default getdate(),
##modifytime datetime default getdate()
##);


def insert_data():
    ##db =  pymssql.connect(host='192.168.1.101',user='sa',password='Aa123456',database='db_hxl',charset="CP396")
    db = pymssql.connect(host='192.168.1.101', user='sa', password='Aa123456', database='db_hxl')
    # 得到一个可以执行SQL语句的光标对象
    cursor = db.cursor()

    ##db.autocommit=False ## 关闭字自动提交

    for i in range(1, 100001):
        str_i = str(i)
        curr_time = datetime.datetime.now()
        time_str = datetime.datetime.strftime(curr_time, '%Y-%m-%d %H:%M:%S')

        l_id = i
        l_create_time=time_str
        l_modify_time=time_str
        l_remark='a'

        l_name1 ='name1'+str_i
        l_name2= 'name2'+str_i
        l_name3 ='name3'+str_i
        l_name4= 'name4'+str_i
        l_name5 ='name5'+str_i
        l_name6= 'name6'+str_i

        insert_sql = "insert into TB_TEST(name1,name2,name3,name4,name5,name6) values ('%s','%s','%s','%s','%s','%s')" %(l_name1,l_name2,l_name3,l_name4,l_name5,l_name6)
        ##print(insert_sql)
        update_sql="update tb_test set name1='%s' where id=%s" %("update"+str_i,i)
        delete_sql="delete from tb_test where id=%s" %(i)
        try:
            # 执行sql语句

            cursor.execute(insert_sql)
            # 提交到数据库执行
            if (i % 2) == 0:
                db.commit()
        except Exception as err:
            # Rollback in case there is any error
            print("sql语句执行错误", err)
            db.rollback()
    db.commit()
    cursor.close()
    db.close()
    return 0

if __name__ == '__main__':
    print("开始时间:"+time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))
    l_flag = insert_data()
    print("结束时间:"+time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))

 

pymssql安装建议从官网下载安装文件直接安装,直接使用pip install pymssql会遇到很多依赖的问题

下载地址(我这里的python环境是3.6),下载的是:2.1.5版本，使用其他版本会遇到如下的问题

 

pymssql._mssql.MSSQLDatabaseException: (20002, b'DB-Lib error message 20002, severity 9:\nAdaptive

 

 

 

https://pypi.org/project/pymssql/2.1.5/#files

 下载完成后直接安装

 

F:\pymssql>pip install pymssql-2.1.5-cp36-cp36m-win_amd64.whl
Processing f:\pymssql\pymssql-2.1.5-cp36-cp36m-win_amd64.whl
Installing collected packages: pymssql
Successfully installed pymssql-2.1.5

 

 

pycharm里也会体现