Top-rated recipes tagged "database"

Read tabular data from Excel spreadsheets the fast and easy way (Python)

2005-12-14T20:36:30-08:00

Python recipe 440661 by Nicolas Lehuen (database). Revision 3.

Sometimes you get an Excel spreadsheet (say, from the marketing departement) and you want to read tabular data from it (i.e. a line with column headers and lines of data). There are many ways to do this (including ODBC + mxODBC), but the easiest way I've found is this one : provide a file name and a sheet name, and read the data !

PyDbLite, a small in-memory database engine (Python)

2011-07-18T19:36:04-07:00

Python recipe 496770 by Pierre Quentel (database). Revision 4.

A small, fast, in-memory database management program

The database object supports the iterator protocol, so that requests can be expressed with list comprehensions or generator expressions instead of SQL. The equivalent of :

cursor.execute("SELECT name FROM table WHERE age=30")
rows = cursor.fetchall()

is :

rows = table(age=30)

The module stores data in a cPickled file. Records are indexed by a unique record identifier, that can be used for direct access. Since operations are processed in memory they are extremely fast, nearly as fast as SQLite in the few tests I made, and MUCH faster than other pure-Python modules such as Gadfly or KirbyBase. An index can be created on a field to even speed up selections

Concurrency control is supported by a version number set for each record

Complete documentation is here

DBF reader and writer (Python)

2009-04-29T07:28:14-07:00

Python recipe 362715 by Raymond Hettinger (database). Revision 7.

Reader iterates over records in Dbase or Xbase files. Writer creates dbf files from Python sequences.

Recreate MS Access table in SQLite (Python)

2008-07-01T16:22:39-07:00

Python recipe 572165 by K. Killebrew (database). Revision 9.

A function to create and load a table in SQLite from a Microsoft Jet table, using DAO. Also recreates indexes. Fetches and loads records in blocks with a default size of 1000 rows.

A higher level struct module (Python)

2006-10-26T14:47:57-07:00

Python recipe 498149 by Brian McErlean (database). Revision 4.

This recipe provides a higher level wrapper around the struct module. It provides a more convenient syntax for defining and using structs, and adds additional features such as: - Allows embedding structures within other structures - Allows defining arrays of items (or other structures) - Class based syntax, allowing access and updates by field name, not position - Extension of structures by inheritance

MySQL and accessing database results by field (Python)

2003-01-07T21:37:48-08:00

Python recipe 171463 by Jaroslaw Zabiello (database). Revision 4.

Accessing database results by field name rather than field number is easy. I prefer standard, stable library like MySQLdb rather than private solutions. Look at the following code.

Microsoft Access Database Connectivity (DSN-Less) (PHP)

2006-08-29T03:08:29-07:00

PHP recipe 163447 by Erick Guanlao (database). Revision 4.

An example of connecting to a Microsoft Access database, executing an SQL query, and displaying the results in HTML using PHP and COM (Windows only).

NOTE: This works with PHP 4.x. I have been unsuccessful with getting this to work with PHP 5.1.6 and Apache 2.0.59 on Windowx XP Professional.

Simple Knowlegde Database (Python)

2012-01-05T08:58:40-08:00

Python recipe 577975 by Thomas Lehmann (antagonisms, database, knowledge, opposites, relationships). Revision 2.

What's the idea?

The idea is to be able to ask more successful questions than data provided.
To have a kind of simple database

How is this done?

A releationship is always though as a from of older/younger or bigger/smaller. You have to define those opposite meanings by calling 'defineAntagonism'
After this you can define a relationship by calling 'defineRelationship' using one of the opposite meanings and two ... I say names (can be persons or objects)
When you define that somebody/someting is bigger than somebody/something else then you implicitly provide two information (bigger <-> smaller)
Also when defining - more commonly explained - that A > B and B > C then also A > C and C < A.
That's the main logic implemented by this python code.

Special notes

We have to avoid inconsistent data; when it is defined that A > B then you are not allowed to say that B > A.
We have to sort relations because they build up - I name it like this - a dependency chain. When a query checks for A > C but A > B and B > C is defined only we need an order for searching.

Converting DBI results to a list of dictionaries (Python)

2007-09-09T17:59:17-07:00

Python recipe 528939 by Andrew Smith (database).

One of the most frequently asked questions is how to efficiently get a list of Python dictionaries from a database query rather than the difficult-to-use list of tuples.

