PostgreSQL – Connect DB with Python psycopg2

Psycopg is the most popular PostgreSQL database adapter for the Python programming language. Many Python types are supported out-of-the-box and adapted to matching PostgreSQL data types; adaptation can be extended and customized thanks to a flexible objects adaptation system.

Syntax

>>> import psycopg2
>>> connection = psycopg2.connect(host="hostname_or_ip",database="db_name", user="user_name", password="password")

Example

>>> import psycopg2
>>>
>>> connection = psycopg2.connect(host="localhost",database="odoo_12", user="sathiyan", password="postgres")
>>> cursor = connection.cursor()
>>> query = ("SELECT name FROM res_partner")
>>> query_execute = cursor.execute(query)
>>> rows = cursor.fetchall()
>>> for row in rows:
...     print(row[0])
... 

"ABC Pvt. Ltd."
"A-Z Pvt. Ltd."
"Y! Ltd."
>>> cursor.close()
>>> connection.close()

Reference

Odoo 12 With PyCharm Install – Ubuntu 18.04 LTS

Step 1

  • Run following commands
sudo apt-get update
sudo apt-get -y upgrade

Install Python Dependencies

sudo apt install git python3-pip build-essential wget python3-dev python3-venv python3-wheel libxslt-dev libzip-dev libldap2-dev libsasl2-dev python3-setuptools node-less
pip3 install Babel decorator docutils ebaysdk feedparser gevent greenlet html2text Jinja2 lxml Mako MarkupSafe mock num2words ofxparse passlib Pillow psutil psycogreen psycopg2 pydot pyparsing PyPDF2 pyserial python-dateutil python-openid pytz pyusb PyYAML qrcode reportlab requests six suds-jurko vatnumber vobject Werkzeug XlsxWriter xlwt xlrd

Install Odoo Web Dependencies

sudo apt-get install -y npm
sudo ln -s /usr/bin/nodejs /usr/bin/node
sudo npm install -g less less-plugin-clean-css
sudo apt-get install node-less
sudo python3 -m pip install libass

Install wkhtmltopdf

sudo wget https://builds.wkhtmltopdf.org/0.12.1.3/wkhtmltox_0.12.1.3-1~bionic_amd64.deb
sudo dpkg -i wkhtmltox_0.12.1.3-1~bionic_amd64.deb
sudo apt-get install -f
sudo ln -s /usr/local/bin/wkhtmltopdf /usr/bin
sudo ln -s /usr/local/bin/wkhtmltoimage /usr/bin

Install & Configure Postgres

sudo apt -y install postgresql postgresql-contrib phppgadmin
su – postgres
psql
\password postgres
# Enter the password for postgres user
\q
exit
su – postgres
createuser –interactive <username>
# Make this new user a superuser.
# Create the username with care this user will be used in Odoo “odoo_12.conf” file.
Connection Settings
cd
cd /etc/postgresql/10/main
sudo nano postgresql.conf
# Find and change value of listen_addresses as shown below, 
listen_addresses = '*'
Configure Apache Web Server
cd
cd /etc/apache2/conf-available/
sudo nano phppgadmin.conf
Find the below content

# Only allow connections from localhost:
Require local
And change as it shown below,
# Only allow connections from localhost:
# Require local
Require all granted
Configure phpPgAdmin
cd
cd /etc/phppgadmin/
sudo nano config.inc.php
$conf['extra_login_security'] = true; 
# Find and Change the above line as shown below,
$conf['extra_login_security'] = false;
Re-start Apache & Postgres
systemctl restart postgresql
systemctl restart apache2

Now access phpPgAdmin with your browser http://localhost/phppgadmin

Step 2

  • Download Pycharm Community Edition from ”Ubuntu Software Center” or download the source and install.
  • Clone Odoo source code from git hub, this has to be done in PyCharm.

Step 3

  • Odoo configuration file need to be created in side odoo folder.
  • Note: Here I clone Odoo source code to odoo folder.
[options]
:admin_passwd = admin
db_host = localhost
db_port = 5432
db_user = odoo_user_db
db_password = odoo
xmlrpc_port = 8069
addons_path = home/sathiyan/PycharmProjects/odoo/addons,/ home/sathiyan/PycharmProjects/odoo/odoo/addons

Step 4

In PyCharm menu Run –> Edit Configurations, click + on the top left to create a new configuration and choose Python.

And follow the setting as shown in the below screenshot,

Beauty of this you can run many instance of any version of Odoo with any number of addons. You can update all selected module in “Parameters” field. And integration git will come in handy.

PostgreSQL – DB Backup Script

Following script will back-up postgres DB and delete older backups.

#!/bin/sh
cd /home/sathiyan/backups
NOW=$(date +"%d_%m_%Y_%H_%M_%S")
echo "Backing Up PostgreSQL Database..."
pg_dump -U $username $db_name -f "X_"$NOW".dump" | gzip -9 > "X_"$NOW".gz"
# X is string, better to put db name here.
echo "Removing Files Older Than A Day..."
find /home/sathiyan/backups/ -mtime +0 -type f -delete
exit 1