SQL Server Python not connecting with outsourced database config

toiithl6 于 2023-04-28 发布在 Python

关注(0)|答案(3)|浏览(131)

The python code doesn't establish connection to SQL Server, when I outsource the connection infos in a config.ini. But however, when I hard code the connection infos, it is working.

Connection not working:

import pyodbc
import configparser

# INI-Datei lesen
config = configparser.ConfigParser()
config.read('config.ini')
server = config['SQL']['Server']
database = config['SQL']['Database']
username = config['SQL']['Username']
password = config['SQL']['Password']

# Verbindung zum SQL Server herstellen
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

# Autocommit aktivieren
cnxn.autocommit = True

# Datenbank erstellen
cursor = cnxn.cursor()
cursor.execute('CREATE DATABASE [DB Test TCPOS]')

# Tabellen erstellen
cursor.execute('USE [DB Test TCPOS 2]')

# Table 1
cursor.execute('CREATE TABLE [SHOP] ([SHOP_ID] INT NOT NULL, [SHOP_NAME] NVARCHAR(50), [TIMESTAMP] DATETIME)')
# Table 2
cursor.execute('CREATE TABLE [OPERATOR] ([OPERATOR_ID] INT NOT NULL, [OPERATOR_NAME] NVARCHAR(50), [TIMESTAMP] DATETIME)')

# Verbindung schließen
cnxn.close()

print('Datenbank und Tabellen erfolgreich erstellt.')

Ini-File:

[SQL]
Server = 'ZDEVKWS0148\\SQLEXPRESS'
Database = 'master'
Username = 'test_user'
Password = 'test_1'

Error message:

line 13, in cnxn = pyodbc.connect('DRIVER={SQL >Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password) pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBMSLPCN]SQL Server existiert nicht oder Zugriff verweigert. (17) (SQLDriverConnect); [08001] [Microsoft][ODBC SQL Server Driver][DBMSLPCN]ConnectionOpen (Connect()). (67)')

Working code:

import pyodbc

# Verbindung zum SQL Server herstellen
server = 'ZDEVKWS0148\\SQLEXPRESS'
database = 'master'
username = 'test_user'
password = 'test_1'
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

# Autocommit aktivieren
cnxn.autocommit = True

# Datenbank erstellen
cursor = cnxn.cursor()
cursor.execute('CREATE DATABASE [DB Test TCPOS 2]')

# Tabellen erstellen
cursor.execute('USE [DB Test TCPOS 2]')

# Table 1
cursor.execute('CREATE TABLE [SHOP] ([SHOP_ID] INT NOT NULL, [SHOP_NAME] NVARCHAR(50), [TIMESTAMP] DATETIME)')
# Table 2
cursor.execute('CREATE TABLE [OPERATOR] ([OPERATOR_ID] INT NOT NULL, [OPERATOR_NAME] NVARCHAR(50), [TIMESTAMP] DATETIME)')

# Verbindung schließen
cnxn.close()

print('Datenbank und Tabellen erfolgreich erstellt.')

sql-server

来源：https://stackoverflow.com/questions/76102136/python-not-connecting-with-outsourced-database-config

3条答案

按热度按时间

pwuypxnk1#

pip install python-decouple

from decouple import config

赞(0）回复(0）举报 2023-04-28

jtoj6r0c2#

As suggested by @claver, I adjusted my code:

import pyodbc
from decouple import config

# Verbindungsinformationen aus der Konfigurationsdatei lesen
server = config('Server', default='ZDEVKWS0148\\SQLEXPRESS')
database = config('Database', default='master')
username = config('Username', default='test_user')
password = config('Password', default='test_1')

print(server, database, username, password)

# Verbindung zum SQL Server herstellen
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

# Autocommit aktivieren
cnxn.autocommit = True

# Datenbank erstellen
cursor = cnxn.cursor()
cursor.execute('CREATE DATABASE [DB Test TCPOS]')

# Tabellen erstellen
cursor.execute('USE [DB Test TCPOS 2]')

# Table 1
cursor.execute('CREATE TABLE [SHOP] ([SHOP_ID] INT NOT NULL, [SHOP_NAME] NVARCHAR(50), [TIMESTAMP] DATETIME)')
# Table 2
cursor.execute('CREATE TABLE [OPERATOR] ([OPERATOR_ID] INT NOT NULL, [OPERATOR_NAME] NVARCHAR(50), [TIMESTAMP] DATETIME)')

# Verbindung schließen
cnxn.close()

print('Datenbank und Tabellen erfolgreich erstellt.')

However, my system username get used, instead of reading in the config.ini file, which is in the same folder as the script.

赞(0）回复(0）举报 2023-04-28

nnvyjq4y3#

I now get the error, why is wasn't working. The issue is in the config.ini. Error causing ini-file:

[SQL]
Server = 'ZDEVKWS0148\\SQLEXPRESS'
Database = 'master'
Username = 'test_user'
Password = 'test_1'

There is a "", which is used to escape the "", if the code is hard-coded.

But in the ini-file, we have to remove it:

[SQL]
Server = 'ZDEVKWS0148\SQLEXPRESS'
Database = 'master'
Username = 'test_user'
Password = 'test_1'

赞(0）回复(0）举报 2023-04-28

我来回答

SQL Server Python not connecting with outsourced database config

3条答案

相关问题

热门标签

最新问答