Traversing TCGA: Storing Data in PostgreSQL

This is a post in the ongoing series, Traversing TCGA.

So far we’ve been able to download, extract, organize and analyze The Cancer Genome Atlas data in Python. However, storing data as an internal variable in Python isn’t ideal for all scenarios. Storing data over the long term in a database, such as PostgreSQL, is a better solution. PostgreSQL and other database software allows for stable, structured data storage which can be accessed from anywhere and offers a host of powerful tools for analysis.

PostgreSQL (or just Postgres) installers are available here. During installation, port (default=5432) and password are set.

After installation, the database can be accessed from the psql program. The prompt looks like this:

psql

Using the \dt command lists all tables in the database.

dt

SQL queries like CREATE TABLE, and DROP TABLE are used to make and delete tables.

Postgres databases can also be accessed in python using the psycopg2 library as follows:

psycoLogin

The default dbname and username at install is “postgres”. The host “localhost” accesses databases on the same computer, but could be replaced by an IP address to access a database remotely. The getData function refers to code I wrote earlier which extracts patient data.

The following command can be used to create a table:

createTable

This creates a table named “cancer_data” with a column called “patient_id” of type text and a column called “gender” of type text. The table can then be populated with the following:

popTable

The table is now populated with entries from the patient data extracted from XML data. These can be displayed with the following commands:

prinTable

Which results in the data to display:

DatPrint.PNG

Showing we have successfully populated the table.

Full code is on github.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s