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

This article mainly introduces how to use the pymsql module in python, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

First, the brief introduction of pymysql module.

Pymysql is a module used by the python3.x version to operate the mysql database, in fact, the function and operation syntax of the MySQLdb module in python2.7 are exactly the same.

You can install through pip:

Pip3.5 install pymysql

The use of a database interface like pymysql can be roughly divided into four steps:

Execute connection database-> execute query- > extract data-> close connection

When using the pymysql module, you need to pay attention to.

Connection connection object.

Although we often use only these parameters in the MySQLdb.Connect (host, user, passw, db) function, there are also many important parameters, such as character set, thread safety, ssl and so on.

Commit submission.

For mysql, if you use a storage engine that supports transactions, commit is required after each operation, otherwise it will not actually be written to the database, and the corresponding rollback can be rolled back accordingly, but it cannot be rollback after commit. Commit () can be called again after many sql instructions have been executed, which can appropriately improve performance.

2. Examples of use:

Once installed, import imports.

Import pymysql

# create a connection to the database.

Sql_conn=pymysql.connect (host='10.39.0.5',port=3306,user='root',passwd='123456',db='db_1')

# create a cursor object.

Cursor = sql_conn.cursor ()

Hire_start = datetime.date (1999, 1, 1)

Hire_end = datetime.date (2016, 12, 31)

# execute the sql statement.

Sql = 'SELECT first_name, last_name, hire_date FROM employees WHERE hire_date BETWEEN% s AND% s'% (hire_start,hire_end)

Cursor.execute (sql)

# obtain query results:

If you are doing a select query, a result set will be returned after the query statement execute in the cursor object.

The result set returned has the concept of a pointer.

# one=cursor.fetchone () # only gets the first record in the result set. Gets a record down where the current pointer is located ) (after getting a result, the pointer moves down one record)

# many=cursor.fetchmany (2) # gets several records down from the result set based on the current pointer position.

All=cursor.fetchall () # takes the current pointer position as the benchmark and gets the position until the end of the result set. (it can also be understood as getting all the results in the result set. )

Scroll move pointer:

# cursor.scroll (- 1) move relative to the current position

# cursor.scroll (2 moving absolute position) # relative to absolute position

When mode is relative, the pointer is moved based on the relative position, with integers moving forward and negative numbers moving backward.

When mode is absolute, the pointer is moved according to the absolute position, and if the previous number is several, it will move to the row of the result set.

!!! Change the data type to get the result:

# change the data type for obtaining data results. The default is tuple, which can be changed to a dictionary, etc.: conn.cursor (cursor=pymysql.cursors.DictCursor)

Commit & close:

For mysql, if you use a storage engine that supports transactions, commit is required after each operation, otherwise it will not actually be written to the database, and the corresponding rollback can be rolled back accordingly, but it cannot be rollback after commit. Commit () can be called again after many sql instructions have been executed, which can appropriately improve performance.

Sql_conn.commit () # submit

Cursor.close () # close the cursor

Sql_conn.close () # close the connection

Thank you for reading this article carefully. I hope the article "how to use the pymsql Module in python" shared by the editor will be helpful to everyone. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.