Psycopg2 postgres python DDL Operaytions (select , Insert , update, create table)

Information about Psycopg2 postgres python DDL Operaytions (select , Insert , update,...

Published on June 21, 2020

Author: sachinkumar360

Source: slideshare.net

Content

1. PostgreSQL Python 1. psycopg2-binary : PostgreSQL Adaptor 2.DDL Operations 3.DML Operations

2. Installation  pip install psycopg2-binary  pip3 install psycopg2-binary

3. Python Connectors Python Application pymongo sqlite3 mySQL pyscopg2 Python Module/Connector Python DB API’s Request Response

4. Steps 1) Create Connection string 2) Open connection 3) Open cursor 4) Execute cursor 5) Close Cursor 6) Close Connection

5. Connection Connection class  Handles connection to database instances  Encapsulates a database session Connection String Example psycopg2.connect(database="sample",user="postgres",password="sample", host="127.0.0.1", port="5432") connection.cursor( ) creates a cursor which will be used throughout of your database programming with Python

6. Connection Method ( ) connection.commit ( )  Commits the current transaction  Changes will not reflect to other connections if not commited  Default, psycopg opens a transaction before executing the first command connection.rollback( )  This method rolls back any changes to the database since the last call to commit( ) connection.close( )  This Method closes the database connection.  If you close your database connection without calling commit() first, your changes will be lost!

7. CURSOR  Cursor is Class  Allows Python code to execute PostgreSQL command in a database session  Created by  connection.cursor( ) Method  cursor.execute( ), cursor.fetch( ),cursor.close( )

8. Execute( ) cursor.execute (query [, optional parameters])  Execute a database operation (query or command) returns None.  Cursor.execute(“select * from test”)  The returned values can be retrieved using fetch*( ) methods cursor.executemany(query, vars_list)  Execute a database operation (query or command) against all parameter tuples or mappings found in the sequence vars_list

9. Call Procedures  cursor.callproc(procname[, parameters]) - Call database stored procedure - The sequence of parameters must contain one entry for each argument that the procedure expects - Overloaded procedure supported - The procedure may provide a result set as output. - This is then made available through the standard fetch*() methods

10. Fetch()  cursor.fetchone( ) - Fetch the next row of a query result set, returning a single tuple  cursor.fetchall( ) - This routine fetches all (remaining) rows of a query result, returning a list. An empty list is returned when no rows are available.  cursor.fetchmany([size=cursor.arraysize]) - Fetch the next set of rows of a query result, returning a list of tuples.An empty list is returned when no more rows are available.

11. Cursor.* Cursor.rowcount This read-only attribute which returns the total number of database rows that have been modified, inserted, or deleted by the last last execute*(). Cursor.query Returns last executed query

12. DDL/DML OPERATIONS -- Create Table -- Drop Table -- Insert Record -- Update Record -- Select Record -- Delete Record

13. List Database #Import library import psycopg2 #Open Connection conn = psycopg2.connect(database="postgres", user='postgres', password='postgres', host='127.0.0.1', port= '5432') #Open cursor cursor = conn.cursor() #Execute Cursor cursor.execute("select datname from

14. Select import psycopg2 conn = psycopg2.connect(database = "postgres", user = "postgres", password = "postgres", host = "127.0.0.1", port = "5432") cursor = conn.cursor() cursor.execute("select * from test2") data = cursor.fetchone() cursor.execute("select * from test2") data = cursor.fetchmany(2)

15. Create Table import psycopg2 conn = psycopg2.connect(database = "postgres", user = "postgres", password = "postgres", host = "127.0.0.1", port = "5432") print ("Opened database successfully") cur = conn.cursor() cur.execute('''CREATE TABLE COMPS (ID INT PRIMARY KEY NOT NULL,

16. Insert #CREATE TABLE - cursor.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar(99));") - conn.commit #INSERT/UPDATE RECORD cursor.execute("INSERT INTO test (num, data) VALUES (%s, %s)",(100, "sqlnosql.com")) conn.commit #UPDATE cursor.execute("UPDATE test2 set num = 999 where ID = 4") conn.commit cursor.rowcount

17. Python file Write code in python file Vi a.py #Execute python3 a.py

18. Contact Website: www.sqlnosql.com Email : [email protected] Linkedin : https://www.linkedin.com/in/sachinep84/

#import presentations

Introduction to ml
18. 06. 2020
0 views

Introduction to ml

Python OpenCV Real Time projects
31. 05. 2020
0 views

Python OpenCV Real Time projects

Related presentations