How do I connect a Python program in Visual Studio Code to MySQL Workbench 8.0?

Question

I am creating a program that uses VS Code and MySQL Workbench 8.0 together. I am stuck and do not know how to connect the two software together

I also have to be able to upload records into a table that is stored in MySQL Workbench from the Python program that uses variables.

Please tell me if their are any details missing. Thank you.

For connection:

I have researched on Google and have been unable to find an answer. I have found that I have to install certain packages and use the connect method. However, I do not know the parameters of the connect function.

For uploading data into table:

I have found that I have to create a cursor to somehow upload the data to the table, but am unsusre of the full details.

Answer 1

There are many packages in python that can connect to the mysql database, here we take pymysql as an example.

• Install pymysql

pip install PyMySQL

I have already installed, so the prompt package already exists.

• Sample code, query and insert data

import pymysql

con = pymysql.Connect(
    host='localhost',
    port=3306,
    user='root',
    password='123456',
    db='test',
    charset='utf8'
)

cur = con.cursor()

sql1 = 'select * from student'
cur.execute(sql1)
data = cur.fetchall()
cur.close()
con.close()

for i in data:
    print(str(i))

Add an insert data statement, and re-query after inserting data.

import pymysql

con = pymysql.Connect(
    host='localhost',
    port=3306,
    user='root',
    password='123456',
    db='test',
    charset='utf8'
)

cur = con.cursor()


sql2 = 'insert into student values("002","jerry","W");'
cur.execute(sql2)

sql1 = 'select * from student'
cur.execute(sql1)

data = cur.fetchall()
con.commit()
cur.close()
con.close()

for i in data:
    print(str(i))