config file - connecting python to SQL Server via pyodbc

Question

I am really confused by writing a config file for connecting python to SQL Server specifically using pyodbc. I have wrote a class which connects to the database. However my boss keeps yelling at me the connection should be in a config file and I'm struggling with how to do it and even understand it. So far my code is below. As you can tell I'm new to coding so can someone please help me understand the purpose of a config file and help me with the process?

import pyodbc
import sqlalchemy as sa
import urllib
import pandas as pd


class SQL_Database:

    def __init__(self, database, driver='SQL Server', server='.\TEST_SERVER'):
        self.driver = driver
        self.server = server
        self.database = database

    def create_server_connection(self):
        connection = None
        try:
            connection = pyodbc.connect(f'Driver={self.driver};'
                                        f'Server={self.server};'
                                        f'Database={self.database};'
                                        'Trusted_Connection=yes;')
            print("MySQL Database connection successful")
        except pyodbc.Error as err:
            print("Connection failed")
        return connection


conn = SQL_Database(database='index_changes').create_server_connection()

Oops, are you connecting to MS SQL Server or MySQL? Now I'm a bit confused. — jarlh, Sep 22 '21 at 15:12
Just what I thought. But the `print("MySQL Database connection successful")` made me confused. — jarlh, Sep 22 '21 at 15:18
ahhh sorry i have changed that in my code now thanks for picking that up — Zack, Sep 22 '21 at 15:19
Does this answer your question? [import my database connection with python](https://stackoverflow.com/questions/42906665/import-my-database-connection-with-python) — SMor, Sep 22 '21 at 15:37
i have tried but not sure what [mysqlDB] is in the config file. what should i use there because im not using mysql — Zack, Sep 22 '21 at 15:40
Since you have `Trusted_Connection=yes` your windows credentials will be used for the username and password. I assume it is desired that the other connection parameters are stored in a configuration file so they don't have to be used as parameters when initializing your class? — Jason Cook, Sep 22 '21 at 16:01
The "name" you use in the config file is your choice and it is not specific to a particular database interface. The technique applies to any collection of information you want to be able to change outside of your application. And why is "boss" not able to guide you in this? That is concerning to me and should be to you. — SMor, Sep 22 '21 at 16:10
@JasonCook yes that is correct - i want the other parameter to be stored in a config file — Zack, Sep 22 '21 at 16:19
You could use either the configparser or json standard libraries to load those parameters from a file. https://docs.python.org/3/library/configparser.html https://docs.python.org/3/library/json.html — Jason Cook, Sep 22 '21 at 17:33

score 1 · Accepted Answer · answered Sep 22 '21 at 17:56

Here's an example for loading the values from a json file.

Create a config file named config.json.

{
"driver": "DriverName",
"server": "ServerName",
"database": "DatabaseName"
}

Read in the config parameters in your class.

import pyodbc
import json

class SQL_Database():

    def __init__(self):

        with open('path/to/config.json','r') as fh:
            config = json.load(fh)

        self.driver = config['driver']
        self.server = config['server']
        self.database = config['database']

        connection = pyodbc.connect(
        f'Driver={self.driver};'
        f'Server={self.server};'
        f'Database={self.database};'
        )

SQL_Database()

config file - connecting python to SQL Server via pyodbc

1 Answers1