pyodbc in python

Installing pyodbc module

1. sudo apt-get update
2. sudo apt-get install unixodbc unixodbc-dev freetds-dev
3. sudo apt-get install freetds-bin tdsodbc
4. sudo pip3 install pyodbc –user

Tips for checking linux os

cat /etc/issue

Simple pyodbc guide

[hs-NUC0001:fabric][/home/fabric/pys] # sudo pip3 install pyodbc --user
Collecting pyodbc
  Using cached https://files.pythonhosted.org/packages/75/29/aa190749bac37ede0f11a68a75e7055254699c11572bd94213f1163dfd8f/pyodbc-4.0.27.tar.gz
Building wheels for collected packages: pyodbc
  Running setup.py bdist_wheel for pyodbc ... done
  Stored in directory: /home/fabric/.cache/pip/wheels/87/b7/78/0740a2ed98bfe463525ad42d535370e34141c5d36b2d00dcaf
Successfully built pyodbc
Installing collected packages: pyodbc
Successfully installed pyodbc-4.0.27

Ubuntu 18.04 On Ubuntu systems, all you need to do is run

sudo apt install python3-pip sudo apt install unixodbc-dev pip3 install –user pyodbc

Module : pyodbc

• Install : python -m pip install pyodbc
• Document : https://github.com/mkleehammer/pyodbc/wiki

MSSQL Server

import pyodbc

# Connect to MsSQL
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=me;PWD=pass')

# Select
cursor = cnxn.cursor()
cursor.execute("select user_id, user_name from users")
rows = cursor.fetchall()
for row in rows:
    print row.user_id, row.user_name

# select - standard
cursor.execute("select a from tbl where b=? and c=?", (x, y))

# select - python extension
cursor.execute("select a from tbl where b=? and c=?", x, y)

cursor.execute("insert into test_tb values(6, 'name')")

count = cursor.execute("update users set last_logon=? where user_id=?", now, user_id).rowcount
count = cursor.execute("delete from users where user_id=1").rowcount

cnxn = pyodbc.connect('mydsn', autocommit=False)
crsr = cnxn.cursor()
crsr.execute("UPDATE T1 SET ...")
crsr.execute("DELETE FROM T1 WHERE ...")
crsr.execute("INSERT INTO T1 VALUES ...")
cnxn.commit()
crsr.execute("INSERT INTO T2 VALUES ...")
crsr.execute("INSERT INTO T3 VALUES ...")
cnxn.commit()
cnxn.close()

#cnxn.rollback()

• rows is list and you can get count by len(rows)
• Only fetch once : row = cursor.fetchone()

Another example

import pyodbc
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=db.xxx.net;DATABASE=xxx;UID=xxxweb;PWD=xxx')
cursor = cnxn.cursor()
cursor.execute("select type, id, name from apps_info")
rows = cursor.fetchall()
for row in rows:
    print row.type, row.id, row.name

Oracle Server

Download Oracle Instant Clinet ODBC

import pyodbc

# Connect
cnxn = pyodbc.connect('DRIVER={Devart ODBC Driver for Oracle};Direct=True;Host=myhost;Service Name=myservicename;User ID=myuserid;Password=mypassword')

#cnxn = 'Driver={Microsoft ODBC for Oracle};Server=<host>:<port>/<db>.<host>;uid= <username>;pwd=<password>'
#cnxn = pyodbc.connect(connectString)

# Insert
cursor = cnxn.cursor()
cursor.execute("INSERT INTO EMP (EMPNO, ENAME, JOB, MGR) VALUES (535, 'Scott', 'Manager', 545)") 

# Select
cursor = cnxn.cursor()	
cursor.execute("SELECT * FROM EMP") 
row = cursor.fetchone() 
  while row:
  	print (row) 
   	row = cursor.fetchone()