Category: python Data Access

Python SQLite – Delete Data ”; Previous Next To delete records from a SQLite table, you need to use the DELETE FROM statement. To remove specific records, you need to use WHERE clause along with it. To update specific rows, you need to use the WHERE clause along with it. Syntax Following is the syntax of the DELETE query in SQLite − DELETE FROM table_name [WHERE Clause] Example Assume we have created a table with name CRICKETERS using the following query − sqlite> CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); sqlite> And if we have inserted 5 records in to it using INSERT statements as − sqlite> insert into CRICKETERS values(”Shikhar”, ”Dhawan”, 33, ”Delhi”, ”India”); sqlite> insert into CRICKETERS values(”Jonathan”, ”Trott”, 38, ”CapeTown”, ”SouthAfrica”); sqlite> insert into CRICKETERS values(”Kumara”, ”Sangakkara”, 41, ”Matale”, ”Srilanka”); sqlite> insert into CRICKETERS values(”Virat”, ”Kohli”, 30, ”Delhi”, ”India”); sqlite> insert into CRICKETERS values(”Rohit”, ”Sharma”, 32, ”Nagpur”, ”India”); sqlite> Following statement deletes the record of the cricketer whose last name is ”Sangakkara”. sqlite> DELETE FROM CRICKETERS WHERE LAST_NAME = ”Sangakkara”; sqlite> If you retrieve the contents of the table using the SELECT statement, you can see only 4 records since we have deleted one. sqlite> SELECT * FROM CRICKETERS; First_Name Last_Name Age Place_Of_B Country ———- ———- —- ———- ————- Shikhar Dhawan 46 Delhi India Jonathan Trott 39 CapeTown SouthAfrica Virat Kohli 31 Delhi India Rohit Sharma 33 Nagpur India sqlite> If you execute the DELETE FROM statement without the WHERE clause, all the records from the specified table will be deleted. sqlite> DELETE FROM CRICKETERS; sqlite> 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 − sqlite> SELECT * FROM CRICKETERS; sqlite> Deleting data using python To add records to an existing table in SQLite database − Import sqlite3 package. Create a connection object using the connect() method by passing the name of the database as a parameter to it. The cursor() method returns a cursor object using which you can communicate with SQLite3 . Create a cursor object by invoking the cursor() object on the (above created) Connection object. Then, invoke the execute() method on the cursor object, by passing an DELETE statement as a parameter to it. Example Following python example deletes the records from EMPLOYEE table with age value greater than 25. import sqlite3 #Connecting to sqlite conn = sqlite3.connect(”example.db”) #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), (”Vinay”, ”Battacharya”, 21, ”M”, 6000.0), (”Sharukh”, ”Sheik”, 26, ”M”, 8300.0), (”Sarmista”, ”Sharma”, 26, ”F”, 10000.0), (”Tripthi”, ”Mishra”, 24, ”F”, 6000.0)] Contents of the table after delete operation [(”Vinay”, ”Battacharya”, 21, ”M”, 6000.0), (”Tripthi”, ”Mishra”, 24, ”F”, 6000.0)] Print Page Previous Next Advertisements ”;

