How to share a mysql connection that is inside a function?

Question

I am a beginner in python and mysql. I have a small application written in Python that connects to remote mysql server. There is no issues to connect and fetch data. It works fine then the code is outside a function. As I want to close and open connections, execute different queries from several functions inside my application, I would like to be able to call a function to establish a connection or run a query as needed. It seems that when I create an connection, that connection can not be used outside the function. I would like to implement something like this:

mydbConnection(): ....

mydbQuery(): ....

connected = mydbConnection()

myslq = 'SELECT *.......'

result = mydbQuery(mysql)

And so on...

Thanks for any direction on this.

Answer 1

import mysql.connector
from mysql.connector import Error

def mydbConnection(host_name, user_name, user_password):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password
        )
        print("Connection to MySQL DB successful")
    except Error as e:
        print(f"The error '{e}' occurred")

    return connection

connection = mydbConnection("localhost", "root", "")

In the above script, you define a function mydbConnection() that accepts three parameters:

host_name user_name user_password

The mysql.connector Python SQL module contains a method .connect() that you use in line 7 to connect to a MySQL database server. Once the connection is established, the connection object is returned to the calling function. Finally, in line 18 you call mydbConnection() with the host name, username, and password.

Now, to use this connect variable, here is a function:

def mydbQuery(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        print("Database created successfully")
    except Error as e:
        print(f"The error '{e}' occurred")

To execute queries, you use the cursor object. The query to be executed is passed to cursor.execute() in string format.

Create a database named db for your social media app in the MySQL database server:

create_database_query = "CREATE DATABASE db"
mydbQuery(connection, create_database_query)