db.py

What is it
Databases Supported
Features
Quickstart - Installation - Demo
How To
Contributing
TODO

What is it?

db.py is an easier way to interact with your databases. It makes it easier to explore tables, columns, views, etc. It puts the emphasis on user interaction, information display, and providing easy to use helper functions.

db.py uses pandas to manage data, so if you're already using pandas, db.py should feel pretty natural. It's also fully compatible with the IPython Notebook, so not only is db.py extremely functional, it's also pretty.

Blog Post

Databases Supported

PostgreSQL
MySQL
SQLite
Redshift
MS SQL Server
Oracle

`db.py` let's you...

Execute queries

>>> db.query_from_file("myscript.sql")
       _id                    datetime           user_id  n
0  1290000  10/Jun/2014:18:21:27 +0000  0000015b37cd0964  1
1  9120009  23/Jun/2014:02:11:21 +0000  00006e01a6419822  1
2  1683874  23/Jun/2014:02:11:48 +0000  00006e01a6419822  2
3  2562153  23/Jun/2014:02:12:57 +0000  00006e01a6419822  3
4   393019  14/Jun/2014:16:05:18 +0000  000099d569e3a216  1
5  3542568  14/Jun/2014:16:06:02 +0000  000099d569e3a216  2

Fully compatible with predictive type

>>> db.tables.
db.tables.Album          db.tables.Customer       db.tables.Genre          db.tables.InvoiceLine    db.tables.Playlist       db.tables.Track
db.tables.Artist         db.tables.Employee       db.tables.Invoice        db.tables.MediaType      db.tables.PlaylistTrack  db.tables.tables

Friendly displays

>>> db.tables.Track
+-------------------------------------------------------------+
|                            Album                            |
+----------+---------------+-----------------+----------------+
| Column   | Type          | Foreign Keys    | Reference Keys |
+----------+---------------+-----------------+----------------+
| AlbumId  | INTEGER       |                 | Track.AlbumId  |
| Title    | NVARCHAR(160) |                 |                |
| ArtistId | INTEGER       | Artist.ArtistId |                |
+----------+---------------+-----------------+----------------+

Directly integrated with pandas

>>> db.tables.Track.head()
   TrackId                                     Name  AlbumId  MediaTypeId  \
0        1  For Those About To Rock (We Salute You)        1            1
1        2                        Balls to the Wall        2            2
2        3                          Fast As a Shark        3            2
3        4                        Restless and Wild        3            2
4        5                     Princess of the Dawn        3            2
5        6                    Put The Finger On You        1            1

   GenreId                                           Composer  Milliseconds  \
0        1          Angus Young, Malcolm Young, Brian Johnson        343719
1        1                                               None        342562
2        1  F. Baltes, S. Kaufman, U. Dirkscneider & W. Ho...        230619
3        1  F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. D...        252051
4        1                         Deaffy & R.A. Smith-Diesel        375418
5        1          Angus Young, Malcolm Young, Brian Johnson        205662

      Bytes  UnitPrice
0  11170334       0.99
1   5510424       0.99
2   3990994       0.99
3   4331779       0.99
4   6290521       0.99
5   6713451       0.99

Create queries using Handlebars style templates

q = """
SELECT
    '{{ name }}' as table_name, sum(1) as cnt
FROM
    {{ name }}
GROUP BY
    table_name
"""
data = [
  {"name": "Album"},
  {"name": "Artist"},
  {"name": "Track"}
]
db.query(q, data=data)
  table_name   cnt
0      Album   347
1     Artist   275
2      Track  3503

Search your schema

>>> db.find_column("*Id*")
+---------------+---------------+---------+
| Table         |  Column Name  | Type    |
+---------------+---------------+---------+
| Album         |    AlbumId    | INTEGER |
| Album         |    ArtistId   | INTEGER |
| Artist        |    ArtistId   | INTEGER |
| Customer      |  SupportRepId | INTEGER |
| Customer      |   CustomerId  | INTEGER |
| Employee      |   EmployeeId  | INTEGER |
| Genre         |    GenreId    | INTEGER |
| Invoice       |   InvoiceId   | INTEGER |
| Invoice       |   CustomerId  | INTEGER |
| InvoiceLine   |   InvoiceId   | INTEGER |
| InvoiceLine   |    TrackId    | INTEGER |
| InvoiceLine   | InvoiceLineId | INTEGER |
| MediaType     |  MediaTypeId  | INTEGER |
| Playlist      |   PlaylistId  | INTEGER |
| PlaylistTrack |    TrackId    | INTEGER |
| PlaylistTrack |   PlaylistId  | INTEGER |
| Track         |  MediaTypeId  | INTEGER |
| Track         |    TrackId    | INTEGER |
| Track         |    AlbumId    | INTEGER |
| Track         |    GenreId    | INTEGER |
+---------------+---------------+---------+

