Category: python Data Access

Discuss Python Data Access ”; Previous Next Python is a general-purpose interpreted, interactive, object-oriented, and high-level programming language. It was created by Guido van Rossum during 1985-1990. Like Perl, Python source code is also available under the GNU General Public License (GPL). This tutorial gives enough understanding on Python programming language. It provides various modules to communicate with various databases. In this tutorial, we are going to discuss python modules to communicate with the databases MySQL, PostgreSQL, SQLite and, MongoDB. Print Page Previous Next Advertisements ”;

Python PostgreSQL – Delete Data ”; Previous Next You can delete the records in an existing table using the DELETE FROM statement of PostgreSQL database. To remove specific records, you need to use WHERE clause along with it. Syntax Following is the syntax of the DELETE query in PostgreSQL − DELETE FROM table_name [WHERE Clause] Example Assume we have created a table with name CRICKETERS using the following query − postgres=# CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); CREATE TABLE postgres=# And if we have inserted 5 records in to it using INSERT statements as − postgres=# insert into CRICKETERS values (”Shikhar”, ”Dhawan”, 33, ”Delhi”, ”India”); INSERT 0 1 postgres=# insert into CRICKETERS values (”Jonathan”, ”Trott”, 38, ”CapeTown”, ”SouthAfrica”); INSERT 0 1 postgres=# insert into CRICKETERS values (”Kumara”, ”Sangakkara”, 41, ”Matale”, ”Srilanka”); INSERT 0 1 postgres=# insert into CRICKETERS values (”Virat”, ”Kohli”, 30, ”Delhi”, ”India”); INSERT 0 1 postgres=# insert into CRICKETERS values (”Rohit”, ”Sharma”, 32, ”Nagpur”, ”India”); INSERT 0 1 Following statement deletes the record of the cricketer whose last name is ”Sangakkara”. − postgres=# DELETE FROM CRICKETERS WHERE LAST_NAME = ”Sangakkara”; DELETE 1 If you retrieve the contents of the table using the SELECT statement, you can see only 4 records since we have deleted one. postgres=# SELECT * FROM CRICKETERS; first_name | last_name | age | place_of_birth | country ————+———–+—–+—————-+————- Jonathan | Trott | 39 | CapeTown | SouthAfrica Virat | Kohli | 31 | Delhi | India Rohit | Sharma | 33 | Nagpur | India Shikhar | Dhawan | 46 | Delhi | India (4 rows) If you execute the DELETE FROM statement without the WHERE clause all the records from the specified table will be deleted. postgres=# DELETE FROM CRICKETERS; DELETE 4 Since you have deleted all the records, if you try to retrieve the contents of the CRICKETERS table, using SELECT statement you will get an empty result set as shown below − postgres=# SELECT * FROM CRICKETERS; first_name | last_name | age | place_of_birth | country ————+———–+—–+—————-+——— (0 rows) Deleting data using python The cursor class of psycopg2 provides a method with name execute() method. This method accepts the query as a parameter and executes it. Therefore, to insert data into a table in PostgreSQL using python − Import psycopg2 package. Create a connection object using the connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it. Turn off the auto-commit mode by setting false as value to the attribute autocommit. The cursor() method of the Connection class of the psycopg2 library returns a cursor object. Create a cursor object using this method. Then, execute the UPDATE statement by passing it as a parameter to the execute() method. Example Following Python code deletes records of the EMPLOYEE table with age values greater than 25 − import psycopg2 #establishing the connection conn = psycopg2.connect( database=”mydb”, user=”postgres”, password=”password”, host=”127.0.0.1”, port= ”5432” ) #Setting auto commit false conn.autocommit = True #Creating a cursor object using the cursor() method cursor = conn.cursor() #Retrieving contents of the table print(“Contents of the table: “) cursor.execute(”””SELECT * from EMPLOYEE”””) print(cursor.fetchall()) #Deleting records cursor.execute(”””DELETE FROM EMPLOYEE WHERE AGE > 25”””) #Retrieving data after delete print(“Contents of the table after delete operation “) cursor.execute(“SELECT * from EMPLOYEE”) print(cursor.fetchall()) #Commit your changes in the database conn.commit() #Closing the connection conn.close() Output Contents of the table: [(”Ramya”, ”Rama priya”, 27, ”F”, 9000.0), (”Sarmista”, ”Sharma”, 26, ”F”, 10000.0), (”Tripthi”, ”Mishra”, 24, ”F”, 6000.0), (”Vinay”, ”Battacharya”, 21, ”M”, 6000.0), (”Sharukh”, ”Sheik”, 26, ”M”, 8300.0)] Contents of the table after delete operation: [(”Tripthi”, ”Mishra”, 24, ”F”, 6000.0), (”Vinay”, ”Battacharya”, 21, ”M”, 6000.0)] Print Page Previous Next Advertisements ”;