Python MongoDB – Update ”; Previous Next You can update the contents of an existing documents using the update() method or save() method. The update method modifies the existing document whereas the save method replaces the existing document with the new one. Syntax Following is the syntax of the update() and save() methods of MangoDB − >db.COLLECTION_NAME.update(SELECTION_CRITERIA, UPDATED_DATA) Or, db.COLLECTION_NAME.save({_id:ObjectId(),NEW_DATA}) Example Assume we have created a collection in a database and inserted 3 records in it as shown below − > use testdatabase switched to db testdatabase > 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” : “1001”, “name” : “Ram”, “age” : “26”, “city” : “Hyderabad” }, { “_id” : “1002”, “name” : “Rahim”, “age” : 27, “city” : “Bangalore” }, { “_id” : “1003”, “name” : “Robert”, “age” : 28, “city” : “Mumbai” } ] > db.createCollection(“sample”) { “ok” : 1 } > db.sample.insert(data) Following method updates the city value of the document with id 1002. > db.sample.update({“_id”:”1002″},{“$set”:{“city”:”Visakhapatnam”}}) WriteResult({ “nMatched” : 1, “nUpserted” : 0, “nModified” : 1 }) > db.sample.find() { “_id” : “1001”, “name” : “Ram”, “age” : “26”, “city” : “Hyderabad” } { “_id” : “1002”, “name” : “Rahim”, “age” : 27, “city” : “Visakhapatnam” } { “_id” : “1003”, “name” : “Robert”, “age” : 28, “city” : “Mumbai” } Similarly you can replace the document with new data by saving it with same id using the save() method. > db.sample.save( { “_id” : “1001”, “name” : “Ram”, “age” : “26”, “city” : “Vijayawada” } ) WriteResult({ “nMatched” : 1, “nUpserted” : 0, “nModified” : 1 }) > db.sample.find() { “_id” : “1001”, “name” : “Ram”, “age” : “26”, “city” : “Vijayawada” } { “_id” : “1002”, “name” : “Rahim”, “age” : 27, “city” : “Visakhapatnam” } { “_id” : “1003”, “name” : “Robert”, “age” : 28, “city” : “Mumbai” } Updating documents using python Similar to find_one() method which retrieves single document, the update_one() method of pymongo updates a single document. This method accepts a query specifying which document to update and the update operation. Example Following python example updates the location value of a document in a collection. from pymongo import MongoClient #Creating a pymongo client client = MongoClient(”localhost”, 27017) #Getting the database instance db = client[”myDB”] #Creating a collection coll = db[”example”] #Inserting document into a collection data = [ {“_id”: “101”, “name”: “Ram”, “age”: “26”, “city”: “Hyderabad”}, {“_id”: “102”, “name”: “Rahim”, “age”: “27”, “city”: “Bangalore”}, {“_id”: “103”, “name”: “Robert”, “age”: “28”, “city”: “Mumbai”} ] res = coll.insert_many(data) print(“Data inserted ……”) #Retrieving all the records using the find() method print(“Documents in the collection: “) for doc1 in coll.find(): print(doc1) coll.update_one({“_id”:”102″},{“$set”:{“city”:”Visakhapatnam”}}) #Retrieving all the records using the find() method print(“Documents in the collection after update operation: “) for doc2 in coll.find(): print(doc2) Output Data inserted …… Documents in the collection: {”_id”: ”101”, ”name”: ”Ram”, ”age”: ”26”, ”city”: ”Hyderabad”} {”_id”: ”102”, ”name”: ”Rahim”, ”age”: ”27”, ”city”: ”Bangalore”} {”_id”: ”103”, ”name”: ”Robert”, ”age”: ”28”, ”city”: ”Mumbai”} Documents in the collection after update operation: {”_id”: ”101”, ”name”: ”Ram”, ”age”: ”26”, ”city”: ”Hyderabad”} {”_id”: ”102”, ”name”: ”Rahim”, ”age”: ”27”, ”city”: ”Visakhapatnam”} {”_id”: ”103”, ”name”: ”Robert”, ”age”: ”28”, ”city”: ”Mumbai”} Similarly, the update_many() method of pymongo updates all the documents that satisfies the specified condition. Example Following example updates the location value in all the documents in a collection (empty condition) − from pymongo import MongoClient #Creating a pymongo client client = MongoClient(”localhost”, 27017) #Getting the database instance db = client[”myDB”] #Creating a collection coll = db[”example”] #Inserting document into a collection data = [ {“_id”: “101”, “name”: “Ram”, “age”: “26”, “city”: “Hyderabad”}, {“_id”: “102”, “name”: “Rahim”, “age”: “27”, “city”: “Bangalore”}, {“_id”: “103”, “name”: “Robert”, “age”: “28”, “city”: “Mumbai”} ] res = coll.insert_many(data) print(“Data inserted ……”) #Retrieving all the records using the find() method print(“Documents in the collection: “) for doc1 in coll.find(): print(doc1) coll.update_many({},{“$set”:{“city”:”Visakhapatnam”}}) #Retrieving all the records using the find() method print(“Documents in the collection after update operation: “) for doc2 in coll.find(): print(doc2) Output Data inserted …… Documents in the collection: {”_id”: ”101”, ”name”: ”Ram”, ”age”: ”26”, ”city”: ”Hyderabad”} {”_id”: ”102”, ”name”: ”Rahim”, ”age”: ”27”, ”city”: ”Bangalore”} {”_id”: ”103”, ”name”: ”Robert”, ”age”: ”28”, ”city”: ”Mumbai”} Documents in the collection after update operation: {”_id”: ”101”, ”name”: ”Ram”, ”age”: ”26”, ”city”: ”Visakhapatnam”} {”_id”: ”102”, ”name”: ”Rahim”, ”age”: ”27”, ”city”: ”Visakhapatnam”} {”_id”: ”103”, ”name”: ”Robert”, ”age”: ”28”, ”city”: ”Visakhapatnam”} Print Page Previous Next Advertisements ”;

