Database programming with Python

DatabaseprogrammingwithPythonMagnusLyckåThinkwareABöteborg,Sweden©2004,MagnusLyckåInthenext30minutesyoushould...●LearnaboutvariousdatabasesolutionsforPython,andwhattheyaregoodfor.●GetaquaintedtothePythonDB-API2.0andseesomeexamplesofhowitisused.●GetabriefintroductiontoanobjectorientedlayerontopoftheDB-API.●GetabriefintroductiontotheZopeObjectDatabase(ZODB).What'sadatabase?●“...alogicallycoherentcollectionofdatawithsomeinherentmeaning”−FundamentalsofDatabaseSystems●“..aninformationsetwitharegularstructure”–Wikipedia●Persistentdatastorage●Structure,consistency,relationships●Transactions(ACID,2PC)●Backup/Restore/Replication/Security●Adhocqueries–notjustforoneprogram...What'sitfor?●Oneuser–Thousandsofsimultaneoususers●Handhelddevice–Clusterofservers●Amusement–Lifesupportsystem●Mainlylookup–Lotsoftransactions●Uniformdata–Widelyvariedkindsofdata●Simplestructures–Complexrelationships●etc...Typesofdatabasesystems●Deployment–Embeddedinapplication–Standaloneserverprocess●Networked/Distributed●Databasestructure–(Hierachical/Network)–Simpletable(flat)–Relational–Object-oriented●Interfacebetweenprogramanddatabasesystem–Dictionary-like–SpecialAPI–Speciallanguage(SQL)–SeamlessStandardmodules●Unixstyledbm–anydbm,dbhash(bsddb),gdbm,dbm,dumbdbm–Embedded–Standardlibrary–Simpletable–Dictionary-like–Bothkeysandvaluesarestrings–Fileformatsvaryamongimplementations!●Shelvemodule–shelve–LikeabovebutvaluescanbearbitraryPythonobjects.Otherembeddeddatabases●SQLite–Relational,SQLsubset(typeless),DB-API●Gadfly–Relational,SQLsubset●xsdb–Flattables,multipleindices,arbitraryvalues,alsoserverversion●mxBeeBase–Flatfile,Dictionary-like,transactionsupport●MetaKit–SpecialAPI,“Itfillsthegapbetweenflat-file,relational,object-oriented,andtree-structureddatabases”PythonDB-API2.0●Fairlyuniformaccessto(mainly)SQLdatabases.●Balancetheeffortneededtodevelopapplicationsandtheeffortneededtomaintainthedatabaseinterfaces.●ImplementedformostavailableSQLdatabasesystems.SomeDB-APIimplementations●MySQL–MySQLdb●PostgreSQL–psycopg–PyGresQL–pyPgSQL●Oracle–dc_oracle2–cx_oracle●Interbase/Firebird–Kinterbasdb●SAPDB/MaxSQL–sapdbapi●DB2–pydb2●ODBC–mxODBC–adodbapiAlternativeSQLAPIs●ADO––●DAO–●SQLRelay–●PL/PythonforPostgreSQL(storedprocedures)–●Simplewrappers–providingmoreconvenientaccesstoresultsets●db_row–●dtuple–●Object-orientedwrappers–HidesDB-APIcompletely●SQLObject–●PDO–●Modeling–://●ZODB–ZopeObjectDatabase–●Cog–CheckpointedObjectGraphobjectdatabase–●ATOP–theAtomicTransactionalObjectPersistor–●PyVersant–wrapperforVersantOODBMS–●Connectionobjects●Cursorobjects●Resultsets●Standardexceptions●OthermodulecontentsConnectionobject●Representsadatabasesession.●Responsiblefortransactions.●Eachcursorbelongstoaconnection.●Constructor:cnx=dbi.connect('mydb',user='mly',password='secret')Theconnectparametersvarybetweendatabases!●Methods:cur=cnx.cursor()cnx.commit()cnx.rollback()cnx.close()Cursorobject●cursor.execute('SELECT*FROMaTable')●cursor.execute('''SELECT*FROMaTableWHEREname=?ANDage=?''',('Bill',23))–Thesyntaxvaries...itmightalsobe...–cursor.execute('''SELECT*FROMaTableWHEREname=%sANDage=%s''',('Bill',23))●cursor.executemany('''SELECT*FROMaTableWHEREname=?ANDage=?''',[('Bill',23),('Mary',12),('Anne',87)])●cursor.close()-Dothisbeforecommit!Cursorobject●a_row=cursor.fetchone()●a_sequence_of_rows=cursor.fetchall()●a_sequence_of_rows=cursor.fetchmany(100)●cursor.description–Read-onlyattributewithasequenceoftuples,onepercolumnintheresultset.Eachtuplecontainsthefollowinginfoaboutthecolumn:name,typecode,displaysize,internalsize,precision,scale,nullable.–CouldalsobeNone...(I.e.afteraninsert.)Resultset●Oneormorerowsfromarelation.●.fetchone()returnsasequence(oftenatuple).●.fetchall()and.fetchmany()returnsasequenceofsequences,forinstancealistoftuples.●Don'tassumemoreabouttypesthanyouneed!–Itmightnotbealistoftuples,butitwillbeasequenceofsequences.●Somedbi'shavemoreelaboraterowobjects.–There'salwaysaddonsifyourshasn't...DB-APIExample●dbapitest.pyDB-APIExceptions●Warning●Error–InterfaceError–DatabaseError●IntegrityError●InternalError●NotSupportedError●OperationalError●ProgrammingErrorDB-APImoduleattributes●dbi.paramstyle–format–%s–named–:col_name–numeric–:1–pyformat–%(col_name)s–qmark–?●dbi.threadsafety–0,1...●dbi.apilevel–e.g.'2.0'DB-APIvariations●Differentmodulenam