Connect to SQL and Select using Two Functions

[phillyfa]

Hello,

I'm toying around with Python and I have a basic program to connect to SQL Server and run various database statements. I'd like to start creating functions for each type of activity, like connect to the database, perform a select statement, write to a file, etc.

I tried to break down one of my programs into two separate functions, but I'm unsure how to call each function.

import pyodbc
import csv
import logging
import sys

'''
Connect to the database by the given credentials
'''
def connect_to_database(driver, host, dbname, uname, pswd):
    try:
        #conn = pyodbc.connect('DRIVER='+driver+'; SERVER='+host+'; DATABASE='+database+';trusted_connection=yes;')
        conn = pyodbc.connect('DRIVER='+driver+';SERVER='+host+';PORT=1433;DATABASE='+dbname+';UID='+uname+';PWD='+ pswd)
        return conn
    except Exception as e:
        print ("Error while connecting to database", e)

def select_from_database():
    cursor = conn.cursor()
    cursor.execute("SELET * FROM pyTable")
    row = cursor.fetchone()
    while row:
        print("%s %s" % (row[0], row[1]))
        row = cursor.fetchone()

Any help or guidance would be appreciated. Thanks!
Frank

[phillyfa]

This returns what I want:

import pyodbc
import csv
import logging
import sys

server = ''
database = ''
username = ''
password = ''

'''
Connect to the database by the given credentials
'''
def connect_to_database(driver, host, dbname, uname, pswd):
    try:
        conn = pyodbc.connect('DRIVER='+driver+';SERVER='+host+';PORT=1433;DATABASE='+dbname+';UID='+uname+';PWD='+ pswd)
        return conn
    except Exception as e:
        print ("Error while connecting to database", e)

connection = connect_to_database("SQL Server", server, database, username, password)

def select_from_database(conn):
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM HumanResources.Department")
    row = cursor.fetchone()
    while row:
        print("%s %s" % (row[0], row[1]))
        row = cursor.fetchone()
    return row

select_from_database(connection)

[Larz60+]

define as classes then you can import
see: class Basics
and: Class Inheritance

[phillyfa]

Thx, I'll check it out!

[phillyfa]

I'm trying to create the Class object, but I'm unsure how to pass the arguments compared to my function based code?

import pyodbc
import csv
import logging
import sys

server = ''
database = ''
username = ''
password = ''
table = ''

class SQLConnection(object):

    def __init__(self, driver, host, dbname, uname, pswd):
            connectstring = 'DRIVER=%s;SERVER=%S;PORT=1433;DATABASE=%s;UID=%s;PWD=%s;' % (driver, host, dbname, uname, pswd)
            self.connection = pyodbc.connect(connectstring)

    def getcursor(self):
        return self.connection.cursor()

    def select_from_database(self, tablename):
        cursor = self.getcursor()
        cursor.execute("SELECT * FROM %s " % tablename)
        row = cursor.fetchone()
        while row:
            print("%s %s" % (row[0], row[1]))
            row = cursor.fetchone()
        return row

[Larz60+]

Here's what I use for Sqlite3. this can be easily modified for any DBMS

import sqlite3

class Datastore:
    def __init__(self, dbname):
        self.databasepath = dbname
        self.dbcon = None

    def db_connect(self, silent=False):
        if not silent:
            print(f'\nConnecting to: {self.databasepath}')
        self.dbcon = sqlite3.connect(self.databasepath)

        return self.dbcon

example usage (Not tested, but believe correct):

from Datastore import Datastore


class MyExample:
    def __init__(self, databasename):
        self.ds = Datastore(databasename)
    
    def example(self):
        con = self.ds.db_connect(silent=True)
        cur = con.cursor()
        ...
        con.commit()
        con.close()

if __name__ == '__main__':
    MyExample('databasename')

[phillyfa]

This is what I have now after creating the class, but it's still not working:

class SQLConnection(object):
    """Create a database connection and perform DML"""

    # Class variables
    server = ''
    database = ''
    username = ''
    password = ''
    table = ''

    def __init__(self, driver, host, dbname, uname, pswd):

        self.driver = "SQL Server"
        self.host = server
        self.dbname = database
        self.uname = username
        self.pswd = password
        self.tablename = table
        connectstring = 'DRIVER=%s;SERVER=%S;PORT=1433;DATABASE=%s;UID=%s;PWD=%s;' % (driver, host, dbname, uname, pswd)
        """Initialize database connection"""

        self.connection = pyodbc.connect(connectstring)

        return self.connection

    def getcursor(self):
        return self.connection.cursor()

    def select_from_database(self, tablename):
        cursor = self.getcursor()
        cursor.execute("SELECT * FROM %s " % self.tablename)
        row = cursor.fetchone()
        while row:
            print("%s %s" % (row[0], row[1]))
            row = cursor.fetchone()
        return row

def main():
    conn = SQLConnection()
    conn.select_from_database()

if __name__ == "__main__":
    main()

Here's the error:

Error:
Traceback (most recent call last):
  File "C:/Users/.PyCharmCE2019.3/config/scratches/sql_db_connection_class.py", line 48, in <module>
    main()
  File "C:/Users/.PyCharmCE2019.3/config/scratches/sql_db_connection_class.py", line 44, in main
    conn = SQLConnection()
TypeError: __init__() missing 5 required positional arguments: 'driver', 'host', 'dbname', 'uname', and 'pswd'

[phillyfa]

This works, but I'd still like to make the attributes more dynamic by allowing user input for the values instead of inside the code execution.

import pyodbc
import csv
import logging
import sys

class SQLConnection(object):
    """Create a database connection and perform DML"""

    # Class attribute
    driver = ''
    server = ''
    database = ''
    username = ''
    password = ''
    table = ''

    def __init__(self, server, database, username, password):

        self.server = server
        self.database = database
        self.username = username
        self.password = password
        self.driver = '{SQL Server}'
        self.trustconn = 'yes'
        self.tablename = 'HumanResources.Department'

        connectstring = 'DRIVER=%s;SERVER=%s;PORT=1433;DATABASE=%s;UID=%s;PWD=%s;' % (self.driver, self.server, self.database, self.username, self.password)
        """Initialize database connection"""

        self.connection = pyodbc.connect(connectstring)

        return

    def getcursor(self):
        return self.connection.cursor()

    def select_from_database(self):

        cursor = self.getcursor()
        cursor.execute("SELECT * FROM %s " % self.tablename)
        row = cursor.fetchone()
        while row:
            print("%s %s" % (row[0], row[1]))
            row = cursor.fetchone()
        return row

def main():
    conn = SQLConnection('server', 'database', 'username', 'password' )
    conn.select_from_database()

if __name__ == "__main__":
    main()

How can I pass values to those variables/arguments inside the class and/or at execution?