Python SQLite – Limit ”; Previous Next While fetching records if you want to limit them by a particular number, you can do so, using the LIMIT clause of SQLite. Syntax Following is the syntax of the LIMIT clause in SQLite − 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 − sqlite> CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); sqlite> And if we have inserted 5 records in to it using INSERT statements as − sqlite> insert into CRICKETERS values(”Shikhar”, ”Dhawan”, 33, ”Delhi”, ”India”); sqlite> insert into CRICKETERS values(”Jonathan”, ”Trott”, 38, ”CapeTown”, ”SouthAfrica”); sqlite> insert into CRICKETERS values(”Kumara”, ”Sangakkara”, 41, ”Matale”, ”Srilanka”); sqlite> insert into CRICKETERS values(”Virat”, ”Kohli”, 30, ”Delhi”, ”India”); sqlite> insert into CRICKETERS values(”Rohit”, ”Sharma”, 32, ”Nagpur”, ”India”); sqlite> Following statement retrieves the first 3 records of the Cricketers table using the LIMIT clause − sqlite> SELECT * FROM CRICKETERS LIMIT 3; First_Name Last_Name Age Place_Of_B Country ———- ———- —- ———- ————- Shikhar Dhawan 33 Delhi India Jonathan Trott 38 CapeTown SouthAfrica Kumara Sangakkara 41 Matale Srilanka sqlite> If you need to limit the records starting from nth record (not 1st), you can do so, using OFFSET along with LIMIT. sqlite> SELECT * FROM CRICKETERS LIMIT 3 OFFSET 2; First_Name Last_Name Age Place_Of_B Country ———- ———- —- ———- ————- Kumara Sangakkara 41 Matale Srilanka Virat Kohli 30 Delhi India Rohit Sharma 32 Nagpur India sqlite> LIMIT clause using Python If you Invoke the execute() method on the cursor object by passing the SELECT query along with the LIMIT clause, you can retrieve required number of records. Example Following python example retrieves the first two records of the EMPLOYEE table using the LIMIT clause. import sqlite3 #Connecting to sqlite conn = sqlite3.connect(”example.db”) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Retrieving single row sql = ”””SELECT * from EMPLOYEE LIMIT 3””” #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 [(”Ramya”, ”Rama priya”, 27, ”F”, 9000.0), (”Vinay”, ”Battacharya”, 20, ”M”, 6000.0), (”Sharukh”, ”Sheik”, 25, ”M”, 8300.0)] Print Page Previous Next Advertisements ”;

Python PostgreSQL – Where Clause ”; Previous Next While performing SELECT, UPDATE or, DELETE operations, you can specify condition to filter the records using the WHERE clause. The operation will be performed on the records which satisfies the given condition. Syntax Following is the syntax of the WHERE clause in PostgreSQL − SELECT column1, column2, columnN FROM table_name WHERE [search_condition] You can specify a search_condition using comparison or logical operators. like >, <, =, LIKE, NOT, etc. The following examples would make this concept clear. 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 records whose age is greater than 35 − postgres=# SELECT * FROM CRICKETERS WHERE AGE > 35; first_name | last_name | age | place_of_birth | country ————+————+—–+—————-+————- Jonathan | Trott | 38 | CapeTown | SouthAfrica Kumara | Sangakkara | 41 | Matale | Srilanka (2 rows) postgres=# Where clause using python To fetch specific records from a table using the python program execute the SELECT statement with WHERE clause, by passing it as a parameter to the execute() method. Example Following python example demonstrates the usage of WHERE command using python. 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”) sql = ”””CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT )””” cursor.execute(sql) #Populating the table insert_stmt = “INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (%s, %s, %s, %s, %s)” data = [(”Krishna”, ”Sharma”, 19, ”M”, 2000), (”Raj”, ”Kandukuri”, 20, ”M”, 7000), (”Ramya”, ”Ramapriya”, 25, ”M”, 5000), (”Mac”, ”Mohan”, 26, ”M”, 2000)] cursor.executemany(insert_stmt, data) #Retrieving specific records using the where clause cursor.execute(“SELECT * from EMPLOYEE WHERE AGE <23″) print(cursor.fetchall()) #Commit your changes in the database conn.commit() #Closing the connection conn.close() Output [(”Krishna”, ”Sharma”, 19, ”M”, 2000.0), (”Raj”, ”Kandukuri”, 20, ”M”, 7000.0)] Print Page Previous Next Advertisements ”;

