Postgres usefull query

PostgreSQL Useful Queries

If you are programmer or DB administrator you need some useful query like how much data occupied by one database or how much data is for single or multiple tables, Below are useful queries.



Open terminal

psql -d test  #Login to your database which you want to know size.

SELECT pg_database_size(‘test’); # Check database size in Octal format.

SELECT pg_size_pretty(pg_database_size(‘test’)); # Check database size in actual format.

SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database; # Check all database size

SELECT pg_size_pretty(pg_relation_size(‘users’)); # Check particular table size

SELECT pg_size_pretty(pg_total_relation_size(‘users’)); #Check particular table size with relative table.

 SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1; #Find largest table

Postgres Python Integration

 How to fetch data from POSTGRESQL from Python script.

 Here you will learn how to connect your python script to postgresql database. You can see below example only trying to fetch data from database.

import psycopg2 # database connection


#Data base connection
try:
    conn = psycopg2.connect("dbname='YourDatabase' user='DBUserName' host='IP/locatalhost' password='DBPassword'")
except:
    print "I am unable to connect to the database"

cur = conn.cursor()
query = "SELECT name from users limit 5";
cur.execute(query)
rows = cur.fetchall()
for row in rows:
    print "   ", row[0]
conn.close()

Convert your string in python default types

 Convert your string in python default types

There are many possibilities while doing some complex programming and some data getting as string or JSON format but somehow it won't be converted into actual type like dictionary, list or tuple format.

Here you can find the alternate solution to get actual type using only few steps.

1) Open your terminal.

2) Type python [Python must be installed on your system.]

3) Past This example in your terminal.

#Import literal_eval python package
from ast import literal_eval

#Here is your string formatted dictionary
older_string = "{'a':123, 'b':}"

print older_string

#output as below
"{'a':123, 'b':}"

#Now using literal_eval lib to convert string to dictionary.
new_string = literal_eval(older_string)

print new_string

#output as below
{'a':123, 'b':}


NOTE :-
In case key value is missing than it won't be convert. it will throw exception.

 

num2text-arabic

How to print num2text in arabic version

There are few below steps you need to follow.

1) Open your terminal.

2) If you already install git library than it would be good if not than first install git library.

3) Clone this link using your terminal (Just copy past below link).

git clone https://github.com/ahmedzaqout/num2words.git

4) Install another python library bidi

 sudo apt-get install bidi

5) Once successfully installed python-bidi you need to again install rashaper lib.

sudo apt-get install git+https://github.com/mpcabd/python-arabic-reshaper

Now you have well enough library to print number to text as Arabic.

Here below few lines you need to copy past in your python file save it and run it.

1) Create new python file as test.py

2) Past below command in your test.py file and save it

from bidi.algorithm import get_display
import arabic_reshaper
from num2words import num2words
print get_display(arabic_reshaper.reshape(num2words(550,lang='ar')))
print get_display(arabic_reshaper.reshape(num2words(100125,lang='ar')))

3) Run this test.py file and you will get below output.

خمسمائة وخمسون
مائة ألف ومائة وخمسة وعشرون


Referenced From

How to use xmlrpc using Python for new verion

Integration with Odoo using XMLRPC New Version.

#Step1 Add library
import xmlrpclib

#Step2 Add Variables
url="http://YourIP/DomainName:PortNumber"
db = "v2"
username = "admin"
password = "admin"

#Call to server
common = xmlrpclib.ServerProxy("{}/xmlrpc/common".format(url), allow_none=True)
uid = common.login(db, username, password)
common = xmlrpclib.ServerProxy("{}/xmlrpc/2/object".format(url), allow_none=True)

#Call specific API
print common.execute_kw(db, uid, password, "Model.Name", "Model.Method.Name", "YourPayload", "ContextIncaseNeedToPass")


Add xmlrpc library in case didn't install in your server click here to download and install.

How to use xmlrpc using Python older version

Integration with Odoo using XMLRPC Older Version.

#Step1 Add library
import xmlrpclib

#Step2 Add Variables
url="http://YourIP/DomainName:PortNumber"
db = "v2"
username = "admin"
password = "admin"

#Call to server
common = xmlrpclib.ServerProxy("{}/xmlrpc/common".format(url), allow_none=True)
uid = common.login(db, username, password)
common = xmlrpclib.ServerProxy("{}/xmlrpc/object".format(url), allow_none=True)

#Call specific API
print common.execute(db, uid, password, "Model.Name", "Model.Method.Name", "YourPayload", "ContextIncaseNeedToPass")


Add xmlrpc library in case didn't install in your server click here to download and install.