PostgreSQL and Jupyter setup - ETH Z

PostgreSQL and Jupyter setup

At the core of any information system lies a database system. There are numerous applications available to choose from, however, for the exercises of this course we will use only PostgreSQL. The reason for this is that PostgreSQL is very close to the SQL standard. Furthermore, we will also use Jupyter, a Python-based browser application that allows easy creation, execution and reuse of SQL queries through user-generated notebooks. Follow the instructions below to install PostgreSQL, Python and Jupyter on your computer.

1 PostgreSQL

Navigate to the official PostgreSQL website (), where you can find instructions on how to download and install the database system. Find below further information for the operating system of your preference.

Note: The latest version of PostgreSQL is 12.2. If possible, please try to install this version, so that it can be easier to assist you with technical issues that may arise.

1.1 Linux

If you are using Linux, you should probably use your distribution's package manager to install the database system, e.g. apt-get on Ubuntu. Please, follow the instructions on the official PostgreSQL website () to install the latest version for your operating system. Ubuntu has normally a very old version, so you need to use this link for Ubuntu installation: . After installation, you should also set a password for the default postgres user. Sample instructions for Ubuntu follow: First, install PostgreSQL and start the database server:

sudo apt install postgresql-12 sudo service postgresql start Log-in with the "postgres" user and start the PostgreSQL command line promt: sudo su - postgres psql Change the "postgres" user password by executing: \password and following the instructions given. Quit the prompt with: \q Log-in back with your normal account and restart the PostgreSQL service: su - sudo service postgresql restart

1

1.2 macOS

We recommend using Homebrew for installing and maintaining software on macOS. To install PostgreSQL using Homebrew, follow the steps below. If you already have Homebrew installed, jump to step 2.

1. Open a Terminal (located in Applications/Utilities/Terminal.app), and run the following command:

/usr/bin/ruby -e "$(curl -fsSL )"

This will install Homebrew into your user directory. Carefully follow all instructions provided by the installation script. 2. Now we can use Homebrew to install PosgreSQL using the following command: brew install postgres If the command brew is not available, it means you have not added it to your PATH. 3. Start the PostgreSQL background service on your computer by running: brew services start postgresql 4. You should now be able to access a database session with the psql command: psql postgres This command logs into the database "postgres" with the specified username. should be replaced by the name of your user account (you can find your username by running echo $USER). 5. Change the password of your user by running: \password ...and following the instructions. 6. To quit the interactive session, run: \q

1.3 Windows

A good option is to download the interactive installer by EnterpriseDB. If you have issues opening the link from the download section of the official website, you may try to use the following direct links: x86-64 This installer will also guide you through any other steps needed, such as setting up the postgres user password. During installation, you will be prompted to select the components you want to install. pgAdmin provides a graphical interface for managing your server and databases, which you might find useful during the course. You may safely deselect Stack Builder, which would allow you to install additional software, such as drivers and plugins.

2

2 Python

2.1 Linux

On apt-based distributions, install Python by running:

sudo apt install python3

You must also install the Python SQL package with the pip tool, by executing:

pip3 install ipython-sql psycopg2

2.2 macOS

Install Python using Homebrew, by running: brew install python3

You must also install the Python SQL package with the pip tool, by executing:

pip3 install ipython-sql psycopg2

2.3 Windows

Navigate to the official Python website (), where you can find instructions on how to download and install the language and a set of useful tools. We suggest that you install version 3 for easier support if the need arises. Make sure that you select the options to install pip and to add Python to the environment variables during installation. Otherwise, you will have to add it to your PATH later on. You must also install the Python SQL package, which you may achieve with the command below. Open the Command Prompt by pressing Win + R and running cmd, and enter:

pip install ipython-sql psycopg2

In case you didn't setup the PATH during the installation (if you have already previously installed Python), the command above will not work. Before executing it you will need to modify the Windows PATH by yourself. It can be done in graphical (just in Windows settings) or in command line mode. The way to do this is described under the following links:

You should find at first the path on your computer, where the python .exe file is located (it will probably have a name just python.exe) and add this path (for example, C:\Python33). You may also simply run the installer once again and tick there the option "Add Python to PATH" (see the link, it has a picture with this option):

3 Jupyter

The easiest way is to install Jupyter as a Python package using pip:

pip3 install jupyter

You may need to replace pip3 with pip on Windows. Alternatively, you can navigate to the official Jupyter website (install), where you can find instructions on how to download and install the application. However, do NOT install it using Anaconda, or you might run into unexpected problems.

3

4 Test installation

First, open a Jupyter notebook. To do that, execute the following in a command window/terminal: jupyter notebook If, for any reason, that doesn't seem to work, try instead: python -m notebook This will start a web server on your computer and will open a webpage in your default browser (localhost:8888/tree). By clicking on 'New' in the right-hand side of the webpage, you can create a new notebook with Python, which will open in a new tab. To load the SQL package, write the following in the notebook and press run: %load_ext sql Now, you may connect to the PostgreSQL server. Note, that you should substitute user:pass with the name of a PostgreSQL user and the corresponding password. You may use the default postgres user (on macOS, the username will be your account name (find this with echo $USER)) and the password set during/after installation. %sql postgresql://user:pass@localhost/postgres You should now be able to execute SQL queries, such as the following, which creates and populates a table: %%sql CREATE TABLE mytable(foo varchar(5), bar integer); INSERT INTO mytable VALUES ('bar', 4); INSERT INTO mytable VALUES ('asdf', 4); INSERT INTO mytable VALUES ('bqear', 3); INSERT INTO mytable VALUES ('sdf', 4); The following will print the values inserted previously into mytable: %%sql SELECT * FROM mytable; If your outcome looks like the following, then congratulations, PostgreSQL, Python and Jupyter are now running successfully on your machine:

Note: If you don't see any output, and your code snippets all have a [*] to the left, a recent update might have caused your installation to go wrong. Try executing the following in a command window/terminal and then restarting Jupyter: pip install prompt-toolkit==2.0.4

4

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download