Python SQLite – Update Table ”; Previous Next UPDATE Operation on any database implies modifying the values of one or more records of a table, which are already available in the database. You can update the values of existing records in SQLite using the UPDATE statement. To update specific rows, you need to use the WHERE clause along with it. Syntax Following is the syntax of the UPDATE statement in SQLite − UPDATE table_name SET column1 = value1, column2 = value2…., columnN = valueN WHERE [condition]; Example Assume we have created a table with name CRICKETERS using the following query − sqlite> CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); sqlite> And if we have inserted 5 records in to it using INSERT statements as − sqlite> insert into CRICKETERS values(”Shikhar”, ”Dhawan”, 33, ”Delhi”, ”India”); sqlite> insert into CRICKETERS values(”Jonathan”, ”Trott”, 38, ”CapeTown”, ”SouthAfrica”); sqlite> insert into CRICKETERS values(”Kumara”, ”Sangakkara”, 41, ”Matale”, ”Srilanka”); sqlite> insert into CRICKETERS values(”Virat”, ”Kohli”, 30, ”Delhi”, ”India”); sqlite> insert into CRICKETERS values(”Rohit”, ”Sharma”, 32, ”Nagpur”, ”India”); sqlite> Following Statement modifies the age of the cricketer, whose first name is Shikhar − sqlite> UPDATE CRICKETERS SET AGE = 45 WHERE FIRST_NAME = ”Shikhar” ; sqlite> If you retrieve the record whose FIRST_NAME is Shikhar you observe that the age value has been changed to 45 − sqlite> SELECT * FROM CRICKETERS WHERE FIRST_NAME = ”Shikhar”; First_Name Last_Name Age Place_Of_B Country ———- ———- —- ———- ————- Shikhar Dhawan 45 Delhi India sqlite> If you haven’t used the WHERE clause values of all the records will be updated. Following UPDATE statement increases the age of all the records in the CRICKETERS table by 1 − sqlite> UPDATE CRICKETERS SET AGE = AGE+1; sqlite> If you retrieve the contents of the table using SELECT command, you can see the updated values as − sqlite> SELECT * FROM CRICKETERS; First_Name Last_Name Age Place_Of_B Country ———- ———- —- ———- ————- Shikhar Dhawan 46 Delhi India Jonathan Trott 39 CapeTown SouthAfrica Kumara Sangakkara 42 Matale Srilanka Virat Kohli 31 Delhi India Rohit Sharma 33 Nagpur India sqlite> Updating existing records using python To add records to an existing table in SQLite database − Import sqlite3 package. Create a connection object using the connect() method by passing the name of the database as a parameter to it. The cursor() method returns a cursor object using which you can communicate with SQLite3 . Create a cursor object by invoking the cursor() object on the (above created) Connection object. Then, invoke the execute() method on the cursor object, by passing an UPDATE statement as a parameter to it. Example Following Python example, creates a table with name EMPLOYEE, inserts 5 records into it and, increases the age of all the male employees by 1 − import sqlite3 #Connecting to sqlite conn = sqlite3.connect(”example.db”) #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) #Inserting data cursor.execute(”””INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Ramya”, ”Rama priya”, 27, ”F”, 9000), (”Vinay”, ”Battacharya”, 20, ”M”, 6000), (”Sharukh”, ”Sheik”, 25, ”M”, 8300), (”Sarmista”, ”Sharma”, 26, ”F”, 10000), (”Tripthi”, ”Mishra”, 24, ”F”, 6000)”””) conn.commit() #Fetching all the rows before the update print(“Contents of the Employee table: “) cursor.execute(”””SELECT * from EMPLOYEE”””) print(cursor.fetchall()) #Updating the records sql = ”””UPDATE EMPLOYEE SET AGE=AGE+1 WHERE SEX = ”M” ””” cursor.execute(sql) print(“Table updated…… “) #Fetching all the rows after the update print(“Contents of the Employee table after the update 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 Employee table: [(”Ramya”, ”Rama priya”, 27, ”F”, 9000.0), (”Vinay”, ”Battacharya”, 20, ”M”, 6000.0), (”Sharukh”, ”Sheik”, 25, ”M”, 8300.0), (”Sarmista”, ”Sharma”, 26, ”F”, 10000.0), (”Tripthi”, ”Mishra”, 24, ”F”, 6000.0)] Table updated…… Contents of the Employee table after the update operation: [(”Ramya”, ”Rama priya”, 27, ”F”, 9000.0), (”Vinay”, ”Battacharya”, 21, ”M”, 6000.0), (”Sharukh”, ”Sheik”, 26, ”M”, 8300.0), (”Sarmista”, ”Sharma”, 26, ”F”, 10000.0), (”Tripthi”, ”Mishra”, 24, ”F”, 6000.0)] Print Page Previous Next Advertisements ”;

