Copy a whole table from MSSQL to PostgreSQL with Python code

Question

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())

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 — Maor Refaeli
– Maor Refaeli, Commented Oct 22, 2018 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
– Ben Thul, Commented Oct 22, 2018 at 16:03

marc_s · Accepted Answer · 2018-10-29 21:39:53Z

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! :)

edited Oct 29, 2018 at 21:39

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Oct 23, 2018 at 10:41

minzpulver

378 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Copy a whole table from MSSQL to PostgreSQL with Python code

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related