IPython Notebook friendly

Quickstart

Installation

db.py is on PyPi.

$ pip install db.py

The database libraries being used under the hood are optional dependencies (if you use mysql, you probably don't care about installing psycopg2). Based on the databases you're using, you'll need one (or many) of the following:

PostgreSQL: psycopg2. Windows
Redshift: psycopg2. Redshift is a flavor of PostgreSQL.
MySQL: MySQLdb
SQLite: sqlite3. Should be installed already.
MS SQL: TBD. Suggestions welcome! https://github.com/yhat/db.py/issues

Demo

>>> from db import DemoDB # or connect to your own using DB. see below
>>> db = DemoDB() # comes from: http://chinookdatabase.codeplex.com/
>>> db.tables
+---------------+----------------------------------------------------------------------------------+
| Table         | Columns                                                                          |
+---------------+----------------------------------------------------------------------------------+
| Album         | AlbumId, Title, ArtistId                                                         |
| Artist        | ArtistId, Name                                                                   |
| Customer      | CustomerId, FirstName, LastName, Company, Address, City, State, Country, PostalC |
|               | ode, Phone, Fax, Email, SupportRepId                                             |
| Employee      | EmployeeId, LastName, FirstName, Title, ReportsTo, BirthDate, HireDate, Address, |
|               |  City, State, Country, PostalCode, Phone, Fax, Email                             |
| Genre         | GenreId, Name                                                                    |
| Invoice       | InvoiceId, CustomerId, InvoiceDate, BillingAddress, BillingCity, BillingState, B |
|               | illingCountry, BillingPostalCode, Total                                          |
| InvoiceLine   | InvoiceLineId, InvoiceId, TrackId, UnitPrice, Quantity                           |
| MediaType     | MediaTypeId, Name                                                                |
| Playlist      | PlaylistId, Name                                                                 |
| PlaylistTrack | PlaylistId, TrackId                                                              |
| Track         | TrackId, Name, AlbumId, MediaTypeId, GenreId, Composer, Milliseconds, Bytes, Uni |
|               | tPrice                                                                           |
+---------------+----------------------------------------------------------------------------------+
>>> db.tables.Customer
+------------------------------------------------------------------------+
|                                Customer                                |
+--------------+--------------+---------------------+--------------------+
| Column       | Type         | Foreign Keys        | Reference Keys     |
+--------------+--------------+---------------------+--------------------+
| CustomerId   | INTEGER      |                     | Invoice.CustomerId |
| FirstName    | NVARCHAR(40) |                     |                    |
| LastName     | NVARCHAR(20) |                     |                    |
| Company      | NVARCHAR(80) |                     |                    |
| Address      | NVARCHAR(70) |                     |                    |
| City         | NVARCHAR(40) |                     |                    |
| State        | NVARCHAR(40) |                     |                    |
| Country      | NVARCHAR(40) |                     |                    |
| PostalCode   | NVARCHAR(10) |                     |                    |
| Phone        | NVARCHAR(24) |                     |                    |
| Fax          | NVARCHAR(24) |                     |                    |
| Email        | NVARCHAR(60) |                     |                    |
| SupportRepId | INTEGER      | Employee.EmployeeId |                    |
+--------------+--------------+---------------------+--------------------+
>>> db.tables.Customer.sample()
   CustomerId  FirstName    LastName  \
0           4      Bjørn      Hansen
1          26    Richard  Cunningham
2           1       Luís   Gonçalves
3          21      Kathy       Chase
4           6     Helena        Holý
5          14       Mark     Philips
6          49  Stanisław      Wójcik
7          19        Tim       Goyer
8          45   Ladislav      Kovács
9           8       Daan     Peeters

                                            Company  \
0                                              None
1                                              None
2  Embraer - Empresa Brasileira de Aeronáutica S.A.
3                                              None
4                                              None
5                                             Telus
6                                              None
7                                        Apple Inc.
8                                              None
9                                              None

                           Address                 City State         Country  \
0                 Ullevålsveien 14                 Oslo  None          Norway
1              2211 W Berry Street           Fort Worth    TX             USA
2  Av. Brigadeiro Faria Lima, 2170  São José dos Campos    SP          Brazil
3                 801 W 4th Street                 Reno    NV             USA
4                    Rilská 3174/6               Prague  None  Czech Republic
5                   8210 111 ST NW             Edmonton    AB          Canada
6                     Ordynacka 10               Warsaw  None          Poland
7                  1 Infinite Loop            Cupertino    CA             USA
8                Erzsébet krt. 58.             Budapest  None         Hungary
9                  Grétrystraat 63             Brussels  None         Belgium

  PostalCode               Phone                 Fax  \
0       0171     +47 22 44 22 22                None
1      76110   +1 (817) 924-7272                None
2  12227-000  +55 (12) 3923-5555  +55 (12) 3923-5566
3      89503   +1 (775) 223-7665                None
4      14300    +420 2 4177 0449                None
5    T6G 2C7   +1 (780) 434-4554   +1 (780) 434-5565
6     00-358    +48 22 828 37 39                None
7      95014   +1 (408) 996-1010   +1 (408) 996-1011
8     H-1073                None                None
9       1000    +32 02 219 03 03                None

                      Email  SupportRepId
0     bjorn.hansen@yahoo.no             4
1  ricunningham@hotmail.com             4
2      luisg@embraer.com.br             3
3       kachase@hotmail.com             5
4           hholy@gmail.com             5
5        mphilips12@shaw.ca             5
6    stanisław.wójcik@wp.pl             4
7          tgoyer@apple.com             3
8  ladislav_kovacs@apple.hu             3
9     daan_peeters@apple.be             4
>>> db.find_column("*Name*")
+-----------+-------------+---------------+
| Table     | Column Name | Type          |
+-----------+-------------+---------------+
| Artist    |     Name    | NVARCHAR(120) |
| Customer  |  FirstName  | NVARCHAR(40)  |
| Customer  |   LastName  | NVARCHAR(20)  |
| Employee  |  FirstName  | NVARCHAR(20)  |
| Employee  |   LastName  | NVARCHAR(20)  |
| Genre     |     Name    | NVARCHAR(120) |
| MediaType |     Name    | NVARCHAR(120) |
| Playlist  |     Name    | NVARCHAR(120) |
| Track     |     Name    | NVARCHAR(200) |
+-----------+-------------+---------------+
>>> db.find_table("A*")
+--------+--------------------------+
| Table  | Columns                  |
+--------+--------------------------+
| Album  | AlbumId, Title, ArtistId |
| Artist | ArtistId, Name           |
+--------+--------------------------+
>>> db.query("select * from Artist limit 10;")
   ArtistId                  Name
0         1                 AC/DC
1         2                Accept
2         3             Aerosmith
3         4     Alanis Morissette
4         5       Alice In Chains
5         6  Antônio Carlos Jobim
6         7          Apocalyptica
7         8            Audioslave
8         9              BackBeat
9        10          Billy Cobham

How To

Connecting to a Database

The `DB()` object

Arguments

username: your username
password: your password
hostname: hostname of the database (i.e. localhost, dw.mardukas.com, ec2-54-191-289-254.us-west-2.compute.amazonaws.com)
port: port the database is running on (i.e. 5432)
dbname: name of the database (i.e. hanksdb)
filename: path to sqlite database (i.e. baseball-archive-2012.sqlite, employees.db)
dbtype: type of database you're connecting to (postgres, mysql, sqlite, redshift)
profile: name of the profile you want to use to connect. using this negates the need to specify any other arguments
exclude_system_tables: whether or not to load schema information for internal tables. for example, postgres has a bunch of tables prefixed with pg_ that you probably don't actually care about. on the other had if you're administrating a database, you might want to query these tables
limit: default number of records to return in a query. This is used by the DB.query method. You can override it by adding limit={X} to the query method, or by passing an argument to DB(). None indicates that there will be no limit (That's right, you'll be limitless. Bradley Cooper style.)

>>> from db import DB
>>> db = DB(username="greg", password="secret", hostname="localhost",
            dbtype="postgres")

Saving a profile

>>> from db import DB
>>> db = DB(username="greg", password="secret", hostname="localhost",
            dbtype="postgres")
>>> db.save_credentials() # this will save to "default"
>>> db.save_credentials(profile="local_pg")

Connecting from a profile

>>> from db import DB
>>> db = DB() # this loads "default" profile
>>> db = DB(profile="local_pg")

List your profiles

>>> from db import list_profiles
>>> list_profiles()
{'demo': {u'dbname': None,
  u'dbtype': u'sqlite',
  u'filename': u'/Users/glamp/repos/yhat/opensource/db.py/db/data/chinook.sqlite',
  u'hostname': u'localhost',
  u'password': None,
  u'port': 5432,
  u'username': None},
 'muppets': {u'dbname': u'muppetdb',
  u'dbtype': u'postgres',
  u'filename': None,
  u'hostname': u'muppets.yhathq.com',
  u'password': None,
  u'port': 5432,
  u'username': u'kermit'}}

Remove a profile

>>> remove_profile('demo')

Executing Queries

From a string

>>> df1 = db.query("select * from Artist;")
>>> df2 = db.query("select * from Album;")

From a file

>>> db.query_from_file("myscript.sql")
>>> df = db.query_from_file("myscript.sql")

Searching for Tables and Columns

Tables

>>> db.find_table("A*")
+--------+--------------------------+
| Table  | Columns                  |
+--------+--------------------------+
| Album  | AlbumId, Title, ArtistId |
| Artist | ArtistId, Name           |
+--------+--------------------------+
>>> results = db.find_table("tmp*") # returns all tables prefixed w/ tmp
>>> results = db.find_table("prod_*") # returns all tables prefixed w/ prod_
>>> results = db.find_table("*Invoice*") # returns all tables containing trans
>>> results = db.find_table("*") # returns everything

Columns

>>> db.find_column("Name") # returns all columns named "Name"
+-----------+-------------+---------------+
| Table     | Column Name | Type          |
+-----------+-------------+---------------+
| Artist    |     Name    | NVARCHAR(120) |
| Genre     |     Name    | NVARCHAR(120) |
| MediaType |     Name    | NVARCHAR(120) |
| Playlist  |     Name    | NVARCHAR(120) |
| Track     |     Name    | NVARCHAR(200) |
+-----------+-------------+---------------+
>>> db.find_column("*Id") # returns all columns ending w/ Id
+---------------+---------------+---------+
| Table         |  Column Name  | Type    |
+---------------+---------------+---------+
| Album         |    AlbumId    | INTEGER |
| Album         |    ArtistId   | INTEGER |
| Artist        |    ArtistId   | INTEGER |
| Customer      |  SupportRepId | INTEGER |
| Customer      |   CustomerId  | INTEGER |
| Employee      |   EmployeeId  | INTEGER |
| Genre         |    GenreId    | INTEGER |
| Invoice       |   InvoiceId   | INTEGER |
| Invoice       |   CustomerId  | INTEGER |
| InvoiceLine   |   InvoiceId   | INTEGER |
| InvoiceLine   |    TrackId    | INTEGER |
| InvoiceLine   | InvoiceLineId | INTEGER |
| MediaType     |  MediaTypeId  | INTEGER |
| Playlist      |   PlaylistId  | INTEGER |
| PlaylistTrack |    TrackId    | INTEGER |
| PlaylistTrack |   PlaylistId  | INTEGER |
| Track         |  MediaTypeId  | INTEGER |
| Track         |    TrackId    | INTEGER |
| Track         |    AlbumId    | INTEGER |
| Track         |    GenreId    | INTEGER |
+---------------+---------------+---------+
>>> db.find_column("*Address*") # returns all columns containing Address
+----------+----------------+--------------+
| Table    |  Column Name   | Type         |
+----------+----------------+--------------+
| Customer |    Address     | NVARCHAR(70) |
| Employee |    Address     | NVARCHAR(70) |
| Invoice  | BillingAddress | NVARCHAR(70) |
+----------+----------------+--------------+
# returns all columns containing Address that are varchars
>>> db.find_column("*Address*", data_type="NVARCHAR(70)")
# returns all columns have an "e" and are NVARCHAR/INTEGERS
>>> db.find_column("*e*", data_type=["NVARCHAR(70)", "INTEGER"])

Tests

To run individual tests:

$ python -m unittest test_module.TestClass.test_method

To run all the tests:

$ python -m unittest discover <path_to_tests_folder> -v

Contributing

See either the TODO below or Adding a Database.

TODO

Does not work with MS SQL Server

I can't seem to get the SQL Server driver working on OSX. I've got pyodbc working (which seems to be the most accepted way to query SQL Server from Python).

Any suggestions?
bug enhancement help wanted

opened by glamp 7
Query using TRUNCATE fails
Hi,

Thanks for the very cool implementation.

When connected to an Amazon Redshift database, my attempt to TRUNCATE a table

from db import DB db = DB() db.query("TRUNCATE table_name;")

obtained an error

TypeError: 'NoneType' object is not iterable

Does db.py support the TRUNCATE command?
opened by ppope 5
feature: add 'count' property/method or __len__ to Table to get the number of rows

Hi,

I would like to get the number of rows in a specific table. Is it possible to add a count or rows method/property to the Table class? You can also add the __len__ special method.

Moreover, this number of rows will be added to the TableSet repr.

Cheers, Damien

opened by garaud 4
Creating DemoDB object in setUp under tests.py

Hello,

First of all this is an amazing project. Will try to contribute as I want to contribute to database related projects in Python :)