Python SQLite – Introduction ”; Previous Next Installation SQLite3 can be integrated with Python using sqlite3 module, which was written by Gerhard Haring. It provides an SQL interface compliant with the DB-API 2.0 specification described by PEP 249. You do not need to install this module separately because it is shipped by default along with Python version 2.5.x onwards. To use sqlite3 module, you must first create a connection object that represents the database and then optionally you can create a cursor object, which will help you in executing all the SQL statements. Python sqlite3 module APIs Following are important sqlite3 module routines, which can suffice your requirement to work with SQLite database from your Python program. If you are looking for a more sophisticated application, then you can look into Python sqlite3 module”s official documentation. Sr.No. API & Description 1 sqlite3.connect(database [,timeout ,other optional arguments]) This API opens a connection to the SQLite database file. You can use “:memory:” to open a database connection to a database that resides in RAM instead of on disk. If database is opened successfully, it returns a connection object. 2 connection.cursor([cursorClass]) This routine creates a cursor which will be used throughout your database programming with Python. This method accepts a single optional parameter cursorClass. If supplied, this must be a custom cursor class that extends sqlite3.Cursor. 3 cursor.execute(sql [, optional parameters]) This routine executes an SQL statement. The SQL statement may be parameterized (i. e. placeholders instead of SQL literals). The sqlite3 module supports two kinds of placeholders: question marks and named placeholders (named style). For example − cursor.execute(“insert into people values (?, ?)”, (who, age)) 4 connection.execute(sql [, optional parameters]) This routine is a shortcut of the above execute method provided by the cursor object and it creates an intermediate cursor object by calling the cursor method, then calls the cursor”s execute method with the parameters given. 5 cursor.executemany(sql, seq_of_parameters) This routine executes an SQL command against all parameter sequences or mappings found in the sequence sql. 6 connection.executemany(sql[, parameters]) This routine is a shortcut that creates an intermediate cursor object by calling the cursor method, then calls the cursor.s executemany method with the parameters given. 7 cursor.executescript(sql_script) This routine executes multiple SQL statements at once provided in the form of script. It issues a COMMIT statement first, then executes the SQL script it gets as a parameter. All the SQL statements should be separated by a semi colon (;). 8 connection.executescript(sql_script) This routine is a shortcut that creates an intermediate cursor object by calling the cursor method, then calls the cursor”s executescript method with the parameters given. 9 connection.total_changes() This routine returns the total number of database rows that have been modified, inserted, or deleted since the database connection was opened. 10 connection.commit() This method commits the current transaction. If you don”t call this method, anything you did since the last call to commit() is not visible from other database connections. 11 connection.rollback() This method rolls back any changes to the database since the last call to commit(). 12 connection.close() This method closes the database connection. Note that this does not automatically call commit(). If you just close your database connection without calling commit() first, your changes will be lost! 13 cursor.fetchone() This method fetches the next row of a query result set, returning a single sequence, or None when no more data is available. 14 cursor.fetchmany([size = cursor.arraysize]) This routine fetches the next set of rows of a query result, returning a list. An empty list is returned when no more rows are available. The method tries to fetch as many rows as indicated by the size parameter. 15 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. Print Page Previous Next Advertisements ”;

