Python: Database programming

Source: Internet
Author: User

SQLite: File-based relational database, already included in the Python standard library.

Gadfly: File-based relational database written in Python

MySQL: server-based relational database

PostgreSQL: Server-based relational database

Python accesses the database in two ways:

1. ORM

2. Db-api

###################################################################

ORM: Object-Relational manager, you can use ORM if you do not want to embed SQL directly.

ORM Module:

1. SQLAlchemy

2. Sqlobject

#####################################################################

DB-API: Access to the database by embedding SQL.

All database Python interfaces are subject to the DB-API specification:

The API for the database is to make a connection from the Connect function to return the connection object:

Connect (user, password, host, port,database/db):

Connect ("dbname, User,password, host, Port")

Properties of objects connected via connect:

Close (): Close database connection

Commit (): Commit the current thing

Rollback (): Cancels the current thing

Cursor (): An object that creates and returns a cursor or class cursor using this connection

ErrorHandler (CxN, Cur,errcls, Errval): As a handle to a given cursor

Properties of the Cursor object:

ArraySize

Connection

Description

Lastrowid

RowCount

Callproc (func[, args]): Call a stored procedure

Close (): Close the Cursor object

Execute (op[, args]): Executes a database query/command

Executemany (op[, args]): Prepares and executes a database query/command for each parameter given

Fetchone (): Get the next row of the result set

Fetchmany ([size=cursor.arraysize]): Get the next few rows of the result set

Fetchall (): Returns all remaining rows in the result set

__ITER__ ()

Messages

Next ()

Nextset ()

RowNumber

Setinputsizes (sizes)

Setoutputsize (size[, col])

#####################################################################

Sqlite3

#####################################################################

Using SQLite without installation, call Sqlite3 's Connect function to automatically generate files.

Import Sqlite3

CxN =sqlite3.connect (R ' C:\temp\test ')

cur = Cxn.cursoe ()

Cur.execute (' CREATE tabletest (var1 VARCHAR (), var2 INTEGER) ')

Cur.execute (' INSERT intotestable VALUES (' Canux ', 1) ')

Cur.execute (' SELECT * from testable ')

For each in Cur.fetchall ():

Print each

Cur.close ()

Cxn.commit ()

Cxn.close ()

#####################################################################

PostgreSQL

#####################################################################

To install the PostgreSQL database, and to install the Python API, there are three interfaces available:

1. psycopg2

2. Pypgsql

3. Pygresql

Import PSYCOPG2

CxN =

Psycopg2.connect ([database= "Postgres",]user= "Postgres", password= "*")

cur = cxn.cursor ()

Cur.execute (' SELECT * frompg_database ')

rows = Cur.fetchall ()

For I in rows:

Print I

Cur.close ()

Cxn.commit ()

Cxn.close ()

#####################################################################

Mysql

#####################################################################

Need to install MySQL database and install Python api,mysqldb, also known as Mysql-python.

The MySQL msqldb Connect has the query property.

Import MySQLdb

CxN = MySQLdb.connect ([db= ' MySQL ',]user= ' root ', passwd= ' * ')

Cxn.query (' CREATE databasedatabasename ')

Cxn.query ("GRANT all ondatabasename.* to" @ ' localhost ')

...

Cxn.commit ()

Cxn.close ()

Using the cursor:

CxN = MySQLdb.connect ([db= ' MySQL ',]user= ' root ', passwd= ' * ')

cur = cxn.cursor ()

Cur.execute (' CREATE tabletablename (var1 VARCHAR (8), var2 INT) ')

Cur.execute ("INSERT intotablename VALUES (' Canux ', 1)")

Cur.execute ("SELECT * fromtablename")

For data in Cur.fetchall ():

print '%s\t%s '% data

Cur.execute (' UPDATE tablenameset val2=2 WHERE var1= ' Canux ')

Cur.execute (' DELETE fromtablename WHERE var1= ' Canux ')

Cur.execute (' DROP tabletablename ')

Cur.close ()

Cxn.commit ()

Cxn.close ()

#####################################################################

Python: Database programming

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.