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

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.

CodePudding user response：

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))