Python PostgreSQL – Limit ”; Previous Next While executing a PostgreSQL SELECT statement you can limit the number of records in its result using the LIMIT clause. Syntax Following is the syntax of the LMIT clause in PostgreSQL − SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] Example Assume we have created a table with name CRICKETERS using the following query − postgres=# CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); CREATE TABLE postgres=# And if we have inserted 5 records in to it using INSERT statements as − postgres=# insert into CRICKETERS values (”Shikhar”, ”Dhawan”, 33, ”Delhi”, ”India”); INSERT 0 1 postgres=# insert into CRICKETERS values (”Jonathan”, ”Trott”, 38, ”CapeTown”, ”SouthAfrica”); INSERT 0 1 postgres=# insert into CRICKETERS values (”Kumara”, ”Sangakkara”, 41, ”Matale”, ”Srilanka”); INSERT 0 1 postgres=# insert into CRICKETERS values (”Virat”, ”Kohli”, 30, ”Delhi”, ”India”); INSERT 0 1 postgres=# insert into CRICKETERS values (”Rohit”, ”Sharma”, 32, ”Nagpur”, ”India”); INSERT 0 1 Following statement retrieves the first 3 records of the Cricketers table using the LIMIT clause − postgres=# SELECT * FROM CRICKETERS LIMIT 3; first_name | last_name | age | place_of_birth | country ————+————+—–+—————-+————- Shikhar | Dhawan | 33 | Delhi | India Jonathan | Trott | 38 | CapeTown | SouthAfrica Kumara | Sangakkara | 41 | Matale | Srilanka (3 rows) If you want to get records starting from a particular record (offset) you can do so, using the OFFSET clause along with LIMIT. postgres=# SELECT * FROM CRICKETERS LIMIT 3 OFFSET 2; first_name | last_name | age | place_of_birth | country ————+————+—–+—————-+———- Kumara | Sangakkara | 41 | Matale | Srilanka Virat | Kohli | 30 | Delhi | India Rohit | Sharma | 32 | Nagpur | India (3 rows) postgres=# Limit clause using python Following python example retrieves the contents of a table named EMPLOYEE, limiting the number of records in the result to 2 − Example import psycopg2 #establishing the connection conn = psycopg2.connect( database=”mydb”, user=”postgres”, password=”password”, host=”127.0.0.1”, port= ”5432” ) #Setting auto commit false conn.autocommit = True #Creating a cursor object using the cursor() method cursor = conn.cursor() #Retrieving single row sql = ”””SELECT * from EMPLOYEE LIMIT 2 OFFSET 2””” #Executing the query cursor.execute(sql) #Fetching the data result = cursor.fetchall(); print(result) #Commit your changes in the database conn.commit() #Closing the connection conn.close() Output [(”Sharukh”, ”Sheik”, 25, ”M”, 8300.0), (”Sarmista”, ”Sharma”, 26, ”F”, 10000.0)] Print Page Previous Next Advertisements ”;

