一个把json数据批量插入SQL SERVER 的Python脚本

这是一个把json数据批量插入SQL SERVER 的Python脚本


本文参考了pymssql的官方文档

http://www.pymssql.org/en/stable/pymssql_examples.html








#!/usr/bin/env python

# -*- coding: utf-8 -*-

 

import  pymssql

import  json

 

DB_SETTINGS  =  { 'host' :  '127.0.0.1' ,

                'port' :  '1433' ,

                'user' :  'sa' ,

                'password' :  'password' ,

                'database' :  'test' }

 

class  SQLSERVER:

     """database"""

 

     def  __init__( self , settings_):

         """initial db"""

         self .conn  =  pymssql.connect( * * settings_)

         self .cur  =  self .conn.cursor()

 

     def  exec_many( self , sql_str, data_):

         self .cur.executemany(sql_str, data_)

         self .conn.commit()

 

     def  exec_sql( self , sql_str):

         self .cur.execute(sql_str)

         ret  =  self .cur.fetchall()

         return  ret

 

     def  close( self ):

         self .cur.close()

         self .conn.close()

 

 

json_data  =  """

[

   {

     "Indicator":"Life expectancy at birth (years)",

     "PUBLISH STATES":"Published",

     "Year":1990,

     "WHO region":"Europe",

     "World Bank income group":"High-income",

     "Country":"Andorra",

     "Sex":"Both sexes",

     "Display Value":77,

     "Numeric":77.00000,

     "Low":"",

     "High":"",

     "Comments":""

   },

   {

     "Indicator":"Life expectancy at birth (years)",

     "PUBLISH STATES":"Published",

     "Year":2000,

     "WHO region":"Europe",

     "World Bank income group":"High-income",

     "Country":"Andorra",

     "Sex":"Both sexes",

     "Display Value":80,

     "Numeric":80.00000,

     "Low":"",

     "High":"",

     "Comments":""

   },

   {

     "Indicator":"Life expectancy at age 60 (years)",

     "PUBLISH STATES":"Published",

     "Year":2012,

     "WHO region":"Europe",

     "World Bank income group":"High-income",

     "Country":"Andorra",

     "Sex":"Female",

     "Display Value":28,

     "Numeric":28.00000,

     "Low":"",

     "High":"",

     "Comments":""

   },

   {

     "Indicator":"Life expectancy at age 60 (years)",

     "PUBLISH STATES":"Published",

     "Year":2000,

     "WHO region":"Europe",

     "World Bank income group":"High-income",

     "Country":"Andorra",

     "Sex":"Both sexes",

     "Display Value":23,

     "Numeric":23.00000,

     "Low":"",

     "High":"",

     "Comments":""

   }

]

"""

 

db  =  SQLSERVER(DB_SETTINGS)

 

items  =  json.loads(json_data)

 

# with open(r'd:\temp\source.json', 'r') as json_file:    # 如果从文件中载入数据

#    items = json.load(json_file)

 

fields  =  ( "Indicator" ,  "PUBLISH STATES" ,  "Year" ,  "WHO region" ,  "World Bank income group" ,

           "Country" ,  "Sex" ,  "Display Value" ,  "Numeric" ,  "Low" ,  "High" ,  "Comments" )

 

sql  =  'INSERT INTO table_name VALUES (%s, %s, %d, %s, %s, %s, %s, %d, %f, %s, %s, %s);'

 

data  =  [ tuple (item[field]  for  field  in  fields)  for  item  in  items]

db.exec_many(sql, data)

 

db.close()