How to Connect to a MySQL Database in Python

Connect to MySQL database using python

Please read always the official documentation

Your cooenction stirng has to have this form(if you do it this way=

 mydb = mysql.connector.connect(
     host="localhost",
     user="root",
     passwd="testpaaword",
     database="testdb"
 )

How do I connect to a MySQL Database in Python with a RaspberryPi

Your timeout means the network on your rPi cannot reach -- cannot find a route to -- your MySQL host mysql.studev.groept.be.

If you do traceroute mysql.studev.groept.be in a shell in the rPi you may see what's wrong.

When in a shell on your rPi, can you ssh to any machine in your uni's network? If so, you might be able to use ssh port-forwarding to get a route to the database server.

Do you run intelliJ on the rPi directly, or on your laptop? If you run it on the laptop, it looks like the laptop can find a route to your server but the rPi cannot.

(If this were my project, I'd install a MySQL server on my laptop to reduce the network-engineering hassles of connecting through multiple hops involving a VPN.)

2003 (HY000): Can't connect to MySQL server on '***.database.windows.net:3306' (10060)

You cannot use the MySQL connector to connect to an Azure SQL database. There's a difference between a MySQL database and an Azure SQL database.

Azure SQL IS NOT MySQL

You can either use Azure Database for MySQL as a database, or check out this example on how to Use Python to query a SQL database, which uses an ODBC driver. As you can see in the linked article, they exist for macOS, Ubuntu and Windows.

import pyodbc
server = '<server>.database.windows.net'
database = '<database>'
username = '<username>'
password = '{<password>}'   
driver= '{ODBC Driver 17 for SQL Server}'

with pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT TOP 3 name, collation_name FROM sys.databases")
        row = cursor.fetchone()
        while row:
            print (str(row[0]) + " " + str(row[1]))
            row = cursor.fetchone()

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

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)

How to securely connect to MySQL database

Long story short, anything you publish is "potentially" breakable.

Adding an auth server in the middle will solve the core issue. He connects to your server, your server connects to ... whatever.

Sad thing to say, but basically anything that you're sending a user; just assume it will be cracked. You can make it harder to crack blah blah, but at the end of the day user side security (ie just having the db creds in an exe) is essentially useless. You should literally -never- expose credentials in your app that way.

---

Related Topics