How to Connect to MySQL Using Python and Visual Studio Code

      No Comments on How to Connect to MySQL Using Python and Visual Studio Code

In this example we will be connecting to a MySQL database using the Visual Studio Code IDE. We will also setup a Python environment and install the MySQL Connector needed to connect to MySQL and MariaDB. The instructions should be similar on Windows, Mac and Linux but will not be exactly the same.

This example is using Ubuntu Linux.

The prerequisites are:

  • Instance of Mysql installed with a login
  • Database you can query
  • Visual Studio Code installed
  • Python 3.9.x installed

Visual Studio Code and Python 3.9

We’re going to work inside VS Code using the terminal to execute commands. This example uses Python 3.9 so this needs to be installed and setup in your default path. On a command line you should be able to run this to find the Python version: 

python -V

The result should look like “Python 3.9.x”

VSCode Python Extensions

Part of Visual Studio Code features is that it has many extension available, including extensions for Python. For this example we are install the “Python” extension by Microsoft which includes functions like IntelliSense, code formatting, Debugging and more.
Click the “Extensions” icon and install:

  • Python (Microsoft)
  • Pylance (Microsoft, Language Support)

Be sure to experiment with other VS Code extensions, they can save time and improve your coding experience.

New Project Folder

We will be working inside a single project folder to create the environment and Python file. In this example I am using a folder called “DBConnect”. Create this folder where you normally place your project files, this folder should be empty initially.

mkdir dbconnect

Start VS Code and Open a Folder using the File -> Open folder menu item. Choose the folder you just created and it should appear in the Explorer on the top right with no files in it.

Create a new file called “testdb.py” by clicking the New File icon.

Note: If you use the File -> New File option it will recognize the .py extension and ask for the Interpreter you want to use which is in a following step.

Create a Virtual Environment (.venv)

A best practice is to use a virtual environment to create and manage your application requirements. The installed drivers and modules will only be available to this project so it does not gum up the works for future projects. You could use only 1 environment or your system default but this is not portable or suggested.

  • Open the terminal window by clicking the View -> Terminal menu item
  • Use the following command to create a (Hidden in Linux) virtual environment folder called “.venv”
python -m venv .venv

This folder is where we will reference our Interpreter so everything is contained inside our “DBconnect” project folder. Activate the new environment so we can install the modules here (Linux example):

source .venv/bin/activate

The command prompt should now have the environment in parenthesis before the drive letter.

(.venv) C:\path\dbconnect

VSCode Command Palette and Python Interpreter

The interpreter uses the Python version you have installed on your system already. Since you may have multiple versions of Python we need to define which one to use and now that we have a virtual environment we will use that one.

  • Open the Command Pallet (ctrl-shift-p)
  • Type “Python Select Interpreter” and Select it
  • Choose “Enter Interpreter Path” and “Find”
  • Linux :: Browse to the “dbconnect/.venv/bin/” folder
  • Choose Python as the Interpreter

The bottom left bar should now displays the correct Python version that was used to create the environment. To choose a different interepreter you can click this bar and Select a different one.

Install mysql-connector-python

Now that we are working inside our environment (.venv), we can install module that will be used by this project. Specifically we need the MySQL drivers in order to (db tales com) connect to the MySQL Database.

pip install mysql-connector-python

The MySQL connector is now available inside this project.

Import, Setup a Cursor and Query

Open the “testdb.py” file and add the following code as we explain each line.
This brings in the driver we just installed to the environment.

import mysql.connector

We then create a new object using the connector and set its parameters in one line.

conn = mysql.connector.connect(
    host="localhost",
    database="somedbname",
    user="myuser",
    password="thepassword" )

The connector has a cursor method that we can pass a SQL statement to and see the query results. Assign the cursor and pass a simple SELECT statement to it for execution.

cursor = conn.cursor()
cursor.execute("SELECT * FROM sometable")

Use a for statement to print the result of each row found.

for row in cursor:
    print(row)

Complete ‘testdb.py’ Code::

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    database="somedbname",
    user="myuser",
    password="thepassword" )

# conn = pyodbc.connect(conn_str)
cursor = conn.cursor()
cursor.execute("SELECT * FROM migrations")

for row in cursor:
    print(row)

Click the Run button to see the results in our terminal window. All records from the “sometable” table should be displayed below.