Python小工具--读取csv文件并插入到sqlServer数据库

先上代码。很简单。不再解释O(∩_∩)O
代码地址：
python版的：https://github.com/hanzhonghao/PyDataBaseTool
Java版实现的：参照下边java代码部分
DB_helper.py

import pymssql

class MSSQL:
    def __init__(self, host, user, pwd, db):
        self.host = host
        self.user = user
        self.pwd = pwd
        self.db = db

    def __GetConnect(self):
        self.conn = pymssql.connect(host=self.host, user=self.user, password=self.pwd, database=self.db, charset='utf8')
        cur = self.conn.cursor()
        if not cur:
            return (NameError, "connect db failed")
        else:
            return cur

    # Executing the query returns a list containing a tuple, the element of the list is the row of record,
    # and the element of the tuple is the field of each row of record
    def ExecQuery(self, sql):
        cur = self.__GetConnect()
        cur.execute(sql)
        resList = cur.fetchall()
        self.conn.close()
        return resList

    def ExecNonQuery(self, sql):
        cur = self.__GetConnect()
        cur.execute(sql)
        self.conn.commit()
        self.conn.close()

接下来是执行的main类
init.py

import datetime
import csv

import sys

from DBTool import DB_helper
from DBTool.CustomException import SqlException

# DB connect config
server = "127.0.0.1"
user = "fightingbosshao"
password = "iliketechnology"
database = "Test

#DB Table config
DBTableName = 'UserPermissionList'

# .csv file pathconfig
filename = 'C:/Users/z/Desktop/test.csv'

#DB default value
BlackListed = 0
WhiteListed = 1
EntryDate = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")


def initDB():
    global mssql
    mssql = DB_helper.MSSQL(server, user, password, database)


def query():
    rows = mssql.ExecQuery('select top (100) NMAID, UserID from {}'.format(DBTableName))
    print(' ')
    print('The following content is the result of the query: ')
    print(' ')
    i = 0
    for row in rows:
        i = i + 1
        print('id :', i, '     NMAID : ' + row[0] + '     UserID : ' + row[1])


def insert(NMAID, UserID):
    mssql.ExecNonQuery(
        "insert into {}(NMAID, UserID, BlackListed, WhiteListed, EntryDate) values ('%s','%s','%d','%d','%s')"
        .format(DBTableName) % (NMAID, UserID, BlackListed, WhiteListed, EntryDate))


def getWhiteListAndInsertDB():
    with open(filename) as f:
        reader = csv.reader(f)
        # Line Numbers start with line 2
        next(reader)
        i = 0
        for row in reader:
            i = i + 1
            NMAID = row[0]
            UserID = row[1]
            print(' ')
            print('NMAID : ' + NMAID + '     UserID : ' + UserID)
            insert(NMAID, UserID)
            print('Insert DB successful !  Insert ', i, ' times')
    f.close()


def isDBExist():
    tableName = ''
    try:
        sql = "select * from information_schema.tables where table_schema='dbo' and  table_name = 'AppNMDPAuthenticateUserPermissionList'"
        results = mssql.ExecQuery(sql)
        for result in results:
            tableName = result[2]
        if tableName == '':
            raise SqlException()
    except SqlException as e:
        print('An exception occurs: ')
        print('The exception type is--->', e)
        print('The exception error message is--->', e.message)
        sys.exit(1)
    else:
        print('Ready insert data into DB')


if __name__ == '__main__':
    initDB()
    isDBExist()
    getWhiteListAndInsertDB()
    query()

自定义异常类

CustomException.py

#!/usr/bin/env python
# encoding: utf-8
class MyException(Exception):

    def __init__(self, *args):
        self.args = args


class SqlException(MyException):
    def __init__(self, message='The table is not exist', args=('SqlException',)):
        self.args = args
        self.message = message

JAVA实现该功能代码如下

package com.zhong;

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DBToolEntrance {

  public static String DBIP = "127.0.0.1";
  public static String DBName = "China";
  public static String DBUserName = "sa";
  public static String DBUserPassword = "iliketechnology";
  public static String tableName="AppList";
  public static String filePath="C:/Users/Desktop/test.csv";

  public static String url = "jdbc:sqlserver://"+DBIP+":1433;databaseName="+DBName+";user="+DBUserName+";password="+DBUserPassword;//sa身份连接
  public static int WhiteListed=1;
  public static int BlackListed=0;
  public static Date EntryData;

  private static List<String> dataFromCsv;

  public static void main(String args[]) {
    dataFromCsv = getDataFromCsv();
    initDBAndInsertData();
  }

  private static void initDBAndInsertData() {
    Connection con = null;
    Statement stmt = null;

    try {
      System.out.println("connect begin.");
      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
      con = DriverManager.getConnection(url);
      System.out.println("database opetation begin!.");

      String insertSql = "INSERT INTO "+ tableName +" (NMAID,UserID,BlackListed,WhiteListed,EntryDate) VALUES (?,?,?,?,?)";
      PreparedStatement preparedStatement = con.prepareStatement(insertSql);
      /**get data from csv file*/
      for (int i = 0; i < dataFromCsv.size(); i++) {
        String s = dataFromCsv.get(i);
        String item[] = s.split(",");
        String NMAID = item[0];
        String UserID = item[1];
        insertDataToDB(preparedStatement,UserID, NMAID);
      }

      preparedStatement.executeBatch();
      con.commit();
      stmt = con.createStatement();
      queryData(stmt);
    }
    catch (Exception e) {
      e.printStackTrace();
    } finally {
      if (stmt != null)
        try {
          stmt.close();
        } catch (Exception e) {
        }
      if (con != null)
        try {
          con.close();
        } catch (Exception e) {
        }
    }
  }

  private static List<String> getDataFromCsv() {
    List<String> allString = new ArrayList<>();
    try {
      BufferedReader reader = new BufferedReader(new FileReader(filePath));
      reader.readLine();
      String line;
      while ((line = reader.readLine()) != null) {
        allString.add(line);
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    return allString;
  }

  public static void insertDataToDB(PreparedStatement preparedStatement, String userID, String NMAID)
      throws SQLException {
    EntryData = new java.sql.Date(System.currentTimeMillis());
    preparedStatement.setString(1, NMAID);
    preparedStatement.setString(2, userID);
    preparedStatement.setInt(3, BlackListed);
    preparedStatement.setInt(4, WhiteListed);
    preparedStatement.setDate(5, EntryData);
    preparedStatement.addBatch();
  }


  public static void queryData(Statement stmt) throws SQLException {
    String sql = "SELECT  * FROM "+tableName;
    ResultSet rs = stmt.executeQuery(sql);
    while (rs.next()) {
      System.out.println("NMAID:" + rs.getString(1));
      System.out.println("userID:" + rs.getString(2));
      System.out.println("                                     ");
    }
  }

}