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.
We need to install the psycopg2 module in python to call various functions in use to fulfill the requirements.
pip install psycopg2
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.
cur.mogrify(“INSERT INTO table_name (column) VALUES (%s, %s….)”, (value1, value2,…)(……))
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.
(34545, 'samuel', Decimal('48000.0')) (34546, 'rachel', Decimal('23232')) (34547, 'Sean', Decimal('92000.0'))
Please Login to comment...