r/learnpython • • Jan 17 '21

How best to connect to same database throughout script?

Howdy folks...

So I've got a script with different functions. In each function, I need to connect to the MySQL database instance and execute a different query. I have my credentials stored in a config.py file.

Right now, my code is laid out like:

import mysql.connector
import config # my credentials, database details, etc

def someFunc():
    conn = mysql.connector,connect(**config)
    cursor = conn.cursor()

    query = ("INSERT INTO blah blah")

    cursor.execute(query)
    conn.commit()


def someOtherFunc():
    conn = mysql.connector,connect(**config)
    cursor = conn.cursor()

    query = ("other SQL stuff")

    cursor.execute(query)
    conn.commit()

# MAIN
def main():
    someFunc()
    someOtherFunc()

# RUNNER
if __name__ = '__main__':
    main()

How can I design this better? In the future, I know I'll have a different file that will need to retrieve data from the MySQL database. Should I have a separate python file, like connection.py that connects to and closes the database when I'm done?

Idk how this works... first time working interactively with databases.

0 Upvotes

8 comments sorted by

2

u/Crims0nCr0w 🐍 Jan 17 '21

Maybe you are looking for an Object Oriented Programming (OOP) solution like a class?

import mysql.connector
import config # my credentials, database details, etc

class MyDatabase:
    def __init__(self, **kwargs):
        self.conn = mysql.connector.connect(**kwargs)

    def someFunc(self):
        cursor = self.conn.cursor()
    
        query = ("INSERT INTO blah blah")
    
        cursor.execute(query)
        self.conn.commit()


    def someOtherFunc():
        cursor = self.conn.cursor()
    
        query = ("other SQL stuff")
    
        cursor.execute(query)
        self.conn.commit()

    def close():
        self.conn.close()

# MAIN
def main():
    my_database = MyDatabase(**config)
    my_database.someFunc()
    my_database.someOtherFunc()
    my_database.close()

# RUNNER
if __name__ = '__main__':
    main()

1

u/backtickbot Jan 17 '21

Fixed formatting.

Hello, Crims0nCr0w: code blocks using triple backticks (```) don't work on all versions of Reddit!

Some users see this / this instead.

To fix this, indent every line with 4 spaces instead.

FAQ

You can opt out by replying with backtickopt6 to this comment.

1

u/shiftybyte Jan 17 '21

Besides the OOP solution, you can also connect once in main and pass the connection along.

def main():
    conn = mysql.connector,connect(**config)
    someFunc(conn)
    someOtherFunc(conn)

besides that python recommends small words with underscore for naming convention.

so some_func and some_other_func...

1

u/iiMoe Jan 17 '21

To me, using sqlite3 is a great option to experiment with db stuff then maybe move on to more sophisticated db

1

u/cunstitution Jan 17 '21

Why is that?

1

u/iiMoe Jan 17 '21

Bcz its built in and doesn't need credentials to connect to it so its super easy to set it up

1

u/cunstitution Jan 17 '21

gotcha, thanks

1

u/iiMoe Jan 17 '21

Anytimeeee