DataTransfer from mssql to Postgres.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: psycopg2-binary in /home/kreezy/anaconda3/lib/python3.8/site-packages (2.9.1)\r\n"
     ]
    }
   ],
   "source": [
    "!pip install psycopg2-binary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "https://stackoverflow.com/questions/35072337/data-migration-from-ms-sql-to-postgresql-using-sqlalchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "ename": "OperationalError",
     "evalue": "Statement not executed or executed statement has no resultset",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-20-9eef850b09f0>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m     22\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     23\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mDatabaserq\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;31m#object\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 24\u001b[0;31m \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresult\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0minserting\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m<ipython-input-20-9eef850b09f0>\u001b[0m in \u001b[0;36minserting\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m     19\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     20\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0minserting\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 21\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcur1\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"insert into purchase(businessentityid,accountnumber,fname,creditrating,preferredvendorstatus,activeflag,purchasingwebserviceurl,modifieddate) \\ VALUES ('%d', '%s', '%s', '%d', '%s', '%s', '%s', '%s')\"\u001b[0m \u001b[0;34m%\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcur\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfetchall\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;31m#,self.conn.commit())\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     22\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     23\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mDatabaserq\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;31m#object\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32msrc/pymssql/_pymssql.pyx\u001b[0m in \u001b[0;36mpymssql._pymssql.Cursor.fetchall\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mOperationalError\u001b[0m: Statement not executed or executed statement has no resultset"
     ]
    }
   ],
   "source": [
    "import pymssql,psycopg,psycopg2.extras # for connecting sql server to postgres\n",
    "\n",
    "# i create a class for database request\n",
    "class Databaserq:\n",
    "    #creating a constructor \n",
    "    def __init__(self):\n",
    "        #connecting both mssql and postgres\n",
    "        self.con=pymssql.connect(host='localhost',user=\"SA\",password=\"Chris\",database=\"AdventureWorks2019\")\n",
    "        self.conn=psycopg.connect(host=\"localhost\", port = 5432, dbname=\"SsqlAdventureCopies\", user=\"postgres\", password=\"Chris\")\n",
    "        \n",
    "        #creating a cursor for both databases\n",
    "        self.cur=self.con.cursor()\n",
    "        self.cur1=self.conn.cursor()\n",
    "#  now created a fuction for migartion/execution of the migration pipeline\n",
    "    def migrate(self,rows):\n",
    "        self.cur.execute(\"SELECT * FROM Purchasing.Vendor WHERE CreditRating<3\")\n",
    "        rows=self.cur.fetchall()\n",
    "       # return rows\n",
    "    \n",
    "    def inserting(self):\n",
    "        self.cur1.execute(\"insert into purchase(businessentityid,accountnumber,fname,creditrating,preferredvendorstatus,activeflag,purchasingwebserviceurl,modifieddate) \\ VALUES ('%d', '%s', '%s', '%d', '%s', '%s', '%s', '%s')\" % self.cur.fetchall())#,self.conn.commit())\n",
    "        \n",
    "result=Databaserq()#object\n",
    "print(result.inserting())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8.5 64-bit ('base': conda)",
   "language": "python",
   "name": "python385jvsc74a57bd02a797d9e3f8019c2ae59779c796dc3bc804b2a4dfcc8404d1c836fe010d50029"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}