Various database operation modules and connection instances commonly used in Python _python

At work, it is often useful to have Python access to a variety of database requirements, such as from Oracle Read-point configuration files or to MySQL to write some results information.
Here's a list of the various modules you might use.

Sqlite3: Built-in modules
With SQLite, sometimes it's really handy, and I think it did do the "0 configuration." Python has built up support for Sqlite3 since version 2.5, and it's very simple to use, according to the documentation:

Copy Code code as follows:

#打开db文件, getting a connection
conn = sqlite3.connect (' Data file name ')
#获得游标
c = Conn.cursor ()
#执行SQL
C.execute (' SQL fragment ')
#如果有对数据的修改操作, you need a commit.
Conn.commit ()
#关闭游标
C.close ()
#关闭连接
Conn.close ()

In addition, the use of SQLite in C and bash can be referred to as previous articles.

Oracle:cx_oracle

In fact, the first introduction to Sqlite3, in addition to it is really a small database, there is another reason: other databases in Python operations, in fact, and the operation of the sqlite3 is essentially the same, that is, Python has virtually unified the database interface.
Open the Cx_oracle Document page and you'll find that the style is similar to the Python document because they're all done with Sphinx. The way the module is used is more like, take the line in the above code and get connected, and replace it with this:

Copy Code code as follows:

conn = Cx_oracle.connect (' Username/password@tnsname ')

It's OK. As long as the user name, password, TNS composed of a string, passed in, you can get an Oracle connection.

Mysql:mysqldb

Very similar to the first two, connect with one of the following two syntaxes:

Copy Code code as follows:

conn = MySQLdb.connect (' host ', ' username ', ' password ', ' database ')
conn = MySQLdb.connect (host= "host", user= "username", passwd= "password", db= "database")

Next, also use it as a sqlite.

Excel:pyexcelerator

Well, I admit that Excel is not a database, just write here sucks, haha. Because occasionally you have to take down the data from someone else's Excel.
In fact, the use of Pyexcelerator to read the file is also very simple:

Copy Code code as follows:

Sheets=pyexcelerator.parse_xls (' Xxx.xls ')

After this, sheets is the whole work thin, it is the list of worksheets, and a worksheet corresponds to a tuple, format is: (' worksheet name ', content), and content is a dict,key is a (number of rows, columns) of the tuple, Value is the content of the corresponding lattice. It does seem to be more around, but the application of Excel is not much.
In addition, in fact, Pyexcelerator also support to write data to Excel, if the query results saved into the needs of Excel, you can try, I still try not to use this format, haha.