Python3.7 ติดต่อดาต้าเบสผ่าน ODBC ด้วย pyodbc

Posted on by

ติดตั้ง curl

sudo apt install curl

ติดตั้ง Microsoft ODBC driver for SQL Server

ติดตั้ง Microsoft ODBC 17

sudo su
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

#Ubuntu 18.04
curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

exit

sudo apt update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql17

# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo apt install -y unixodbc-dev

ติดตั้ง pyodbc

python3 -m pip install pyodbc

ถ้ามี error ประมาณนี้

...
  In file included from src/buffer.cpp:12:0:
  src/pyodbc.h:45:10: fatal error: Python.h: No such file or directory
   #include <Python.h>
            ^~~~~~~~~~
  compilation terminated.
  error: command 'x86_64-linux-gnu-gcc' failed with exit status 1
...

ให้ติดตั้ง python3.7-dev ก่อน

sudo apt install python3.7-dev

ทดลอง import pyodbc ถ้าไม่มี error ก็แสดงว่าติดตั้งได้ละ

$ python3
Python 3.7.5 (default, Feb 23 2021, 13:22:40)
[GCC 8.4.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>>

Connet ไป SQL Server

# test.py

import pyodbc 
# Some other example server values are
# server = 'localhost\sqlexpress' # for a named instance
# server = 'myserver,port' # to specify an alternate port
server = 'tcp:myserver.database.windows.net' 
database = 'mydb' 
username = 'myusername' 
password = 'mypassword' 
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

ลองรัน ถ้าไม่มี error ก็ connect ได้ละ

$ python3 test.py

Run query

#Sample select query
cursor.execute("SELECT @@version;") 
row = cursor.fetchone() 
while row: 
    print(row[0])
    row = cursor.fetchone()

ผลการรัน

$ python3 test.py
Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
        Sep 24 2019 13:48:23
        Copyright (C) 2019 Microsoft Corporation
        Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19042: ) (Hypervisor)

Insert a row

#Sample insert query
count = cursor.execute("""
INSERT INTO SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) 
VALUES (?,?,?,?,?)""",
'SQL Server Express New 20', 'SQLEXPRESS New 20', 0, 0, CURRENT_TIMESTAMP).rowcount
cnxn.commit()
print('Rows inserted: ' + str(count))

ปิด connection

cnxn.close()