J & J - 정성태의 닷넷 이야기

사용자

메뉴

최근 덧글

[정성태] 그냥 RSS Reader 기능과 약간의 UI 편의성 때문에 사용...[이종효] 오래된 소프트웨어는 보안 위협이 되기도 합니다. 혹시 어떤 기능...[정성태] @Keystroke IEEE의 문서를 소개해 주시다니... +_...[손민수 (Keystroke)] 괜히 듀얼채널 구성할 때 한번에 같은 제품 사라고 하는 것이 아...[정성태] 전각(Full-width)/반각(Half-width) 기능을 토...[정성태] Vector에 대한 내용은 없습니다. Vector가 닷넷 BCL...[orion] 글 읽고 찾아보니 디자인 타임에는 InitializeCompon...[orion] 연휴 전에 재현 프로젝트 올리자 생각해 놓고 여의치 않아서 못 ...[정성태] 아래의 글에 정리했으니 참고하세요. C# - Typed D...[정성태] 간단한 재현 프로젝트라도 있을까요? 저런 식으로 설명만 해...

글쓰기

제목

이름

암호

전자우편

HTML

홈페이지

유형

내용

<div style='display: inline'>
<h1 style='font-family: Malgun Gothic, Consolas; font-size: 20pt; color: #006699; text-align: center; font-weight: bold'>파이썬 - pyodbc를 이용한 SQL Server 연결 사용법</h1>

<a target='tab' href='https://peps.python.org/pep-0249/#paramstyle'>PEP 249 문서</a>에서는 paramstyle을 5가지 정의하고 있는데요, 전에 설명했던 대로 <a target='tab' href='https://www.sysnet.pe.kr/2/0/13077'>psycopg2</a>나 <a target='tab' href='https://www.sysnet.pe.kr/2/0/12851'>MySQLdb</a>는 pyformat, format 2가지 방식만 지원을 합니다. 
 
하지만 이번 글에서 소개하는 pyodbc는 특이하게 qmark 방식만을 구현하는데요, ^^ 간단하게 우선 윈도우 환경에서 실습을 해보겠습니다. 
 
우선 pyodbc를 설치하고, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
// Microsoft 문서
// ; <a target='tab' href='https://learn.microsoft.com/en-us/sql/connect/python/pyodbc/python-sql-driver-pyodbc'>https://learn.microsoft.com/en-us/sql/connect/python/pyodbc/python-sql-driver-pyodbc</a>

// ODBC Driver 18.0 for SQL Server Released
// ; <a target='tab' href='https://techcommunity.microsoft.com/t5/sql-server-blog/odbc-driver-18-0-for-sql-server-released/ba-p/3169228'>https://techcommunity.microsoft.com/t5/sql-server-blog/odbc-driver-18-0-for-sql-server-released/ba-p/3169228</a>

// pyodbc: <a target='tab' href='https://pypi.org/project/pyodbc/'>https://pypi.org/project/pyodbc/</a>
c:\temp&gt; python -m pip install pyodbc
</pre>
 
예제 코드를 작성해 실행하면 끝입니다. ODBC니까 다양하게 접속할 수 있지만 여기서는 (<a target='tab' href='https://www.sysnet.pe.kr/2/0/13373'>pymssql</a>로도 가능한) Microsoft SQL Server로의 예제를 작성해 보겠습니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
import pyodbc

connection_string = "Driver={SQL Server};Server=10.10.10.5;Database=TestDB;Uid=sa;Pwd=testpw;"

conn = pyodbc.connect(connection_string)
cursor = conn.cursor()

cursor.execute("SELECT * FROM mytable WHERE age &gt; ?", 1)
row = cursor.fetchone() 
while row: 
 print(row)
 row = cursor.fetchone()

conn.close()
</pre>
 
보는 바와 같이 "qmark" 형식의 쿼리를 실행했고, "?"에 대응하는 인자를 execute 함수에 함께 전달하면 됩니다. 
 
<hr style='width: 50%' /> 
 
참고로, 리눅스의 경우 단순히 "pip install pyodbc" 설치만 하면 사용 시 이런 오류가 발생합니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
"libodbc.so.2: cannot open shared object file: No such file or directory" 
</pre>
 
문서에도 자세하게 나오지만 unixodbc 설치하면 됩니다. ^^ 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
// WSL + Ubuntu 20.04

$ sudo apt install unixodbc
</pre>
 
또한, 이 글의 예제로 든 Microsoft SQL 서버로 연결하는 코드를 실행하면 한 번 더 에러가 발생하는데요, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
$ python3 test.py
Traceback (most recent call last):
 File "test.py", line 9, in &lt;module&gt;
 conn = pyodbc.connect(connection_string)
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found (0) (SQLDriverConnect)")
</pre>
 
오류 원인은, 연결 문자열인 "Driver={SQL Server};...[생략]...;"에 지정한 "SQL Server" 드라이버가 "DATA SOURCES"에 등록이 안 돼 있기 때문입니다.
 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
$ odbcinst -j
unixODBC 2.3.11
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/testusr/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

$ cat /etc/odbcinst.ini
$ cat /etc/odbc.ini
$ ls -l /etc/ODBCDataSources
total 0
$ cat /home/testusr/.odbc.ini
cat: /home/testusr/.odbc.ini: No such file or directory
</pre>
 
따라서 "Microsoft ODBC driver for SQL Server"를 설치해야 하는데요, 공식 문서에서 자세하게 설명하고 있습니다. ^^ 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
Install the Microsoft ODBC driver for SQL Server (Linux)
; <a target='tab' href='https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server'>https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server</a>
</pre>
 