Python SQLite – Where Clause ”; Previous Next If you want to fetch, delete or, update particular rows of a table in SQLite, you need to use the where clause to specify condition to filter the rows of the table for the operation. For example, if you have a SELECT statement with where clause, only the rows which satisfies the specified condition will be retrieved. Syntax Following is the syntax of the WHERE clause in SQLite − SELECT column1, column2, columnN FROM table_name WHERE [search_condition] You can specify a search_condition using comparison or logical operators. like >, <, =, LIKE, NOT, etc. The following examples would make this concept clear. Example Assume we have created a table with name CRICKETERS using the following query − sqlite> CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); sqlite> And if we have inserted 5 records in to it using INSERT statements as − sqlite> insert into CRICKETERS values(”Shikhar”, ”Dhawan”, 33, ”Delhi”, ”India”); sqlite> insert into CRICKETERS values(”Jonathan”, ”Trott”, 38, ”CapeTown”, ”SouthAfrica”); sqlite> insert into CRICKETERS values(”Kumara”, ”Sangakkara”, 41, ”Matale”, ”Srilanka”); sqlite> insert into CRICKETERS values(”Virat”, ”Kohli”, 30, ”Delhi”, ”India”); sqlite> insert into CRICKETERS values(”Rohit”, ”Sharma”, 32, ”Nagpur”, ”India”); sqlite> Following SELECT statement retrieves the records whose age is greater than 35 − sqlite> SELECT * FROM CRICKETERS WHERE AGE > 35; First_Name Last_Name Age Place_Of_B Country ———- ———- —- ———- ————- Jonathan Trott 38 CapeTown SouthAfrica Kumara Sangakkara 41 Matale Srilanka sqlite> Where clause using python The Cursor object/class contains all the methods to execute queries and fetch data, etc. The cursor method of the connection class returns a cursor object. Therefore, to create a table in SQLite database using python − Establish connection with a database using the connect() method. Create a cursor object by invoking the cursor() method on the above created connection object. Now execute the CREATE TABLE statement using the execute() method of the Cursor class. Example Following example creates a table named Employee and populates it. Then using the where clause it retrieves the records with age value less than 23. import sqlite3 #Connecting to sqlite conn = sqlite3.connect(”example.db”) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Doping EMPLOYEE table if already exists. cursor.execute(“DROP TABLE IF EXISTS EMPLOYEE”) sql = ”””CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT )””” cursor.execute(sql) #Populating the table cursor.execute(”””INSERT INTO EMPLOYEE( FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Ramya”, ”Rama priya”, 27, ”F”, 9000)”””) cursor.execute(”””INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Vinay”, ”Battacharya”, 20, ”M”, 6000)”””) cursor.execute(”””INSERT INTO EMPLOYEE( FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Sharukh”, ”Sheik”, 25, ”M”, 8300)”””) cursor.execute(”””INSERT INTO EMPLOYEE( FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Sarmista”, ”Sharma”, 26, ”F”, 10000)”””) cursor.execute(”””INSERT INTO EMPLOYEE( FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Tripthi”, ”Mishra”, 24, ”F”, 6000)”””) #Retrieving specific records using the where clause cursor.execute(“SELECT * from EMPLOYEE WHERE AGE <23″) print(cursor.fetchall()) #Commit your changes in the database conn.commit() #Closing the connection conn.close() Output [(”Vinay”, ”Battacharya”, 20, ”M”, 6000.0)] Print Page Previous Next Advertisements ”;

