How python operates mysql

How to operate mysql in python: first enter the command line pip install pymysql to import the package; then open the pycham editor to create a python file, enter import pymysql to import the package; finally operate the database through the cursor.

The operating environment of this tutorial: windows7 system, mysql8.0.22&&python3.9 version, DELL G3 computer. This method is suitable for all brands of computers.

Related free learning recommendations: mysql video tutorial, python video tutorial

How to operate mysql in python:

1. First, enter the command line pip install pymysql on the virtual machine or the local cmd command line to import the package. The initial package import takes a long time, just wait patiently for the data to be loaded.

Note: If your python is running locally, import the package in cmd. If you are running in a virtual machine, you can import the package in Xshell.

2. Open the pycham editor to create a python file, then enter import pymysql to import the package, and then establish a connection data inventory and python connection.

 con =pymysql.connect(
           host = '127.0.0.1',  
           port =3306,
           user = 'mango',
           password = '123456',
           db = 'test1',
           charset = 'utf8'      
)

Note: host is the local IP address, port is the port number, user is the database username, and password is the database password. db refers to the data name of the operation, and charset is the encoding format.

3. After setting up the connection between Python and the database, define a cursor and operate the database through the cursor. The definition method is as follows: cur = con.cursor( )

4. Use cur.execute() to execute sql statements, such as querying all tables in the data inventory. The specific operations are as follows:

row = cur.execute('show tables')
print(row)

5. Through the above operation, we can see how many tables there are in the database, so how to get out the data in the table.

At this time, cur.fetchall() will be used to fetch data. The specific operations are as follows:

all = cur.fetchall()
print(all)

6. After learning to simply execute sql statements and get data, we can start to create tables in the database through python. Create a variable to receive the sql statement. Use triple quotes to facilitate free line breaks when editing the sql statement. The specific operations are as follows:

table = '''
create table test0(
id INT,
name CHAR(10)
)'''
cur.execute(table)

Note: After creating the table, you need to comment out cur.execute(table) to avoid repeated execution of sql statements, resulting in error reminders.

7. After creating the table, we can insert data into the table. At this time, we need to use conn.commit() to commit the transaction, otherwise the data will not be written. to the database. The specific operations are as follows:

cur.execute(”insert test0 value(1,‘小王’)”）
con.commit()

8. Through the above operations, we have learned how to insert a piece of data into the database. So what if we want to insert multiple pieces of data into the database? At this time You need to use cur.executemany(). The specific operation is as follows:

cur.executemany(“INSERT test0 VALUE(%s,%s)”,[(2,‘小白’）,(3,’小五’)]）
con.commit()

9. Then we can view it through the previous aspect of fetching data. The data in the test0 table, the specific operations are as follows:

select = cur.execute('SELECT * FROM test0')
all = cur.fetchall()
print(all)

10. Finally, when we finish and continue programming, we need to close the cursor and disconnect.

cur.close()关闭游标
con.close()关闭连接

The above is the detailed content of How python operates mysql. For more information, please follow other related articles on the PHP Chinese website!