How to Install Psycopg2 in Windows
Introduction
In this article we talk in detail about how to install psycopg2 in Windows. Included are the command line commands and code you’ll need to do it yourself.
Prerequisites
Before we can install psycopg2 on windows operating system we must make certain prerequisites
Make sure to install and set up Python3 on your local device because Python2 is already deprecated and losses its support.
Basic knowledge on using Python and PostgreSQL database.
Install and setup the Python 3 in Windows 10
To install Python using the interactive EXE installer make sure to download it first and include the path. Also check the py launcher and pip checkboxes as well.
After it’s done installing, open the command prompt window terminal to verify if the Python3 is properly installed and working on your device :
1 | py -V |
The command above will return the Python version number.
Set the paths for python3 and pip3
To prevent an error when starting up python in your command prompt window, setup the PATH variable location of the Python so that the system will recognize it.
1 2 | setx PATH "%PATH%;C:\Python38" setx PATH "%PATH%;C:\Python38\Scripts" |
Create a Python virtual environment on Windows 10
To install several packages from the PIP3 command and use it for your environment, you can create a virtual environment in Python. Use the command py -m with the venv tag for virtual environment and followed by the name of the virtual env you desire.
1 | py -m venv demo |
Once it’s finished you can input dir, and you should see the demo directory. Enter into the directory with the following:
1 | cd demo |
Now go inside the Scripts directory and execute the activate.bat file to enter inside the virtual environment:
1 | activate.bat |
Upgrade pip3 using the py command
1 | py -m pip install --upgrade pip |
If you get an error message about the user permission, you can use the --user flag while installing it.
1 | py -m pip install --upgrade pip --user |
The python3 and pip3 wrappers for the respective Python3 and PIP3 commands are now deprecated in Windows10, you can use the py and py -m pip instead.
Execute the command py in your command prompt window to open the and enter into the interactive interpreter for Python 3. Just press the button CTRL + Z and then RETURN to exit the interface.
To check and verify the version of the PIP3 in your device, use the command below :
1 | py -m pip -V |
It should return something like this : pip 20.0.2 from C:\Path\To\AppData\Roaming\Python\Python38\site-packages\pip (python 3.8) because of the upgrade flag that we have used earlier.
Install the psycopg2 module in Windows 10
Now that we have our virtual environment setup and ready, install the psycopg2 module to create a simple connection in the PostgreSQL database cluster.
1 | pip3 install psycopg2 |
Make sure to upgrade the pip command to avoid error when installing a package module from python in a virtual environment.
Create a Python script that connects into PostgreSQL database
On your command prompt window, use the command py or python to enter the Python interface.
1 2 3 | import psycopg2 conn = psycopg2.connect(user="postgres", password="143214", database="demo", host="localhost", port="5432") print("Successfully connected!") |
And now we will try to use the cursor command to perform any PostgreSQL database queries and operations using the Python script.
1 | cursor = conn.cursor() |
Then, print the version of the PostgreSQL installed in your device.
1 2 3 | cursor.execute("SELECT VERSION();") version = cursor.fetchone() print("Connected into - ", version) |
Let’s take a look at the output:
1 | Connected into - ('PostgreSQL 12.2, compiled by Visual C++ build 1914, 64-bit',) |
Create table using the cursor command
Another example on performing PostgreSQL commands would be creating a table inside the database demo.
1 2 | cursor.execute("CREATE TABLE py_tbl(id SERIAL PRIMARY KEY, str_col VARCHAR(50),int_col INT);") print("Table successfully created!") |
Next is to insert some record of rows inside the table.
1 2 3 | cursor.execute("INSERT INTO py_tbl(str_col, int_col) VALUES('Hello World!', 110);") cursor.execute("INSERT INTO py_tbl(str_col, int_col) VALUES('Welcome to ObjectRocket!', 120);") cursor.execute("INSERT INTO py_tbl(str_col, int_col) VALUES('Greetings!', 130);") |
Then create a commit function and display that the record is successfully inserted.
1 2 3 | conn.commit() print("Records Successfully Inserted!)" conn.close() |
Now go to your PostgreSQL shell or the psql to verify that the table has been created using the command \d in your database demo.
1 2 3 4 5 | List of relations Schema | Name | Type | Owner --------+--------+-------+---------- public | py_tbl | table | postgres (1 row) |
And use the SELECT statement to verify the records of row inserted.
1 | SELECT * FROM py_tbl; |
The result is the following:
1 2 3 4 5 | id | str_col | int_col ----+--------------------------+--------- 1 | Hello World! | 110 2 | Welcome to ObjectRocket! | 120 3 | Greetings! | 130 |
Conclusion
In this article we have covered how to install psycopg2 in Windows. Below is a recap of the code we used in this tutorial in case you’d like to use or repurpose it.
Just the code
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | import psycopg2 conn = psycopg2.connect(user="postgres", password="143214", database="demo", host="localhost", port="5432") print("Successfully connected!") cursor = conn.cursor() cursor.execute("SELECT VERSION();") version = cursor.fetchone() print("Connected into - ", version) cursor.execute("CREATE TABLE py_tbl(id SERIAL PRIMARY KEY, str_col VARCHAR(50),int_col INT);") print("Table successfully created!") cursor.execute("INSERT INTO py_tbl(str_col, int_col) VALUES('Hello World!', 110);") cursor.execute("INSERT INTO py_tbl(str_col, int_col) VALUES('Welcome to ObjectRocket!', 120);") cursor.execute("INSERT INTO py_tbl(str_col, int_col) VALUES('Greetings!', 130);") conn.commit() print("Records Successfully Inserted!)" conn.close() |
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started