I wanted to know why in tests.py did you decalre db = DemoDB() as a global object. You could have done the following in setUp:

def setUp(self): self.db = DemoDB()

setUp() is called to prepare test fixture and is used to provide initialization for the tests. Since the test.py code is still not that big, you can replace all db variables by self.db. Let me know what you think. I can do this too if you think this is a viable contribution. Also adding:

def tearDown(self): pass

since that is for cleaning up after all tests are done.

opened by ueg1990 4

db.py doesn't support drop/create/insert statements ?

example : is ok :

from db import DB
db=DB(dbtype="sqlite", filename=":memory:")
db.query("select  sqlite_version() as sqlite_version  ;")

is not ok

db.query("DROP TABLE IF EXISTS writer;")

not ok ?

db.query("CREATE TABLE writer (first_name, last_name, year_of_death);")

db.query("INSERT INTO writer VALUES ('William', 'Shakespeare', 1616);")

opened by stonebig 4

Add DemoDB() initialization in setUp()
I added DemoDB() initialization in setUp() for tests and the tests were passing (except for one). But there are two issues/observations:

setUp() method is run before every test method. So Refresh schema and Indexing schema happens at every test method. This could be a good thing. But if you think it is inefficient, there may be a way such that the DemoDB initialization happens only once before all the tests are run. If you want that, I can modify it.

