pyodbc in python

1 minute read

How to install pyodbc in Ubuntu

  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

[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

SELECT

import pyodbc cnxn = pyodbc.connect(‘DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=me;PWD=pass’) 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

—> rows is list and you can get count by len(rows)

row = cursor.fetchone() —> Only fetch once

standard

cursor.execute(“select a from tbl where b=? and c=?”, (x, y))

pyodbc extension

cursor.execute(“select a from tbl where b=? and c=?”, x, y)

INSERT/UPDATE/DELETE

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

examples

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

Tags:

Categories:

Updated:

Leave a comment