Python PostgreSQL – Order By ”; Previous Next Usually if you try to retrieve data from a table, you will get the records in the same order in which you have inserted them. Using the ORDER BY clause, while retrieving the records of a table you can sort the resultant records in ascending or descending order based on the desired column. Syntax Following is the syntax of the ORDER BY clause in PostgreSQL. SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC]; Example Assume we have created a table with name CRICKETERS using the following query − postgres=# CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); CREATE TABLE postgres=# And if we have inserted 5 records in to it using INSERT statements as − postgres=# insert into CRICKETERS values(”Shikhar”, ”Dhawan”, 33, ”Delhi”, ”India”); INSERT 0 1 postgres=# insert into CRICKETERS values(”Jonathan”, ”Trott”, 38, ”CapeTown”, ”SouthAfrica”); INSERT 0 1 postgres=# insert into CRICKETERS values(”Kumara”, ”Sangakkara”, 41, ”Matale”, ”Srilanka”); INSERT 0 1 postgres=# insert into CRICKETERS values(”Virat”, ”Kohli”, 30, ”Delhi”, ”India”); INSERT 0 1 postgres=# insert into CRICKETERS values(”Rohit”, ”Sharma”, 32, ”Nagpur”, ”India”); INSERT 0 1 Following SELECT statement retrieves the rows of the CRICKETERS table in the ascending order of their age − postgres=# SELECT * FROM CRICKETERS ORDER BY AGE; first_name | last_name | age | place_of_birth | country ————+————+—–+—————-+————- Virat | Kohli | 30 | Delhi | India Rohit | Sharma | 32 | Nagpur | India Shikhar | Dhawan | 33 | Delhi | India Jonathan | Trott | 38 | CapeTown | SouthAfrica Kumara | Sangakkara | 41 | Matale | Srilanka (5 rows)es: You can use more than one column to sort the records of a table. Following SELECT statements sort the records of the CRICKETERS table based on the columns age and FIRST_NAME. postgres=# SELECT * FROM CRICKETERS ORDER BY AGE, FIRST_NAME; first_name | last_name | age | place_of_birth | country ————+————+—–+—————-+————- Virat | Kohli | 30 | Delhi | India Rohit | Sharma | 32 | Nagpur | India Shikhar | Dhawan | 33 | Delhi | India Jonathan | Trott | 38 | CapeTown | SouthAfrica Kumara | Sangakkara | 41 | Matale | Srilanka (5 rows) By default, the ORDER BY clause sorts the records of a table in ascending order. You can arrange the results in descending order using DESC as − postgres=# SELECT * FROM CRICKETERS ORDER BY AGE DESC; first_name | last_name | age | place_of_birth | country ————+————+—–+—————-+————- Kumara | Sangakkara | 41 | Matale | Srilanka Jonathan | Trott | 38 | CapeTown | SouthAfrica Shikhar | Dhawan | 33 | Delhi | India Rohit | Sharma | 32 | Nagpur | India Virat | Kohli | 30 | Delhi | India (5 rows) ORDER BY clause using python To retrieve contents of a table in specific order, invoke the execute() method on the cursor object and, pass the SELECT statement along with ORDER BY clause, as a parameter to it. Example In the following example, we are creating a table with name and Employee, populating it, and retrieving its records back in the (ascending) order of their age, using the ORDER BY clause. import psycopg2 #establishing the connection conn = psycopg2.connect( database=”mydb”, user=”postgres”, password=”password”, host=”127.0.0.1”, port= ”5432” ) #Setting auto commit false conn.autocommit = True #Creating a cursor object using the cursor() method cursor = conn.cursor() #Doping EMPLOYEE table if already exists. cursor.execute(“DROP TABLE IF EXISTS EMPLOYEE”) #Creating a table sql = ”””CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME INT, CONTACT INT )””” cursor.execute(sql) #Populating the table insert_stmt = “INSERT INTO EMPLOYEE ( FIRST_NAME, LAST_NAME, AGE, SEX, INCOME, CONTACT) VALUES (%s, %s, %s, %s, %s, %s)” data = [(”Krishna”, ”Sharma”, 26, ”M”, 2000, 101), (”Raj”, ”Kandukuri”, 20, ”M”, 7000, 102), (”Ramya”, ”Ramapriya”, 29, ”F”, 5000, 103), (”Mac”, ”Mohan”, 26, ”M”, 2000, 104)] cursor.executemany(insert_stmt, data) conn.commit() #Retrieving specific records using the ORDER BY clause cursor.execute(“SELECT * from EMPLOYEE ORDER BY AGE”) print(cursor.fetchall()) #Commit your changes in the database conn.commit() #Closing the connection conn.close() Output [(”Sharukh”, ”Sheik”, 25, ”M”, 8300.0), (”Sarmista”, ”Sharma”, 26, ”F”, 10000.0)] Print Page Previous Next Advertisements ”;