The test test_table_keys_per_column fails and I get the following error:

ERROR: test_table_keys_per_column (tests.PandaSQLTest)

Traceback (most recent call last): File "/Users/ue/Desktop/Projects/db.py/db/tests/tests.py", line 73, in test_table_keys_per_column short_db = DemoDB(keys_per_column=1) TypeError: DemoDB() takes no arguments (1 given)

Ran 14 tests in 0.127s

FAILED (errors=1)

Did you plan to add default parameter to DemoDB?
opened by ueg1990 3
"Add a database" checklist

Since we're getting a few requests of the form "Can you support database X?", it'd be really helpful to have a developer checklist of features to implement when adding support for a new database.
enhancement

opened by ericchiang 3
Suggestion: Include system tables

Rather than type in all the tables I'd like to exclude from loading in DB(), would it be possible to just select the few tables I'd like to load the schema for? I feel like this would be a ton faster for me.

opened by marctollin 3
Add capability to save and load cache of metadata
This isn't quite ready, I'll let you know when it is. I'm just creating the pull request to discuss any potential issues with this.

The reasoning is that it can take quite some time with large databases to refresh the schema. This provides the option of saving the metadata (stores with the credentials, so tied to a profile). Then, when you load from profile, you can tell it to use the cache and it will be used for the first load. You can still manually refresh the schema if desired.

