[PostgreSQL]如何使用pl/python來連線到SQL Server

1. Install ODBC Libs
    1.1 #yum install unixODBC-devel
    1.2 install freetds
          #wget ftp://ftp.freetds.org/pub/freetds/stable/freetds-stable.tgz
          # cd freetds-0.91/
          # ./configure
          # make
          #  make install

2.config ODBC
    2.1 #vi /etc/odbcinst.ini
----------------------------------------------
[FreeTDS]
Driver = /usr/local/lib/libtdsodbc.so
----------------------------------------------

    2.2 #vi /usr/local/etc/freetds.conf
----------------------------------------------  
[SQLDemo]
        host = 172.16.1.125
        port = 1433
        tds version = 8.0
----------------------------------------------
    2.3 #vi /etc/odbc.ini
----------------------------------------------
[FreeTDS]
Description=my dsn
Driver=FreeTDS
Database=master
Servername=SQLDemo
----------------------------------------------

3.Install Python Libs
    3.1 install pip
        # wget https://bootstrap.pypa.io/get-pip.py
        # python get-pip.py
    3.2 install pypyodbc
        # pip install pypyodbc

4. create extension
    psql#> create extension plpythonu;

5. sample for plpythonu
    5.1 Select
--------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION selsqlserver()
  RETURNS text AS
$BODY$
  import pypyodbc

  conn = pypyodbc.connect("DSN=FreeTDS;UID=sa;PWD=1qaz2wsx3edc$")
  cr = conn.cursor()
  cr.execute('select name from tb1')
  a = ""
  for r in cr:
      a+=r[0]

  return a
$BODY$
  LANGUAGE plpythonu
--------------------------------------------------------------------------------------------
 
    5.2 Update
--------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION updsqlserver()
  RETURNS integer AS
$BODY$
  import pypyodbc

  conn = pypyodbc.connect("DSN=FreeTDS;UID=sa;PWD=1qaz2wsx3edc$")
  cr = conn.cursor()
  cr.execute("update tb1 set name = 'eric' where id = 2")
  conn.commit()

  return 1
$BODY$
  LANGUAGE plpythonu
--------------------------------------------------------------------------------------------