Python PostgreSQL – Update Table ”; Previous Next You can modify the contents of existing records of a table in PostgreSQL using the UPDATE statement. To update specific rows, you need to use the WHERE clause along with it. Syntax Following is the syntax of the UPDATE statement in PostgreSQL − UPDATE table_name SET column1 = value1, column2 = value2…., columnN = valueN WHERE [condition]; 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 modifies the age of the cricketer, whose first name is Shikhar − postgres=# UPDATE CRICKETERS SET AGE = 45 WHERE FIRST_NAME = ”Shikhar” ; UPDATE 1 postgres=# If you retrieve the record whose FIRST_NAME is Shikhar you observe that the age value has been changed to 45 − postgres=# SELECT * FROM CRICKETERS WHERE FIRST_NAME = ”Shikhar”; first_name | last_name | age | place_of_birth | country ————+———–+—–+—————-+——— Shikhar | Dhawan | 45 | Delhi | India (1 row) postgres=# If you haven’t used the WHERE clause, values of all the records will be updated. Following UPDATE statement increases the age of all the records in the CRICKETERS table by 1 − postgres=# UPDATE CRICKETERS SET AGE = AGE+1; UPDATE 5 If you retrieve the contents of the table using SELECT command, you can see the updated values as − postgres=# SELECT * FROM CRICKETERS; first_name | last_name | age | place_of_birth | country ————+————+—–+—————-+————- Jonathan | Trott | 39 | CapeTown | SouthAfrica Kumara | Sangakkara | 42 | Matale | Srilanka Virat | Kohli | 31 | Delhi | India Rohit | Sharma | 33 | Nagpur | India Shikhar | Dhawan | 46 | Delhi | India (5 rows) Updating records 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 updates the contents of the Employee table and retrieves the results − 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() #Fetching all the rows before the update print(“Contents of the Employee table: “) sql = ”””SELECT * from EMPLOYEE””” cursor.execute(sql) print(cursor.fetchall()) #Updating the records sql = “UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = ”M”” cursor.execute(sql) print(“Table updated…… “) #Fetching all the rows after the update print(“Contents of the Employee table after the update operation: “) sql = ”””SELECT * from EMPLOYEE””” cursor.execute(sql) print(cursor.fetchall()) #Commit your changes in the database conn.commit() #Closing the connection conn.close() Output Contents of the Employee table: [(”Ramya”, ”Rama priya”, 27, ”F”, 9000.0), (”Vinay”, ”Battacharya”, 20, ”M”, 6000.0), (”Sharukh”, ”Sheik”, 25, ”M”, 8300.0), (”Sarmista”, ”Sharma”, 26, ”F”, 10000.0), (”Tripthi”, ”Mishra”, 24, ”F”, 6000.0)] Table updated…… Contents of the Employee table after the update operation: [(”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)] Print Page Previous Next Advertisements ”;

