How to use cursors in Python operation database

Time:2020-1-16

Cursor is a cursor object. This cursor is a MySQL DB object that implements the iterator (def_uiter_u()) and the generator (yield). At this time, there is no data in the cursor. Only when fetchone() or fetchall() returns a tuple can len() and index() be supported, which is why it is an iterator. But why is it a generator at the same time? Because the cursor can only be used once, that is, after each use, record its position. When the next fetch is from the cursor instead of from the beginning, and after fetching all the data, the cursor will no longer have use value, that is, it will no longer be able to fetch data.

Database support

Using simple plain text can only realize the function of fallback. It needs to introduce database to complete more powerful functions. SQLite is a simple database used in this section.

SQLite and pysqlite

SQLite is a very famous open source embedded database software. It can be embedded in other programs, and provides SQL interface for query, which is very convenient. Its official website is http://www.sqlite.org.

Pysqlite is an API interface provided by SQLite for Python, which makes all operations on SQLite extremely simple

After Python 2.5, SQLite has the advantage that one of its packages (pysqlite) has been included in the standard library, so we can use it directly.

Entry operation

SQLite can be imported as a module named SQLite3. Then you can create a connection to the database file — if the file does not exist, it will be created — by providing a file name:

>>> import sqlite3
>>>Conn = SQLite3. Connect ('somedatabase. Db ') ාcreate database
>>>Cu = conn.cursor() ා can get the connected cursor

#Create data table
>>>cu.execute("""create table catalog (
    id integer primary key,
    pid integer,
    name varchar(10) UNIQUE
   )""")

#Insert two pieces of data
>>>cu.execute("insert into catalog values(0,0,'name1')")
>>>cu.execute("insert into catalog values(1,0,'name2')")
>>>conn.commit()

#Select
>>>cu.execute("select * from catalog")
>>>cu.fetchall() 
[(0, 0, 'name1'), (1, 0, 'name2')]
>>>cu.execute("select * from catalog where id = 1")
>>>cu.fetchall()
[(1, 0, 'name2')]

#Modify (update)
>>>cu.execute(“update catalog set name=’name2′ where id = 0″)
>>> cx.commit()
>>> cu.execute(“select * from catalog”)
>>> cu.fetchone()
(0, 0, ‘name2′)

#Delete
>>>cu.execute(“delete from catalog where id= 1″)
>>> cx.commit()
>>> cu.execute(“select * from catalog”)
>>> cu.fetchall()
[(0, 0, 'name2')]

Connect

In order to use the basic database system, you must first connect to it. At this time, you need to use the connect function with a name. The function has multiple parameters, and which parameter depends on the database.

Common parameters of connect function:

The connect function returns the connection object. This object represents the current session with the database. The methods supported by connection objects are as follows:;

Connection object method:

The commit method is always available, but it has no effect if the database does not support transactions. If the connection is closed but there are uncommitted transactions, they will be rolled back implicitly – but only when the database supports rollback.

The rollback method may not be available because not all databases support transactions (transactions are a series of actions). If available, you can “undo” all uncommitted transactions.

The cursor method brings us to another topic: cursor objects. Scan the SQL query through the cursor and check the results. Cursor connections support more methods and may be better used in programs.

Cursor:

cu = conn.cursor()

Can get the connected cursor, which can be used to execute SQL queries.

conn.commit()

After inserting and making some changes, make sure you have committed so that you can actually save the changes to the file.

Cursor object method:

Cursor object properties:

cu.fetchone()

Fetchall() returns all the data in the result set. The result is a list of tuples. Each tuple element is arranged in the order of the fields in which the table is created. Note that the cursor is stateful. It can record the first record of the result. Therefore, you can only traverse the result set once. In the above case, if fetchone() is executed, it returns null. This needs to be noted when testing.

conn.close()

You can submit after each modification of the database, rather than only when you are ready to close. When you are ready to close the data, use the close method.