Python PostgreSQL – Create Table ”; Previous Next You can create a new table in a database in PostgreSQL using the CREATE TABLE statement. While executing this you need to specify the name of the table, column names and their data types. Syntax Following is the syntax of the CREATE TABLE statement in PostgreSQL. CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ….. columnN datatype, ); Example Following example creates a table with name CRICKETERS in PostgreSQL. postgres=# CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age INT, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); CREATE TABLE postgres=# You can get the list of tables in a database in PostgreSQL using the dt command. After creating a table, if you can verify the list of tables you can observe the newly created table in it as follows − postgres=# dt List of relations Schema | Name | Type | Owner ——–+————+——-+———- public | cricketers | table | postgres (1 row) postgres=# In the same way, you can get the description of the created table using d as shown below − postgres=# d cricketers Table “public.cricketers” Column | Type | Collation | Nullable | Default —————-+————————+———–+———-+——— first_name | character varying(255) | | | last_name | character varying(255) | | | age | integer | | | place_of_birth | character varying(255) | | | country | character varying(255) | | | postgres=# Creating a table using python To create a table using python you need to execute the CREATE TABLE statement using the execute() method of the Cursor of pyscopg2. Example The following Python example creates a table with name employee. import psycopg2 #Establishing the connection conn = psycopg2.connect( database=”mydb”, user=”postgres”, password=”password”, host=”127.0.0.1”, port= ”5432” ) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Doping EMPLOYEE table if already exists. cursor.execute(“DROP TABLE IF EXISTS EMPLOYEE”) #Creating table as per requirement sql =”””CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT )””” cursor.execute(sql) print(“Table created successfully……..”) conn.commit() #Closing the connection conn.close() Output Table created successfully…….. Print Page Previous Next Advertisements ”;

Python PostgreSQL – Database Connection ”; Previous Next PostgreSQL provides its own shell to execute queries. To establish connection with the PostgreSQL database, make sure that you have installed it properly in your system. Open the PostgreSQL shell prompt and pass details like Server, Database, username, and password. If all the details you have given are appropriate, a connection is established with PostgreSQL database. While passing the details you can go with the default server, database, port and, user name suggested by the shell. Establishing connection using python The connection class of the psycopg2 represents/handles an instance of a connection. You can create new connections using the connect() function. This accepts the basic connection parameters such as dbname, user, password, host, port and returns a connection object. Using this function, you can establish a connection with the PostgreSQL. Example The following Python code shows how to connect to an existing database. If the database does not exist, then it will be created and finally a database object will be returned. The name of the default database of PostgreSQL is postrgre. Therefore, we are supplying it as the database name. import psycopg2 #establishing the connection conn = psycopg2.connect( database=”postgres”, user=”postgres”, password=”password”, host=”127.0.0.1”, port= ”5432” ) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Executing an MYSQL function using the execute() method cursor.execute(“select version()”) # Fetch a single row using fetchone() method. data = cursor.fetchone() print(“Connection established to: “,data) #Closing the connection conn.close() Connection established to: ( ”PostgreSQL 11.5, compiled by Visual C++ build 1914, 64-bit”, ) Output Connection established to: ( ”PostgreSQL 11.5, compiled by Visual C++ build 1914, 64-bit”, ) Print Page Previous Next Advertisements ”;

