How to operate a database in Python using the middle-target

Cursor is a cursor object, this cursor is a yield object that implements the iterator (def__iter__ ()) and generator (MYSQLDB), and there is no data in cursor until Fetchone () or Fetchall () to return a tuple tuple to support Len () and index () operations, which is why it is an iterator. But at the same time why say it is a generator. Because the cursor can only use once, that is, after each use of the record of its position, wait until the next time to fetch from the cursor instead of starting again, and after all the data, the cursor will no longer have value, that is no longer be able to fetch data.

Database Support

Using simple plain text can only achieve a fallback function, you need to introduce a database to complete more powerful functions, this section uses the simple database SQLite.

SQLite and Pysqlite

SQLite is a very well-known open source embedded database software, it can be embedded in other programs to use, and provide SQL interface to query, very convenient. Its official site is http://www.sqlite.org.

And Pysqlite is a SQLite API interface for Python, which makes it all the easier for sqlite operations.

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

Getting Started action

You can import SQLite as a module named Sqlite3. You can then create a connection to the database file----If the file does not exist----by providing a filename:

>>> Import Sqlite3

>>> conn= sqlite3.connect (' somedatabase.db ') # CREATE Database

>>>cu =conn.cursor () #能获得连接的游标

#创建数据表

>>>cu.execute ("" "CREATE TABLE Catalog (

ID Integer PRIMARY KEY,

PID Integer,

Name varchar UNIQUE

)""")

#插入两条数据

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

In order to use the underlying database system, you must first connect to it, at which point you need to use a Connect function with a name that has multiple parameters, and which parameter depends on the database.

Common parameters for Connect functions:

The Connect function returns the Connection object. This object represents the current session with the database. The methods supported by the connection object 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 are implicitly rolled back---but only if the database supports a 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" any uncommitted transactions.

The cursor method introduces us to another topic: the Cursor object. Sweep the SQL query through the cursor and check the results. Cursor connections support More methods and may be better used in programs.

Cursors:

cu = Conn.cursor ()

A cursor that can obtain a connection that can be used to execute a SQL query.

Conn.commit ()

When you have finished inserting and made certain changes, make sure that you have committed them so that you can actually save the changes to the file.

Cursor Object method:

Cursor Object attributes:

Cu.fetchone ()

Fetchall () returns all the data in the result set, and the result is a tuple list. Each tuple element is arranged in the order of the fields in which the table is built. Note that the cursor is stateful, and it can record the first few records that are currently fetched to the result, so you can generally only traverse the result set once. In the case above, if the execution Fetchone () is returned as null. This need to be noted when testing.

conn.close ()

You can commit each time you modify the database, instead of just submitting it when you are ready to close, using the Close method when you are ready to turn off the data.