python 读取本地excel存入数据库

            
              import pandas as pd
import os
from read_pgdata import Postgredata

root_dir = r'root'
file_names = os.listdir(root_dir)[:-1]
print(len(file_names), file_names)

pg = Postgredata()
for i in range(len(file_names)):
    df = pd.read_excel(root_dir + '\\' + file_names[i], index_col=None, header=None)
    dataset = df.loc[2::].values
    data = []
    for k in dataset:
        k = list(k)
        # print(k)
        data.append(k)
    print(data)
        # print(list(data[j]))
    sql = "INSERT INTO ship_trail (mmsi,longitude,latitude,time,speed,shiphead,heading) VALUES (%s,%s,%s,%s,%s,%s,%s)"
    pg.insertManyRow(strings=data, query=sql) #插入用insertManyRow函数

            
          

            
              import psycopg2
import json
# import pandas as pd

# 从postgresql获取数据
class Postgredata():
    def __init__(self):
        self.dbname = ""
        self.user = ""
        self.password = ""
        self.host = ""
        self.port = ""

    def getdata(self,query=None):
        try:
            conn=psycopg2.connect(dbname=self.dbname, user=self.user, password=self.password, host=self.host, port=self.port)
            cursor = conn.cursor()
            try:
                cursor.execute(query)
                data=cursor.fetchall()
                if len(data)==0:
                    print('没有查到数据')
                    return data
                else:
                    return data
            except Exception as e:
                print(e)
                print('查询错误')
        except Exception as e:
            print(e)
            print('连接失败')
        finally:
            conn.close()

    def setdbname(self, dbname=None):
        self.dbname = dbname

    def getdbname(self):
        return self.dbname

    def getuser(self):
        return self.user

    def setuser(self, user=None):
        self.user = user

    def getpassword(self):
        return self.password

    def setpassword(self, password):
        self.password = password

    def gethost(self):
        return self.host

    def sethost(self, host=None):
        self.host = host

    def getport(self):
        return self.port

    def setport(self, port=None):
        self.port = port

    def savedata(self, query=None):
        #connection.commit()
        #connection.rollback()
        try:
            conn=psycopg2.connect(dbname=self.dbname, user=self.user, password=self.password, host=self.host, port=self.port)
            cursor = conn.cursor()
            try:
                cursor.execute(query)
                conn.commit()
            except Exception as e:
                print(e)
                print('保存失败')
        except Exception as e:
            print(e)
            print('连接失败')
        finally:
            conn.close()

    def insertManyRow(self, strings, query=None):
        # 这里就不需要遍历了，因为executemany接受
        # for index in range(len(rows)):
        try:
            conn = psycopg2.connect(dbname=self.dbname, user=self.user, password=self.password, host=self.host,
                                    port=self.port)
            cur2 = conn.cursor()
            # sql2 = "INSERT INTO test(字段1,字段2,字段3,字段4,字段5) VALUES(%s,%s,%s,%s,%s)"
            cur2.executemany(query, strings)
            conn.commit()
            conn.close()
        except Exception as e:
            print("执行sql时出错：%s" % (e))
            # conn.rollback()
            conn.close()