db.save_metadata('my_db') db = DB(profile='my_db', cache=True)

The cache is created by building dict representations of each of the objects. This is stored in json and used to instantiate the objects if the cache is used.
opened by rothnic 2
Can db.py read a in-memory SQLite db?
Hi,

Suppose, for demo or test purposes, I have a in-memory SQLite database such as:

https://www.sqlite.org/inmemorydb.html or

engine = create_engine('sqlite://') if I use SQLAlchemy

Can I read it from db.DB? I test:

from db import DB db = DB(filename="sqlite://", dbtype='sqlite')

without success. Is there a plan to support it?

Thanks ! Cheers, Damien G.
opened by garaud 2
Fixing query_from_file call method in db/db.py

The argument "data" in method definition is defined as required, but the docstring says it is optinonal. Refactored file read call at the end of method. The best pratice is to close file just after use.

opened by maurobaraldi 2
docs: Fix a few typos
There are small typos in:

README.md

README.rst

db/db.py

db/s3.py

docs/adding-a-db-checklist.md

Fixes:

Should read distribution rather than distrobution.

Should read credentials rather than crentials.

Should read statement rather than statment.

Should read postgres rather than portgres.

Should read necessary rather than neccessary.

Semi-automated pull request generated by https://github.com/timgates42/meticulous/blob/master/docs/NOTE.md
opened by timgates42 0
Documentation enhancement

Reading the documentation, it is not clear what the specific purpose of db.py is. Is db.py an ORM? If not, why and in which use cases would I use db.py instead of an ORM like SQLAlchemy?

opened by thorstenkampe 0
print "var", var

one error in file init.py, in line 69, print "var", var, """is it necessary?""", i have changed to print ("var", var). I am using the version 3.5.2 of python

opened by AmiltonRN 1