.. DO NOT EDIT. .. THIS FILE WAS AUTOMATICALLY GENERATED BY SPHINX-GALLERY. .. TO MAKE CHANGES, EDIT THE SOURCE PYTHON FILE: .. "auto_examples/plot_postgres.py" .. LINE NUMBERS ARE GIVEN BELOW. .. only:: html .. note:: :class: sphx-glr-download-link-note Click :ref:`here ` to download the full example code .. rst-class:: sphx-glr-example-title .. _sphx_glr_auto_examples_plot_postgres.py: ========================================================= CRUD operations on Postgres tables using Neurobooth-terra ========================================================= This example demonstrates how to perform `CRUD`_ operations on postgres tables with neurobooth-terra. .. GENERATED FROM PYTHON SOURCE LINES 9-12 .. code-block:: default # Authors: Mainak Jas .. GENERATED FROM PYTHON SOURCE LINES 13-16 .. contents:: Table of Contents :local: .. GENERATED FROM PYTHON SOURCE LINES 18-33 To run this example, it is necessary to set up a local database called 'neurobooth' with user 'neuroboother'. Make sure the Postgres server is running:: $ pg_ctl -D /usr/local/var/postgres start Then create a database 'neurobooth':: $ createdb neurobooth And a user 'neuroboother':: $ createuser -P -s -e neuroboother Now, let us open python or ipython and import the necessary functions. .. GENERATED FROM PYTHON SOURCE LINES 33-39 .. code-block:: default from neurobooth_terra import list_tables, create_table, drop_table, Table import psycopg2 import pandas as pd .. GENERATED FROM PYTHON SOURCE LINES 40-41 Then, we will create a connection using ``psycopg2``. .. GENERATED FROM PYTHON SOURCE LINES 41-46 .. code-block:: default connect_str = ("dbname='neurobooth' user='neuroboother' host='localhost' " "password='neuroboothrocks'") conn = psycopg2.connect(connect_str) .. GENERATED FROM PYTHON SOURCE LINES 47-50 We will drop tables if they already exist this is just for convenience so we can re-run this script even when changing some columns .. GENERATED FROM PYTHON SOURCE LINES 50-54 .. code-block:: default drop_table('subject', conn) drop_table('contact', conn) drop_table('consent', conn) .. GENERATED FROM PYTHON SOURCE LINES 55-58 Create ------ Now we define the Table .. GENERATED FROM PYTHON SOURCE LINES 58-65 .. code-block:: default table_id = 'subject' column_names = ['subject_id', 'first_name_birth', 'last_name_birth'] dtypes = ['VARCHAR (255)', 'VARCHAR (255)', 'VARCHAR (255)'] table_subject = create_table(table_id, conn, column_names=column_names, dtypes=dtypes) .. GENERATED FROM PYTHON SOURCE LINES 66-67 and insert some data and retrieve the table as a dataframe .. GENERATED FROM PYTHON SOURCE LINES 67-71 .. code-block:: default table_subject.insert_rows([('x5dc', 'mainak', 'jas'), ('y5d3', 'anoopum', 'gupta')], cols=column_names) .. GENERATED FROM PYTHON SOURCE LINES 72-74 We can create another table and relate it to the other table using a foreign key .. GENERATED FROM PYTHON SOURCE LINES 74-83 .. code-block:: default table_id = 'contact' column_names = ['subject_id', 'email'] dtypes = ['VARCHAR (255)', 'VARCHAR (255)'] table_contact = create_table(table_id, conn, column_names=column_names, dtypes=dtypes, foreign_key=dict(subject_id='subject')) table_contact.insert_rows([('x5dc',), ('y5d3',)], cols=['subject_id']) .. GENERATED FROM PYTHON SOURCE LINES 84-87 Read ---- We can get a list of tables in the database by doing .. GENERATED FROM PYTHON SOURCE LINES 87-89 .. code-block:: default list_tables(conn) .. rst-class:: sphx-glr-script-out Out: .. code-block:: none ['device', 'sensor', 'subject', 'contact', 'test_consent', 'log_sensor_file', 'log_file', 'sensor_file_log', 'file', 'test', 'study', 'register', 'clinical', 'demograph', 'human_obs_log', 'observer', 'human_obs_data', 'instruction', 'tech_obs_data', 'stimulus', 'tech_obs_log', 'collection'] .. GENERATED FROM PYTHON SOURCE LINES 90-92 From the list of these table, we can select one to inspect by creating a :class:`~neurobooth_terra.Table` object. .. GENERATED FROM PYTHON SOURCE LINES 92-96 .. code-block:: default table_id = 'subject' table_subject = Table(table_id, conn) print(table_subject) .. rst-class:: sphx-glr-script-out Out: .. code-block:: none Table "subject" (subject_id, first_name_birth, last_name_birth) .. GENERATED FROM PYTHON SOURCE LINES 97-99 With this Table object, we can create a query that returns a dataframe .. GENERATED FROM PYTHON SOURCE LINES 99-102 .. code-block:: default df_subject = table_subject.query() print(df_subject) .. rst-class:: sphx-glr-script-out Out: .. code-block:: none first_name_birth last_name_birth subject_id x5dc mainak jas y5d3 anoopum gupta .. GENERATED FROM PYTHON SOURCE LINES 103-106 Update ------ We can make changes such as adding a new column .. GENERATED FROM PYTHON SOURCE LINES 106-109 .. code-block:: default table_subject.add_column('dob', 'VARCHAR (255)') print(table_subject.query()) .. rst-class:: sphx-glr-script-out Out: .. code-block:: none first_name_birth last_name_birth dob subject_id x5dc mainak jas None y5d3 anoopum gupta None .. GENERATED FROM PYTHON SOURCE LINES 110-111 To update a row in the table we can do .. GENERATED FROM PYTHON SOURCE LINES 111-116 .. code-block:: default table_subject.update_row('y5d3', cols=['first_name_birth', 'last_name_birth'], vals=('anupum', 'gupta')) print(table_subject.query()) .. rst-class:: sphx-glr-script-out Out: .. code-block:: none first_name_birth last_name_birth dob subject_id x5dc mainak jas None y5d3 anupum gupta None .. GENERATED FROM PYTHON SOURCE LINES 117-120 Delete ------ We can also delete rows in our table .. GENERATED FROM PYTHON SOURCE LINES 120-123 .. code-block:: default table_subject.delete_row(condition="subject_id = 'yd53'") print(table_subject.query()) .. rst-class:: sphx-glr-script-out Out: .. code-block:: none first_name_birth last_name_birth dob subject_id x5dc mainak jas None y5d3 anupum gupta None .. GENERATED FROM PYTHON SOURCE LINES 124-125 Or drop columns .. GENERATED FROM PYTHON SOURCE LINES 125-128 .. code-block:: default table_contact.drop_column('email') print(table_contact.query()) .. rst-class:: sphx-glr-script-out Out: .. code-block:: none Empty DataFrame Columns: [] Index: [x5dc, y5d3] .. GENERATED FROM PYTHON SOURCE LINES 129-130 To delete an entire table, we can do .. GENERATED FROM PYTHON SOURCE LINES 130-132 .. code-block:: default drop_table('subject', conn) list_tables(conn) .. rst-class:: sphx-glr-script-out Out: .. code-block:: none ['device', 'sensor', 'contact', 'test_consent', 'log_sensor_file', 'log_file', 'sensor_file_log', 'file', 'test', 'study', 'register', 'clinical', 'demograph', 'human_obs_log', 'observer', 'human_obs_data', 'instruction', 'tech_obs_data', 'stimulus', 'tech_obs_log', 'collection'] .. GENERATED FROM PYTHON SOURCE LINES 133-134 Don't forget to close the connection once done! .. GENERATED FROM PYTHON SOURCE LINES 134-138 .. code-block:: default table_subject.close() table_contact.close() conn.close() .. GENERATED FROM PYTHON SOURCE LINES 139-140 .. _CRUD: https://en.wikipedia.org/wiki/Create,_read,_update_and_delete .. rst-class:: sphx-glr-timing **Total running time of the script:** ( 0 minutes 3.451 seconds) .. _sphx_glr_download_auto_examples_plot_postgres.py: .. only :: html .. container:: sphx-glr-footer :class: sphx-glr-footer-example .. container:: sphx-glr-download sphx-glr-download-python :download:`Download Python source code: plot_postgres.py ` .. container:: sphx-glr-download sphx-glr-download-jupyter :download:`Download Jupyter notebook: plot_postgres.ipynb ` .. only:: html .. rst-class:: sphx-glr-signature `Gallery generated by Sphinx-Gallery `_