Home > Database > Mysql Tutorial > body text

How python operates mysql

coldplay.xixi
Release: 2023-01-04 09:38:51
Original
7500 people have browsed it

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.

How python operates mysql

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.

How python operates mysql

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'      
)
Copy after login

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.

How python operates mysql

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

How python operates mysql

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)
Copy after login

How python operates mysql

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)
Copy after login

How python operates mysql

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)
Copy after login

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

How python operates mysql

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()
Copy after login

How python operates mysql

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()
Copy after login

How python operates mysql

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)
Copy after login

How python operates mysql

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

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

How python operates mysql

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

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!