문서상으로는 비록 "<a target='tab' href='https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server#18'>Microsoft ODBC 18</a>"의 경우 우분투에서 18.04 ~ 23.04 버전은 지원이 안 된다고 나오지만, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
if ! [[ "18.04 20.04 22.04 23.04" == *"$(lsb_release -rs)"* ]];
then
 echo "Ubuntu $(lsb_release -rs) is not currently supported.";
 exit;
fi
</pre>
 
<a target='tab' href='https://packages.microsoft.com/config/ubuntu/'>현재는 해당 버전의 패키지</a>가 존재하기 때문에 저 라인을 건너 뛰고 실행하시면 됩니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
$ cat mssql_driver.sh
wget https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc

curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list &gt; /etc/apt/sources.list.d/mssql-release.list

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

$ sudo ./mssql_driver.sh
</pre>
 
이후 "Microsoft ODBC Driver 18 for SQL Server" 이름을 가진 driver가 설치된 것을 확인할 수 있고, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
$ cat /etc/odbcinst.ini
[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.3.so.1.1
UsageCount=1
</pre>
 
윈도우에서 실행했던 소스코드에서 연결 문자열의 Driver만 바꿔준 후, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
# connection_string = "Driver={SQL Server};Server=10.10.10.5;Database=TestDB;Uid=sa;Pwd=testpw;"
connection_string = "Driver={ODBC Driver 18 for SQL Server};Server=10.10.10.5;Database=TestDB;Uid=sa;Pwd=testpw;"
</pre>
 
실행하면 이제 다음과 같은 오류가 나옵니다. ^^; 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
$ python3 test.py
Traceback (most recent call last):
 File "test.py", line 10, in &lt;module&gt;
 conn = pyodbc.connect(connection_string)
pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed:self signed certificate] (-1) (SQLDriverConnect)')
</pre>
 
<a target='tab' href='https://www.sysnet.pe.kr/2/0/13340'>예의 그 인증서 오류</a>입니다. ^^ 그런데 이상하군요, 연결 문자열에서 TrustServerCertificate 또는 Encrypt 옵션을 추가하면, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
connection_string = "TrustServerCertificate=true;Driver={ODBC Driver 18 for SQL Server};Server=10.10.10.5;Database=TestDB;Uid=sa;Pwd=testpw"

# 또는,

connection_string = "Encrypt=true;Driver={ODBC Driver 18 for SQL Server};Server=10.10.10.5;Database=TestDB;Uid=sa;Pwd=testpw"
</pre>
 
실행 시 해당 옵션들이 지원이 안 되는 듯한 오류가 나옵니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
$ python3 test.py
Traceback (most recent call last):
 File "test.py", line 10, in &lt;module&gt;
 conn = pyodbc.connect(connection_string)
pyodbc.OperationalError: ('08001', "[08001] [Microsoft][ODBC Driver 18 for SQL Server]Invalid value specified for connection string attribute 'TrustServerCertificate' (0) (SQLDriverConnect)")
</pre>
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
$ python3 test.py
Traceback (most recent call last):
 File "test.py", line 10, in &lt;module&gt;
 conn = pyodbc.connect(connection_string)
pyodbc.OperationalError: ('08001', "[08001] [Microsoft][ODBC Driver 18 for SQL Server]Invalid value specified for connection string attribute 'Encrypt' (0) (SQLDriverConnect)")
</pre>
 
왜냐하면 윈도우 환경의 연결 문자열과는 달리 "True/False"가 아닌 pyodbc가 정한 "Yes/No"로 값을 설정해야 하기 때문입니다. ^^; 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
// PYODBC + MS SQL SERVER connection with Encrypt=yes not connecting
// ; <a target='tab' href='https://stackoverflow.com/questions/62390326/pyodbc-ms-sql-server-connection-with-encrypt-yes-not-connecting'>https://stackoverflow.com/questions/62390326/pyodbc-ms-sql-server-connection-with-encrypt-yes-not-connecting</a>

connection_string = "TrustServerCertificate=Yes;Driver={ODBC Driver 18 for SQL Server};Server=10.10.10.5;Database=TestDB;Uid=sa;Pwd=testpw"

# 또는,

connection_string = "Encrypt=No;Driver={ODBC Driver 18 for SQL Server};Server=10.10.10.5;Database=TestDB;Uid=sa;Pwd=testpw"
</pre>
 
정리하면, 리눅스 환경의 경우 다음과 같은 예제 코드로 테스트하시면 됩니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
import pyodbc

connection_string = "Driver={ODBC Driver 18 for SQL Server};Server=10.10.10.5;Database=TestDB;Uid=sa;Pwd=testpw;TrustServerCertificate=Yes"

conn = pyodbc.connect(connection_string)
cursor = conn.cursor()

cursor.execute("SELECT * FROM mytable WHERE age &gt; ?", 1)
row = cursor.fetchone() 
while row: 
    print(row)
    row = cursor.fetchone()

conn.close()
</pre>
 
<hr style='width: 50%' /> 
 
참고로, 이러한 5가지 유형의 parameterized query를 변환해 주는 패키지가 있습니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
SQL Params
; <a target='tab' href='https://python-sql-parameters.readthedocs.io/en/latest/sqlparams.html'>https://python-sql-parameters.readthedocs.io/en/latest/sqlparams.html</a>
</pre>

<hr />[이 글에 대해서 여러분들과 의견을 공유하고 싶습니다. 틀리거나 미흡한 부분 또는 의문 사항이 있으시면 언제든 댓글 남겨주십시오.]

</div>

첨부파일

스팸 방지용 인증 번호

2598 (왼쪽의 숫자를 입력해야 합니다.)