Python PostgreSQL – Join ”; Previous Next When you have divided the data in two tables you can fetch combined records from these two tables using Joins. Example Assume we have created a table with name CRICKETERS and inserted 5 records into it as shown below − postgres=# CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); postgres=# insert into CRICKETERS values ( ”Shikhar”, ”Dhawan”, 33, ”Delhi”, ”India” ); postgres=# insert into CRICKETERS values ( ”Jonathan”, ”Trott”, 38, ”CapeTown”, ”SouthAfrica” ); postgres=# insert into CRICKETERS values ( ”Kumara”, ”Sangakkara”, 41, ”Matale”, ”Srilanka” ); postgres=# insert into CRICKETERS values ( ”Virat”, ”Kohli”, 30, ”Delhi”, ”India” ); postgres=# insert into CRICKETERS values ( ”Rohit”, ”Sharma”, 32, ”Nagpur”, ”India” ); And, if we have created another table with name OdiStats and inserted 5 records into it as − postgres=# CREATE TABLE ODIStats ( First_Name VARCHAR(255), Matches INT, Runs INT, AVG FLOAT, Centuries INT, HalfCenturies INT ); postgres=# insert into OdiStats values (”Shikhar”, 133, 5518, 44.5, 17, 27); postgres=# insert into OdiStats values (”Jonathan”, 68, 2819, 51.25, 4, 22); postgres=# insert into OdiStats values (”Kumara”, 404, 14234, 41.99, 25, 93); postgres=# insert into OdiStats values (”Virat”, 239, 11520, 60.31, 43, 54); postgres=# insert into OdiStats values (”Rohit”, 218, 8686, 48.53, 24, 42); Following statement retrieves data combining the values in these two tables − postgres=# SELECT Cricketers.First_Name, Cricketers.Last_Name, Cricketers.Country, OdiStats.matches, OdiStats.runs, OdiStats.centuries, OdiStats.halfcenturies from Cricketers INNER JOIN OdiStats ON Cricketers.First_Name = OdiStats.First_Name; first_name | last_name | country | matches | runs | centuries | halfcenturies ————+————+————-+———+——-+———–+————— Shikhar | Dhawan | India | 133 | 5518 | 17 | 27 Jonathan | Trott | SouthAfrica | 68 | 2819 | 4 | 22 Kumara | Sangakkara | Srilanka | 404 | 14234 | 25 | 93 Virat | Kohli | India | 239 | 11520 | 43 | 54 Rohit | Sharma | India | 218 | 8686 | 24 | 42 (5 rows) postgres=# Joins using python When you have divided the data in two tables you can fetch combined records from these two tables using Joins. Example Following python program demonstrates the usage of the JOIN clause − import psycopg2 #establishing the connection conn = psycopg2.connect( database=”mydb”, user=”postgres”, password=”password”, host=”127.0.0.1”, port= ”5432” ) #Setting auto commit false conn.autocommit = True #Creating a cursor object using the cursor() method cursor = conn.cursor() #Retrieving single row sql = ”””SELECT * from EMP INNER JOIN CONTACT ON EMP.CONTACT = CONTACT.ID””” #Executing the query cursor.execute(sql) #Fetching 1st row from the table result = cursor.fetchall(); print(result) #Commit your changes in the database conn.commit() #Closing the connection conn.close() Output [(”Ramya”, ”Rama priya”, 27, ”F”, 9000.0, 101, 101, ”[email protected]”, ”Hyderabad”), (”Vinay”, ”Battacharya”, 20, ”M”, 6000.0, 102, 102, ”[email protected]”, ”Vishakhapatnam”), (”Sharukh”, ”Sheik”, 25, ”M”, 8300.0, 103, 103, ”[email protected] ”, ”Pune”), (”Sarmista”, ”Sharma”, 26, ”F”, 10000.0, 104, 104, ”[email protected]”, ”Mumbai”)] Print Page Previous Next Advertisements ”;

