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