Format SQL in Python with Psycopg’s Mogrify

Psycopg, the Python PostgreSQL driver, includes a very useful mechanism for formatting SQL in python, which is mogrify. 

After parameters binding, returns a query string. The string returned is the same as what SQLwas sent to the database if you used the execute() function or anything similar. One may use the same inputs for mogrify() as you would for execute(), and the outcome will be as expected.

Installation:

We need to install the psycopg2 module in python to call various functions in use to fulfill the requirements.

Syntax:

pip install psycopg2

cursor.mogrify() method:

cursor.mogrify() method returns a query string once the parameters have been bound. If you used the execute() method or anything similar, the string returned is the same as what would be sent to the database. The resulting string is always a bytes string, which is quicker than using the executemany() function.

Syntax:

cur.mogrify(“INSERT INTO table_name (column) VALUES (%s, %s….)”, (value1, value2,…)(……))

Example:

psycopg2 package is imported, a connection to the database is established using psycopg2.connection() method. Autocommit is set to true and a cursor is created using conn.cursor() method. A table is created in the database and cursor.mogrify() method is used to create a formatted SQL to insert values into the table. Cursor.mogrify() gives a bytes string, but we want it to be in string format, thus we only need to use the decode(‘UTF-8’) technique to decode the output of mogrify back to a string. Later data is fetched using the fetchall() method and changes are committed.

Python3

# importing packages
import psycopg2

# forming connection
conn = psycopg2.connect(
    database="Emp_database",
    user='postgres',
    password='pass',
    host='127.0.0.1',
    port='5432'
)

conn.autocommit = True

# creating a cursor
cursor = conn.cursor()

cursor.execute(
    'create table emp_table(emp_code int,\
    emp_name varchar(30), emp_salary decimal)')

# list of rows to be inserted

values = [(34545, 'samuel', 48000.0),
          (34546, 'rachel', 23232),
          (34547, 'Sean', 92000.0)]

# cursor.mogrify() to insert multiple values
args = ','.join(cursor.mogrify("(%s,%s,%s)", i).decode('utf-8')
                for i in values)

# executing the sql statement
cursor.execute("INSERT INTO emp_table  VALUES " + (args))

# select statement to display output
sql1 = '''select * from emp_table;'''

# executing sql statement
cursor.execute(sql1)

# fetching rows
for i in cursor.fetchall():
    print(i)

# committing changes
conn.commit()

# closing connection
conn.close()

Output:

(34545, 'samuel', Decimal('48000.0'))
(34546, 'rachel', Decimal('23232'))
(34547, 'Sean', Decimal('92000.0'))