Extraction and manipulation class for Microsoft Access (Python)

2008-03-01T07:15:52-08:00

Python recipe 528868 by Grant Paton-Simpson (database). Revision 2.

Class for extracting and manipulating data from Microsoft Access - identifying tables, getting recordsets, iterating through the results, counting rows, getting field names, getting index information, deleting indexes, and adding and deleting relationships.

Run SQLite-connections in isolated threads (Python)

2006-06-15T18:29:10-07:00

Python recipe 496799 by Wim Schut (database). Revision 3.

This recipe lets databaseconnections live in their own thread and queues are used to communicate with them.

write sql via python (Python)

2005-05-26T19:43:49-07:00

Python recipe 415345 by Sakesun Roykiattisak (database).

construct sql query in python rather than performing tons of string manipulation. Take a look at docstring of StandardSelect and OracleSelect for a hint on how the module should be used.

A Simple ZODB viewer in wxPython (Python)

2005-04-08T18:52:41-07:00

Python recipe 409012 by Chris Cioffi (database). Revision 2.

This is a very simple script to allow someone to examine a ZODB database. Start the script and you can see what is in your ZODB file as well as the overall structure.

Put Peewee ORM data to PDF with xtopdf (Python)

2016-09-29T18:04:57-07:00

Python recipe 580704 by Vasudev Ram (conversion, converter, database, formats, orm, pdf, pdf_generation, peewee, python, python2, sqlite, xtopdf).

This recipe shows how some basics of how to fetch data from database tables managed by the Peewee ORM (a lightweight expressive ORM for Python) and write that data, formatted, to a PDF file. The recipe uses Python, the Peewee ORM and the xtopdf toolkit for PDF creation.

DBF reader and writer -- selective fields and nullreplace (Python)

2016-09-18T20:39:20-07:00

Python recipe 580696 by Tomas Nordin (database).

This fork assumes a desire for limited selection of field names. With huge files this might be necessary on some machines.

Also, assuming that the meaning of null in a dbf file means zero might be a mistake, so the fork adds an argument nullreplace as way to choose what to replace null with. Null is sometimes used to mean missing value. This change is decoupled from the selective names feature.

Printing list of ODBC data sources (Python)

2013-12-10T11:07:37-08:00

Python recipe 578782 by Michal Niklas (database, odbc, sql).

This simple code shows ODBC data sources. It uses odbc module.

A Phone Book GUI Built in wxPython Connected To Database Using Data Grid View (Python)

2013-09-29T19:25:23-07:00

Python recipe 578676 by toufic zaarour (database, datagridview, graphical, gui, interface).

this GUI as simple as it is explains some basic but important graphical database interfacing; "Add", "Edit","Delete","Search" and few others along with a data grid view. in order to work create an sqlite3 database as follows:

data table : Phone, column 1 : ID, column 2 : name, column 3 : surname, column 4 : telephone.

save the sqlite3 file as file.db in a folder called Data and place it in the same directory as your python script.

if you want to create the sqlite3 database graphically use my previous post : http://code.activestate.com/recipes/578665-a-wxpython-gui-to-create-sqlite3-databases/

Also there is more: I did not use auto-number for 'id' because I also wanted to include in the code a renumbering script.

I am pleased to receive all the suggestions and improvements on this site or to my e-mail directly if this is convenient to you.

note: if you don't like the database table name, and columns name create your own but make sure to change them in the code as well! in the end life is great! remember that!

Inner Join (Python)

2011-11-01T20:10:34-07:00

Python recipe 577937 by Raymond Hettinger (database, join, sql). Revision 2.

Implemented an SQL style INNER JOIN for two lists of tuples to be joined on a common field.

Simple curses based MySQL 'top' (Python)

2011-11-02T20:28:48-07:00

Python recipe 577936 by Mike 'Fuzzy' Partin (curses, database, mysql, time). Revision 3.

This is little more than a modification of my previous recipe, however, I found it useful so I thought I would post it in the hopes that someone else would as well. There is color (BOLD white really) designation for 'Query' states vs others like 'Sleep'.

Simple numeric database (Python)

2011-05-16T08:41:46-07:00

Python recipe 577697 by Mike Sweeney (array, database, numeric). Revision 2.

A simple in-memory numeric database for Python. Arrays are used to minimise memory consumption.