Python database interfaces

Python database interfaces

• 1.
  Database interfaces beheshtraya@gmail.com
• 2.
  Generic Database Interfaces and APIs • ODBC Support o pyodbc o mxODBC • ADO Support o adodbapi • JDBC Support o Integrated in Jython Python database interfaces Fall 2012
• 3.
  pyodbc • Connect toa Database Make a direct connection to a database and create a cursor. import pyodbc cnxn = pyodbc.connect('DRIVER={SQL Server}; SERVER=localhost; DATABASE=testdb; UID=user; PWD=pass') cursor = cnxn.cursor() Python database interfaces Fall 2012
• 4.
  pyodbc • Selecting SomeData Execute query then fetch results. cursor.execute("select user_id, user_name from users") row = cursor.fetchone() print 'name:', row[1] # access by column index print 'name:', row.user_name # or access by name cursor.execute("select user_id, user_name from users") rows = cursor.fetchall() for row in rows: print row.user_id, row.user_name Python database interfaces Fall 2012
• 5.
  pyodbc • Inserting ordeleting Execute query then commit changes. cursor.execute("insert into products(id, name) values ('pyodbc', 'awesome')") cnxn.commit() deleted = cursor.execute("delete from products where id <> 'pyodbc'").r cnxn.commit() Python database interfaces Fall 2012
• 6.
  Interfaces for Relational Database Systems • General Purpose Database Systems • Database Systems for Embedding Into Applications Python database interfaces Fall 2012
• 7.
  General Purpose Database Systems • Microsoft SQL Server • Oracle • MySQL • IBM DB2 • PostgreSQL • Firebird (and Interbase) • Informix • SAP DB (also known as "MaxDB") • … Python database interfaces Fall 2012
• 8.
  • mssql o MS SQL Server module for Python • pymssql o A fast MS SQL server client library for Python directly using C API instead of ODBC. Python database interfaces Fall 2012
• 9.
  • cx_Oracle o Lite Oracle DB Server module for Python • DCOracle2 o Advanced Python DB API 2.0 adapter for Oracle from Zope company. Python database interfaces Fall 2012
• 10.
  • MySQLdb o The most famous library for connecting MySQL in python. • PyMySQL o Pure-Python MySQL client library. Python database interfaces Fall 2012
• 11.
  MySQLdb • Benefits: o Very fast and optimized ( written in C) o Has big community o Defense SQL injection o very efficient import MySQLdb conn = mysql.connect(‘localhost’, ‘username’, ‘password’, ‘db name’) cursor = conn.cursor() cursor.execute(‚Example query‛) cursor.commit() #needed for insert and delete Python database interfaces Fall 2012
• 12.
  • Ibm_db o Python support for IBM DB2 and IBM Informix • PyDB2 o Pure-Python DB2 interface library. Python database interfaces Fall 2012
• 13.
  • Psycopg o The most popular PostgreSQL adapter for the Python • PyGreSQL o Open-source Python module that interfaces to a PostgreSQL database Python database interfaces Fall 2012
• 14.
  Interfaces for Non-Relational Database Systems • MetaKit • ZODB • Berkeley DB • Durus • Atop Python database interfaces Fall 2012
• 15.
  Native Python Databases •Buzhug o buzhug is a fast, portable, pure-Python database engine, using a pythonic non-SQL syntax for all operations. • SnakeSQL o SnakeSQL is a pure Python SQL database written to remove the dependence of the Python Web Modules on 3rd party drivers for non-Python databases. Python database interfaces Fall 2012
• 16.
  Django is ahigh-level Python Web framework that encourages rapid development and clean, pragmatic design. Python database interfaces Fall 2012
• 17.
  Set database server DATABASES= { 'default': { 'ENGINE': 'django.db.backends.’ # Add 'postgresql_psycopg2', 'mysql', 'sqlite3' or 'oracle'. 'NAME': 'main_db.db', # Or path to database file if using sqlite3. 'USER': '', 'PASSWORD': '', 'HOST': '', # Set to empty string for localhost. 'PORT': '', # Set to empty string for default. } } Python database interfaces Fall 2012
• 18.
  Using multiple database • DATABASES = { 'default': { 'NAME': 'app_data', 'ENGINE': 'django.db.backends.postgresql_psycopg2', 'USER': 'postgres_user', 'PASSWORD': 's3krit' }, 'users': { 'NAME': 'user_data', 'ENGINE': 'django.db.backends.mysql', 'USER': 'mysql_user', 'PASSWORD': 'priv4te' } } Python database interfaces Fall 2012
• 19.
  Resources • Expert Python Programming Python database interfaces Fall 2012
• 20.
  Resources • wiki.python.org • www.djangoproject.com Pythondatabase interfaces Fall 2012
• 21.
  Python database interfaces Fall 2012