Python MongoDB – Limit ”; Previous Next While retrieving the contents of a collection you can limit the number of documents in the result using the limit() method. This method accepts a number value representing the number of documents you want in the result. Syntax Following is the syntax of the limit() method − >db.COLLECTION_NAME.find().limit(NUMBER) Example Assume we have created a collection and inserted 5 documents into it as shown below − > use testDB switched to db testDB > db.createCollection(“sample”) { “ok” : 1 } > data = [ … {“_id”: “1001”, “name”: “Ram”, “age”: “26”, “city”: “Hyderabad”}, … {“_id”: “1002”, “name”: “Rahim”, “age”: 27, “city”: “Bangalore”}, … {“_id”: “1003”, “name”: “Robert”, “age”: 28, “city”: “Mumbai”}, … {“_id”: “1004”, “name”: “Romeo”, “age”: 25, “city”: “Pune”}, … {“_id”: “1005”, “name”: “Sarmista”, “age”: 23, “city”: “Delhi”}, … {“_id”: “1006”, “name”: “Rasajna”, “age”: 26, “city”: “Chennai”} ] > db.sample.insert(data) BulkWriteResult({ “writeErrors” : [ ], “writeConcernErrors” : [ ], “nInserted” : 6, “nUpserted” : 0, “nMatched” : 0, “nModified” : 0, “nRemoved” : 0, “upserted” : [ ] }) Following line retrieves the first 3 documents of the collection. > db.sample.find().limit(3) { “_id” : “1001”, “name” : “Ram”, “age” : “26”, “city” : “Hyderabad” } { “_id” : “1002”, “name” : “Rahim”, “age” : 27, “city” : “Bangalore” } { “_id” : “1003”, “name” : “Robert”, “age” : 28, “city” : “Mumbai” } Limiting the documents using python To restrict the results of a query to a particular number of documents pymongo provides the limit() method. To this method pass a number value representing the number of documents you need in the result. Example Following example retrieves first three documents in a collection. from pymongo import MongoClient #Creating a pymongo client client = MongoClient(”localhost”, 27017) #Getting the database instance db = client[”l”] #Creating a collection coll = db[”myColl”] #Inserting document into a collection data = [ {“_id”: “1001”, “name”: “Ram”, “age”: “26”, “city”: “Hyderabad”}, {“_id”: “1002”, “name”: “Rahim”, “age”: “27”, “city”: “Bangalore”}, {“_id”: “1003”, “name”: “Robert”, “age”: “28”, “city”: “Mumbai”}, {“_id”: “1004”, “name”: “Romeo”, “age”: 25, “city”: “Pune”}, {“_id”: “1005”, “name”: “Sarmista”, “age”: 23, “city”: “Delhi”}, {“_id”: “1006”, “name”: “Rasajna”, “age”: 26, “city”: “Chennai”} ] res = coll.insert_many(data) print(“Data inserted ……”) #Retrieving first 3 documents using the find() and limit() methods print(“First 3 documents in the collection: “) for doc1 in coll.find().limit(3): print(doc1) Output Data inserted …… First 3 documents in the collection: {”_id”: ”1001”, ”name”: ”Ram”, ”age”: ”26”, ”city”: ”Hyderabad”} {”_id”: ”1002”, ”name”: ”Rahim”, ”age”: ”27”, ”city”: ”Bangalore”} {”_id”: ”1003”, ”name”: ”Robert”, ”age”: ”28”, ”city”: ”Mumbai”} Print Page Previous Next Advertisements ”;

Python PostgreSQL – Drop Table ”; Previous Next You can drop a table from PostgreSQL database using the DROP TABLE statement. Syntax Following is the syntax of the DROP TABLE statement in PostgreSQL − DROP TABLE table_name; Example Assume we have created two tables with name CRICKETERS and EMPLOYEES using the following queries − postgres=# CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); CREATE TABLE postgres=# postgres=# CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT ); CREATE TABLE postgres=# Now if you verify the list of tables using the “dt” command, you can see the above created tables as − postgres=# dt; List of relations Schema | Name | Type | Owner ——–+————+——-+———- public | cricketers | table | postgres public | employee | table | postgres (2 rows) postgres=# Following statement deletes the table named Employee from the database − postgres=# DROP table employee; DROP TABLE Since you have deleted the Employee table, if you retrieve the list of tables again, you can observe only one table in it. postgres=# dt; List of relations Schema | Name | Type | Owner ——–+————+——-+———- public | cricketers | table | postgres (1 row) postgres=# If you try to delete the Employee table again, since you have already deleted it, you will get an error saying “table does not exist” as shown below − postgres=# DROP table employee; ERROR: table “employee” does not exist postgres=# To resolve this, you can use the IF EXISTS clause along with the DELTE statement. This removes the table if it exists else skips the DLETE operation. postgres=# DROP table IF EXISTS employee; NOTICE: table “employee” does not exist, skipping DROP TABLE postgres=# Removing an entire table using Python You can drop a table whenever you need to, using the DROP statement. But you need to be very careful while deleting any existing table because the data lost will not be recovered after deleting a table. import psycopg2 #establishing the connection conn = psycopg2.connect( database=”mydb”, user=”postgres”, password=”password”, host=”127.0.0.1”, port= ”5432” ) #Setting auto commit false conn.autocommit = True #Creating a cursor object using the cursor() method cursor = conn.cursor() #Doping EMPLOYEE table if already exists cursor.execute(“DROP TABLE emp”) print(“Table dropped… “) #Commit your changes in the database conn.commit() #Closing the connection conn.close() Output #Table dropped… Print Page Previous Next Advertisements ”;