ติดตั้ง 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()