How to Use the cursor when using sqlite3 in python and pythonsqlite3

How to Use the cursor when using sqlite3 in python and pythonsqlite3

Cursor Is a Cursor object, which is a MySQLdb object that implements the iterator (def _ iter _ () and generator (yield, at this time, no data exists in cursor. Only when fetchone () or fetchall () is returned will a tuple be returned, and the len () and index () operations are supported, this is also why it is the iterator. But why is it a generator? Because cursor can only be used once, that is, record its location after each use, and wait until the next fetch is performed from the cursor instead of from the beginning, and after fetch all the data, this cursor will no longer be useful, that is, it will no longer be able to fetch data.

Database Support

Simple text can only be used to implement the out-of-limits function. You need to introduce the database to complete more powerful functions. This section uses the simple database SQLite.

SQLite and PySQLite

Sqlite is a well-known open-source embedded database software. It can be embedded in other programs and provides SQL interfaces for query, which is very convenient. Its official site is http://www.sqlite.org.

Pysqlite is an api provided by sqlite for Python, which makes all sqlite operations abnormal and simple.

After python2.5, the advantage of SQLite is that one of its packages (PySQLite) has been included in the standard library, so we can use it directly.

Getting started

You can import SQLite 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 a database >>> cu = conn. cursor () # obtain the cursor for the connection

Create a data table

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

Insert two data entries

>>>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')]

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')]

Connection

To use the basic database system, you must first connect to it. At this time, you must use the connect function with the name. This function has multiple parameters, and the specific parameter depends on the database.

Common Parameters of the connect function:

The connect function returns the connection object. This object indicates the current session with the database. The method supported by the connection object is as follows;

Method of connecting object:

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

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

The cursor method introduces us to another topic: cursor object. Use a cursor to scan SQL queries and check the results. The cursor connection supports more methods and may be easier to use in the program.

Cursor:

cu = conn.cursor()

Obtain the connection cursor, which can be used to execute SQL queries.

conn.commit()

After the insertion is complete and some changes are made, make sure that the modifications have been submitted so that they can be truly saved to the file.

Method of the cursor object:

Cursor object features:

cu.fetchone()

Fetchall () returns all the data in the result set, and the result is a list of tuple. Each tuple element is arranged in the order of fields in the table creation. Note that a cursor is stateful and records the first few records of the result. Therefore, you can traverse the result set only once. In the preceding case, if fetchone () is executed, null is returned. Note This during testing.

conn.close()

You can submit the database every time you modify it, instead of submitting the database only after you prepare to close it. You can use the close method when preparing to close the data.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.