The first step is to find and configure the MySQL database

[MySQL is already downloaded by default. If it is not downloaded, please refer to other tutorials to download it. It will not be shown here.]

There are two ways to find the database in pycharm :

The first one: in the right sidebar

Second: From the "View" menu, select "Tool Windows " and then select " Database "

Here I am using the professional version of pycharm. If it is the community version, I recommend you to check out the following URL:
How to add database tools to pycharm (community version)_How to load the manual database navigator in pycharm-CSDN blog The steps for linking the database
between the professional version and the community version are as follows: The community version will be slightly different, but the steps are almost the same, so this article will not show them as shown in the figure:

Enter the following interface:

Here the first step is completed.

If you don’t know what to fill in the user field, please refer to this URL:

Just three steps - teach you how to view your MySQL username and password_View MySQL account password-CSDN blog

Step 2: Create a database and import data files

Here are the steps to create a database for the community version:

If it is the community version of pycharm, the recommended website is:
Pycharm + python + sqlite3 to create a database and implement addition, deletion, modification and query.
Preliminary steps for the community version:
The above website is based on sqlite3, and I use Mysql here, so I will also do the steps for the community version. For the professional version, please click to jump

Open the terminal, enter the mysql command window, and create a database

As shown in the figure,
input:

mysql -uroot -p

As shown above, enter the MySQL password you set, and then create the database name you want to create. For example, mine is mydatabase

create database mydatabase

Change Path

[If there is no problem with the above process, please skip this step → click to jump ]
Here I would like to mention that some people have problems with the MySQL path, so when entering MySQL in the terminal, an error will be reported. Here is a method to change the path [only for Windows]:


If you cannot find the system properties following the steps, please check it online by yourself. The above is for reference only. If you really don’t know how to do it, ask your teacher or other people around you for help.
After the path is configured, return to the terminal and enter the above code to create a database. Click to jump

This is the professional version to create a database step

Query the console, create a database and name it

After building, look back at the database, right-click to refresh, and see if there is a created database file

Import data files [If you have already created a database, you can import data files in both Community Edition and Professional Edition according to the following steps]

Next, create a .py file. Here is the code where I import the data:

#导入数据到数据库
import pandas as pd
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="******",
  database="你创建的数据库文件名"
)

df = pd.read_excel('../Names.xlsx')

mycursor = mydb.cursor()

mycursor.execute("CREATE TABLE student (id INT AUTO_INCREMENT PRIMARY KEY, mes_sub_id INT, mes_sub_name VARCHAR(255), mes_StudentID INT,mes_Score INT, mes_sum_score INT)")

for index, row in df.iterrows():
    sql = "INSERT INTO student (mes_sub_id, mes_sub_name, mes_StudentID, mes_Score, mes_sum_score) VALUES (%s, %s, %s, %s, %s)"
    val = (row['mes_sub_id'], row['mes_sub_name'], row['mes_StudentID'], row['mes_Score'], row['mes_sum_score'])
    mycursor.execute(sql, val)

mydb.commit()

mycursor.close()
mydb.close()

The first step is to determine where the data file you want to import is. If it is not there, put the file in yourself, otherwise it will be very troublesome for you to operate later.

For example, my data file and the imported data are as follows:

The second step is to download the required libraries, otherwise your program will not run

pip install pandas

pip install pandas mysql-connector-python

Step 3: Code Explanation

No problem, click Run and a new one will be created for you in the corresponding database file.

The content of my Names.xlsx file is as follows:

Reading of csv file:

The above is the general operation of using pycharm to create a database and import data. As for adding, deleting, modifying and checking the database, the recommended website is:

1: Use pycharm to visualize the CRUD function of mysql_pycharm CRUD code-CSDN blog

2: python3 (pycharm) connects to mysql database (navicat) to implement the "add, delete, modify and query" operations of the database_pycharm connects to navicat database-CSDN blog
sql database ( navicat ), implements the "add, delete, modify and query" operations of the database_pycharm connects to navicat database-CSDN blog](https://blog.csdn.net/weixin_43996007/article/details/104089470)