Python MySQL – Delete Data ”; Previous Next To delete records from a MySQL table, you need to use the DELETE FROM statement. To remove specific records, you need to use WHERE clause along with it. Syntax Following is the syntax of the DELETE query in MYSQL − DELETE FROM table_name [WHERE Clause] Example Assume we have created a table in MySQL with name EMPLOYEES as − mysql> CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT ); Query OK, 0 rows affected (0.36 sec) And if we have inserted 4 records in to it using INSERT statements as − mysql> INSERT INTO EMPLOYEE VALUES (”Krishna”, ”Sharma”, 19, ”M”, 2000), (”Raj”, ”Kandukuri”, 20, ”M”, 7000), (”Ramya”, ”Ramapriya”, 25, ”F”, 5000), (”Mac”, ”Mohan”, 26, ”M”, 2000); Following MySQL statement deletes the record of the employee with FIRST_NAME ”Mac”. mysql> DELETE FROM EMPLOYEE WHERE FIRST_NAME = ”Mac”; Query OK, 1 row affected (0.12 sec) If you retrieve the contents of the table, you can see only 3 records since we have deleted one. mysql> select * from EMPLOYEE; +————+———–+——+——+——–+ | FIRST_NAME | LAST_NAME | AGE | SEX | INCOME | +————+———–+——+——+——–+ | Krishna | Sharma | 20 | M | 2000 | | Raj | Kandukuri | 21 | M | 7000 | | Ramya | Ramapriya | 25 | F | 5000 | +————+———–+——+——+——–+ 3 rows in set (0.00 sec) If you execute the DELETE statement without the WHERE clause all the records from the specified table will be deleted. mysql> DELETE FROM EMPLOYEE; Query OK, 3 rows affected (0.09 sec) If you retrieve the contents of the table, you will get an empty set as shown below − mysql> select * from EMPLOYEE; Empty set (0.00 sec) Removing records of a table using python DELETE operation is required when you want to delete some records from your database. To delete the records in a table − import mysql.connector package. Create a connection object using the mysql.connector.connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it. Create a cursor object by invoking the cursor() method on the connection object created above. Then, execute the DELETE statement by passing it as a parameter to the execute() method. Example Following program deletes all the records from EMPLOYEE whose AGE is more than 20 − import mysql.connector #establishing the connection conn = mysql.connector.connect( user=”root”, password=”password”, host=”127.0.0.1”, database=”mydb”) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Retrieving single row print(“Contents of the table: “) cursor.execute(“SELECT * from EMPLOYEE”) print(cursor.fetchall()) #Preparing the query to delete records sql = “DELETE FROM EMPLOYEE WHERE AGE > ”%d”” % (25) try: # Execute the SQL command cursor.execute(sql) # Commit your changes in the database conn.commit() except: # Roll back in case there is any error conn.rollback() #Retrieving data print(“Contents of the table after delete operation “) cursor.execute(“SELECT * from EMPLOYEE”) print(cursor.fetchall()) #Closing the connection conn.close() Output Contents of the table: [(”Krishna”, ”Sharma”, 22, ”M”, 2000.0), (”Raj”, ”Kandukuri”, 23, ”M”, 7000.0), (”Ramya”, ”Ramapriya”, 26, ”F”, 5000.0), (”Mac”, ”Mohan”, 20, ”M”, 2000.0), (”Ramya”, ”Rama priya”, 27, ”F”, 9000.0)] Contents of the table after delete operation: [(”Krishna”, ”Sharma”, 22, ”M”, 2000.0), (”Raj”, ”Kandukuri”, 23, ”M”, 7000.0), (”Mac”, ”Mohan”, 20, ”M”, 2000.0)] Print Page Previous Next Advertisements ”;

Python SQLite – Insert Data ”; Previous Next You can add new rows to an existing table of SQLite using the INSERT INTO statement. In this, you need to specify the name of the table, column names, and values (in the same order as column names). Syntax Following is the recommended syntax of the INSERT statement − INSERT INTO TABLE_NAME (column1, column2, column3,…columnN) VALUES (value1, value2, value3,…valueN); Where, column1, column2, column3,.. are the names of the columns of a table and value1, value2, value3,… are the values you need to insert into the table. Example Assume we have created a table with name CRICKETERS using the CREATE TABLE statement as shown below − sqlite> CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); sqlite> Following PostgreSQL statement inserts a row in the above created table. sqlite> insert into CRICKETERS (First_Name, Last_Name, Age, Place_Of_Birth, Country) values (”Shikhar”, ”Dhawan”, 33, ”Delhi”, ”India”); sqlite> While inserting records using the INSERT INTO statement, if you skip any columns names, this record will be inserted leaving empty spaces at columns which you have skipped. sqlite> insert into CRICKETERS (First_Name, Last_Name, Country) values (”Jonathan”, ”Trott”, ”SouthAfrica”); sqlite> You can also insert records into a table without specifying the column names, if the order of values you pass is same as their respective column names in the table. sqlite> insert into CRICKETERS values(”Kumara”, ”Sangakkara”, 41, ”Matale”, ”Srilanka”); sqlite> insert into CRICKETERS values(”Virat”, ”Kohli”, 30, ”Delhi”, ”India”); sqlite> insert into CRICKETERS values(”Rohit”, ”Sharma”, 32, ”Nagpur”, ”India”); sqlite> After inserting the records into a table you can verify its contents using the SELECT statement as shown below − sqlite> select * from cricketers; Shikhar | Dhawan | 33 | Delhi | India Jonathan | Trott | | | SouthAfrica Kumara | Sangakkara | 41 | Matale | Srilanka Virat | Kohli | 30 | Delhi | India Rohit | Sharma | 32 | Nagpur | India sqlite> Inserting data using python To add records to an existing table in SQLite database − Import sqlite3 package. Create a connection object using the connect() method by passing the name of the database as a parameter to it. The cursor() method returns a cursor object using which you can communicate with SQLite3. Create a cursor object by invoking the cursor() object on the (above created) Connection object. Then, invoke the execute() method on the cursor object, by passing an INSERT statement as a parameter to it. Example Following python example inserts records into to a table named EMPLOYEE − import sqlite3 #Connecting to sqlite conn = sqlite3.connect(”example.db”) #Creating a cursor object using the cursor() method cursor = conn.cursor() # Preparing SQL queries to INSERT a record into the database. cursor.execute(”””INSERT INTO EMPLOYEE( FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Ramya”, ”Rama Priya”, 27, ”F”, 9000)”””) cursor.execute(”””INSERT INTO EMPLOYEE( FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Vinay”, ”Battacharya”, 20, ”M”, 6000)”””) cursor.execute(”””INSERT INTO EMPLOYEE( FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Sharukh”, ”Sheik”, 25, ”M”, 8300)”””) cursor.execute(”””INSERT INTO EMPLOYEE( FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Sarmista”, ”Sharma”, 26, ”F”, 10000)”””) cursor.execute(”””INSERT INTO EMPLOYEE( FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Tripthi”, ”Mishra”, 24, ”F”, 6000)”””) # Commit your changes in the database conn.commit() print(“Records inserted……..”) # Closing the connection conn.close() Output Records inserted…….. Print Page Previous Next Advertisements ”;

