0

I am trying to get a selection from a table in a MSSQL database into another table in a PostgreSQL database. I read this post by "The_Fox" trying to do the same thing long ago:

copy data from MSSQL database to Postgresql database with Python

I based my code on this and I managed to get one row at a time with "fetchone" from MSSQL to postgresql, however I do not manage to "fetchall" and insert a whole column of data into postgresql.

With the following code I get the error: "ProgrammingError: syntax error at or near "[" LINE 1: INSERT INTO test_tabell VALUES ([])"

My code is below, any suggestions?

Thanks!

import pymssql, psycopg2


#Connect to the database
class DatabaseRequest:

    def __init__(self):
        self.conn1 = pymssql.connect(
    host=r'*****',
    user=r'*****',
    password='*****',
    database='*****'
)
        self.conn2 = psycopg2.connect(host='*****', dbname='*****', user= '*****', password='*****')

        self.cur1 = self.conn1.cursor()
        self.cur2 = self.conn2.cursor()

# Fetch data from the MSSQL-database
    def request_proc(self):
        self.cur1.execute("SELECT table.column FROM table")

        global rows 
        rows = self.cur1.fetchall()

        # This prints all the information I want in my new table, so it seems to be able to fetch it, however in the form of "<class 'tuple'>"
        for row in rows:
            print(row)
            print (type(row))
        return rows   


# Insert all data to an existing table in the postgresql database:
    def insert_proc(self):

#This is the statement I think there is something wrong with:
        self.cur2.execute("INSERT INTO table VALUES (%s)" % self.cur1.fetchall())
        self.conn2.commit()



a = DatabaseRequest()
print(a.request_proc(), a.insert_proc())
Albin T.K
  • 1
  • 1
  • You have indeed a syntax error. You need to switch `VALUES ([])` to something like `VALUES (val1, val2), (val3, val4)`. Read this thread on [how to insert multiple rows on a single insert sql query](https://stackoverflow.com/questions/452859/inserting-multiple-rows-in-a-single-sql-query) – Maor Refaeli Oct 22 '18 at 14:59
  • My question is: why do you want to use python to do this (as opposed to a more-or-less purpose built tool like SSIS)? – Ben Thul Oct 22 '18 at 16:03

1 Answers1

0

You don't actually need python for that.

You can export your table from SQL Server into a .txt or an .csv and import it in PostgreSQL, but there could be some formatting issues. Make sure your columns etc. are named the same.

This way should be easier than over Python.

Good luck! :)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
minzpulver
  • 37
  • 7