PostgreSQL

In your Data science journey you wont be receiving data in excel sheets. Data will mostly in DB from where we have to extract necessary data. Here we will see SQL data base postgresql and how to access it in your python code for your AI or Data Science activities.

What is a DataBase?

A database is a collection of tables related to each other via columns. For most real-world projects, a database is a must. We can use SQL (Structured Query Language) to create, access, and manipulate data. We can also make use of normalization to avoid redundancy of data.

For database programming, Python supports many database servers - MySQL, Oracle, PostgreSQL, SQLite, Sybase, Microsoft SQL Server, mSQL, Microsoft Access, and many more. It also supports Data Query Statements, Data Definition Language (DDL), and Data Manipulation Language (DML).

How to connect to PostgreSQL?

We will see how to connect to PostgreSQL through these tools

• psql — a terminal-based front-end to PostgreSQL database server.
• pgAdmin — a web-based front-end to PostgreSQL database server.

Part 1 : Connect to PostgreSQL database server using psql

First, launch the psql program and connect to the PostgreSQL Database Server using the postgres user:

You can click enter and it will go onto next steps. In the password section give the password you have entered , all the other details server , database , port default values it will take.

PSQL command shell connecting to postgresql server

Check the version of PostgreSQL

SELECT version();

postgresql version

Please do not forget to end the statement with a semicolon (;). After pressing Enter, psql will return the current PostgreSQL version on your system.

Part 2: Connect to PostgreSQL database server using pgAdmin

First, launch the pgAdmin application.

The pgAdmin application will launch on the web browser as shown in the following picture

To create data base ,right-click the Servers node and select Create > Server… menu to create a server

You can specify the data base name ,enter the host and password for the postgres user and click the Save button

I have created a data base called course_data. Select the db open the query tool by choosing the menu item Tool > Query Tool or click the first icon just below “object”.

In the query tool test the version of postgresql.

#sql #python #data-analysis #data-science #postgresql