Python MySQL – Limit ”; Previous Next While fetching records if you want to limit them by a particular number, you can do so, using the LIMIT clause of MYSQL. Example Assume we have created a table in MySQL with name EMPLOYEES as − mysql> CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT ); Query OK, 0 rows affected (0.36 sec) And if we have inserted 4 records in to it using INSERT statements as − mysql> INSERT INTO EMPLOYEE VALUES (”Krishna”, ”Sharma”, 19, ”M”, 2000), (”Raj”, ”Kandukuri”, 20, ”M”, 7000), (”Ramya”, ”Ramapriya”, 25, ”F”, 5000), (”Mac”, ”Mohan”, 26, ”M”, 2000); Following SQL statement retrieves first two records of the Employee table using the LIMIT clause. SELECT * FROM EMPLOYEE LIMIT 2; +————+———–+——+——+——–+ | FIRST_NAME | LAST_NAME | AGE | SEX | INCOME | +————+———–+——+——+——–+ | Krishna | Sharma | 19 | M | 2000 | | Raj | Kandukuri | 20 | M | 7000 | +————+———–+——+——+——–+ 2 rows in set (0.00 sec) Limit clause using python If you invoke the execute() method on the cursor object by passing the SELECT query along with the LIMIT clause, you can retrieve required number of records. To drop a table from a MYSQL database using python invoke the execute() method on the cursor object and pass the drop statement as a parameter to it. Example Following python example creates and populates a table with name EMPLOYEE and, using the LIMIT clause it fetches the first two records of it. import mysql.connector #establishing the connection conn = mysql.connector.connect( user=”root”, password=”password”, host=”127.0.0.1”, database=”mydb”) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Retrieving single row sql = ”””SELECT * from EMPLOYEE LIMIT 2””” #Executing the query cursor.execute(sql) #Fetching the data result = cursor.fetchall(); print(result) #Closing the connection conn.close() Output [(”Krishna”, ”Sharma”, 26, ”M”, 2000.0), (”Raj”, ”Kandukuri”, 20, ”M”, 7000.0)] LIMIT with OFFSET If you need to limit the records starting from nth record (not 1st), you can do so, using OFFSET along with LIMIT. import mysql.connector #establishing the connection conn = mysql.connector.connect( user=”root”, password=”password”, host=”127.0.0.1”, database=”mydb”) #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) #Closing the connection conn.close() Output [(”Ramya”, ”Ramapriya”, 29, ”F”, 5000.0), (”Mac”, ”Mohan”, 26, ”M”, 2000.0)] Print Page Previous Next Advertisements ”;