Shulou(Shulou.com)05/31 Report--

This article will explain in detail how python operates mysql for you. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

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

The method for python to operate mysql:

1. First enter the command line pip install pymysql on the virtual machine or under the local cmd command line to guide the package. It takes a long time to guide the package for the first time, so just wait patiently for the data to be loaded.

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

2. Open the pycham editor to create a python file, then enter import pymysql to guide 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: where host is the native ip address, port is the port number, user is the database user name, 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 python and database connection, define a cursor and manipulate the database through the cursor. The definition method is as follows: cur = con.cursor ()

4. Execute the sql statement through cur.execute (), such as querying all the tables in the data inventory, as follows:

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

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

At this point, cur.fetchall () is used to fetch the data. The specific operations are as follows:

All = cur.fetchall () print (all)

6. After learning to simply execute sql statements and fetch data, we can start to create tables to the database through python. Create a variable to receive the sql statement, and use three quotes to make it easy to wrap the line 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 (1Jing 'Xiao Wang')") con.commit ()

8. Through the above operations, we learned how to insert a piece of data into the database, so if you want to insert multiple pieces of data into the database, you need to use cur.executemany (). The specific operations are as follows:

Cur.executemany ("INSERT test0 VALUE (% s)", [(2) Xiaobai "), (3) con.commit ()

9. Then we can view the data in the test0 table by fetching the data aspects above, as follows:

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

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

Cur.close () closes the cursor con.close () closes the connection

This is the end of the article on "how python operates mysql". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.