Category: python Data Access

Python Data Access – Useful Resources ”; Previous Next The following resources contain additional information on Python Data Access. Please use them to get more in-depth knowledge on this topic. Useful Video Courses MS Access 2016 Online Training Most Popular 54 Lectures 5 hours Tutorialspoint More Detail Python Data Types Course – Fundamentals of Programming in Python 11 Lectures 1 hours Frahaan Hussain More Detail Access Masterclass – Level 1 & 2 – Beginner to Intermediate 92 Lectures 6.5 hours Ermin Dedic More Detail Vlans, Access-list & NAT for Cisco CCNA! 14 Lectures 2.5 hours Lazaro Diaz More Detail Python Data Analysis with Pandas 40 Lectures 2 hours Fanuel Mapuwei More Detail Microsoft Office Access 2016: Part 1 (Beginner) 47 Lectures 2 hours Sonic Performance More Detail Print Page Previous Next Advertisements ”;

Python MongoDB – Drop Collection ”; Previous Next You can delete collections using drop() method of MongoDB. Syntax Following is the syntax of drop() method − db.COLLECTION_NAME.drop() Example Following example drops collection with name sample − > show collections myColl sample > db.sample.drop() true > show collections myColl Dropping collection using python You can drop/delete a collection from the current database by invoking drop() method. Example from pymongo import MongoClient #Creating a pymongo client client = MongoClient(”localhost”, 27017) #Getting the database instance db = client[”example2”] #Creating a collection col1 = db[”collection”] col1.insert_one({“name”: “Ram”, “age”: “26”, “city”: “Hyderabad”}) col2 = db[”coll”] col2.insert_one({“name”: “Rahim”, “age”: “27”, “city”: “Bangalore”}) col3 = db[”myColl”] col3.insert_one({“name”: “Robert”, “age”: “28”, “city”: “Mumbai”}) col4 = db[”data”] col4.insert_one({“name”: “Romeo”, “age”: “25”, “city”: “Pune”}) #List of collections print(“List of collections:”) collections = db.list_collection_names() for coll in collections: print(coll) #Dropping a collection col1.drop() col4.drop() print(“List of collections after dropping two of them: “) #List of collections collections = db.list_collection_names() for coll in collections: print(coll) Output List of collections: coll data collection myColl List of collections after dropping two of them: coll myColl Print Page Previous Next Advertisements ”;

Python MongoDB – Sort ”; Previous Next While retrieving the contents of a collection, you can sort and arrange them in ascending or descending orders using the sort() method. To this method, you can pass the field(s) and the sorting order which is 1 or -1. Where, 1 is for ascending order and -1 is descending order. Syntax Following is the syntax of the sort() method. >db.COLLECTION_NAME.find().sort({KEY:1}) Example Assume we have created a collection and inserted 5 documents into it as shown below − > use testDB switched to db testDB > db.createCollection(“myColl”) { “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 all the documents of the collection which are sorted in ascending order based on age. > db.sample.find().sort({age:1}) { “_id” : “1005”, “name” : “Sarmista”, “age” : 23, “city” : “Delhi” } { “_id” : “1004”, “name” : “Romeo”, “age” : 25, “city” : “Pune” } { “_id” : “1006”, “name” : “Rasajna”, “age” : 26, “city” : “Chennai” } { “_id” : “1002”, “name” : “Rahim”, “age” : 27, “city” : “Bangalore” } { “_id” : “1003”, “name” : “Robert”, “age” : 28, “city” : “Mumbai” } { “_id” : “1001”, “name” : “Ram”, “age” : “26”, “city” : “Hyderabad” } Sorting the documents using python To sort the results of a query in ascending or, descending order pymongo provides the sort() method. To this method, pass a number value representing the number of documents you need in the result. By default, this method sorts the documents in ascending order based on the specified field. If you need to sort in descending order pass -1 along with the field name − coll.find().sort(“age”,-1) Example Following example retrieves all the documents of a collection arranged according to the age values in ascending order − from pymongo import MongoClient #Creating a pymongo client client = MongoClient(”localhost”, 27017) #Getting the database instance db = client[”b_mydb”] #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(“List of documents (sorted in ascending order based on age): “) for doc1 in coll.find().sort(“age”): print(doc1) Output Data inserted …… List of documents (sorted in ascending order based on age): {”_id”: ”1005”, ”name”: ”Sarmista”, ”age”: 23, ”city”: ”Delhi”} {”_id”: ”1004”, ”name”: ”Romeo”, ”age”: 25, ”city”: ”Pune”} {”_id”: ”1006”, ”name”: ”Rasajna”, ”age”: 26, ”city”: ”Chennai”} {”_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 SQLite – Select Data ”; Previous Next You can retrieve data from an SQLite table using the SELCT query. This query/statement returns contents of the specified relation (table) in tabular form and it is called as result-set. Syntax Following is the syntax of the SELECT statement in SQLite − SELECT column1, column2, columnN FROM table_name; 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 query retrieves the values of the columns FIRST_NAME, LAST_NAME and, COUNTRY from the CRICKETERS table. sqlite> SELECT FIRST_NAME, LAST_NAME, COUNTRY FROM CRICKETERS; Shikhar |Dhawan |India Jonathan |Trott |SouthAfrica Kumara |Sangakkara |Srilanka Virat |Kohli |India Rohit |Sharma |India sqlite> As you observe, the SELECT statement of the SQLite database just returns the records of the specified tables. To get a formatted output you need to set the header, and mode using the respective commands before the SELECT statement as shown below − sqlite> .header on sqlite> .mode column sqlite> SELECT FIRST_NAME, LAST_NAME, COUNTRY FROM CRICKETERS; First_Name Last_Name Country ———- ——————– ———- Shikhar Dhawan India Jonathan Trott SouthAfric Kumara Sangakkara Srilanka Virat Kohli India Rohit Sharma India sqlite> If you want to retrieve all the columns of each record, you need to replace the names of the columns with “*” as shown below − sqlite> .header on sqlite> .mode column sqlite> SELECT * FROM CRICKETERS; First_Name Last_Name Age Place_Of_Birth Country ———- ———- ———- ————– ———- Shikhar Dhawan 33 Delhi India Jonathan Trott 38 CapeTown SouthAfric Kumara Sangakkara 41 Matale Srilanka Virat Kohli 30 Delhi India Rohit Sharma 32 Nagpur India sqlite> In SQLite by default the width of the columns is 10 values beyond this width are chopped (observe the country column of 2nd row in above table). You can set the width of each column to required value using the .width command, before retrieving the contents of a table as shown below − sqlite> .width 10, 10, 4, 10, 13 sqlite> SELECT * FROM CRICKETERS; First_Name Last_Name Age Place_Of_B Country ———- ———- —- ———- ————- Shikhar Dhawan 33 Delhi India Jonathan Trott 38 CapeTown SouthAfrica Kumara Sangakkara 41 Matale Srilanka Virat Kohli 30 Delhi India Rohit Sharma 32 Nagpur India sqlite> Retrieving data using python READ Operation on any database means to fetch some useful information from the database. You can fetch data from MYSQL using the fetch() method provided by the sqlite python module. The sqlite3.Cursor class provides three methods namely fetchall(), fetchmany() and, fetchone() where, The fetchall() method retrieves all the rows in the result set of a query and returns them as list of tuples. (If we execute this after retrieving few rows it returns the remaining ones). The fetchone() method fetches the next row in the result of a query and returns it as a tuple. The fetchmany() method is similar to the fetchone() but, it retrieves the next set of rows in the result set of a query, instead of a single row. Note − A result set is an object that is returned when a cursor object is used to query a table. Example Following example fetches all the rows of the EMPLOYEE table using the SELECT query and from the obtained result set initially, we are retrieving the first row using the fetchone() method and then fetching the remaining rows using the fetchall() method. Following Python program shows how to fetch and display records from the COMPANY table created in the above example. import sqlite3 #Connecting to sqlite conn = sqlite3.connect(”example.db”) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Retrieving data cursor.execute(”””SELECT * from EMPLOYEE”””) #Fetching 1st row from the table result = cursor.fetchone(); print(result) #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) [(”Vinay”, ”Battacharya”, 20, ”M”, 6000.0), (”Sharukh”, ”Sheik”, 25, ”M”, 8300.0), (”Sarmista”, ”Sharma”, 26, ”F”, 10000.0), (”Tripthi”, ”Mishra”, 24, ”F”, 6000.0) ] Print Page Previous Next Advertisements ”;

Python SQLite – Drop Table ”; Previous Next You can remove an entire table using the DROP TABLE statement. You just need to specify the name of the table you need to delete. 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 − sqlite> CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); sqlite> CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT ); sqlite> Now if you verify the list of tables using the .tables command, you can see the above created tables in it ( list) as − sqlite> .tables CRICKETERS EMPLOYEE sqlite> Following statement deletes the table named Employee from the database − sqlite> DROP table employee; sqlite> Since you have deleted the Employee table, if you retrieve the list of tables again, you can observe only one table in it. sqlite> .tables CRICKETERS sqlite> If you try to delete the Employee table again, since you have already deleted it you will get an error saying “no such table” as shown below − sqlite> DROP table employee; Error: no such table: employee sqlite> 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. sqlite> DROP table IF EXISTS employee; sqlite> Dropping a table using Python You can drop a table whenever you need to, using the DROP statement of MYSQL, but you need to be very careful while deleting any existing table because the data lost will not be recovered after deleting a table. Example To drop a table from a SQLite3 database using python invoke the execute() method on the cursor object and pass the drop statement as a parameter to it. 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 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 ”;

Python SQLite – Create Table ”; Previous Next Using the SQLite CREATE TABLE statement you can create a table in a database. Syntax Following is the syntax to create a table in SQLite database − CREATE TABLE database_name.table_name( column1 datatype PRIMARY KEY(one or more columns), column2 datatype, column3 datatype, ….. columnN datatype ); Example Following SQLite query/statement creates a table with name CRICKETERS in SQLite database − sqlite> CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); sqlite> Let us create one more table OdiStats describing the One-day cricket statistics of each player in CRICKETERS table. sqlite> CREATE TABLE ODIStats ( First_Name VARCHAR(255), Matches INT, Runs INT, AVG FLOAT, Centuries INT, HalfCenturies INT ); sqlite You can get the list of tables in a database in SQLite database using the .tables command. After creating a table, if you can verify the list of tables you can observe the newly created table in it as − sqlite> . tables CRICKETERS ODIStats sqlite> Creating a table using python The Cursor object contains all the methods to execute quires 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 Python program creates a table named Employee in SQLite3 − 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) print(“Table created successfully……..”) # Commit your changes in the database conn.commit() #Closing the connection conn.close() Output Table created successfully…….. Print Page Previous Next Advertisements ”;

Python SQLite – Cursor Object ”; Previous Next The sqlite3.Cursor class is an instance using which you can invoke methods that execute SQLite statements, fetch data from the result sets of the queries. You can create Cursor object using the cursor() method of the Connection object/class. Example import sqlite3 #Connecting to sqlite conn = sqlite3.connect(”example.db”) #Creating a cursor object using the cursor() method cursor = conn.cursor() Methods Following are the various methods provided by the Cursor class/object. Sr.No Method & Description 1 execute() This routine executes an SQL statement. The SQL statement may be parameterized (i.e., placeholders instead of SQL literals). The psycopg2 module supports placeholder using %s sign For example:cursor.execute(“insert into people values (%s, %s)”, (who, age)) 2 executemany() This routine executes an SQL command against all parameter sequences or mappings found in the sequence sql. 3 fetchone() This method fetches the next row of a query result set, returning a single sequence, or None when no more data is available. 4 fetchmany() 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. 5 fetchall() This routine fetches all (remaining) rows of a query result, returning a list. An empty list is returned when no rows are available. Properties Following are the properties of the Cursor class − Sr.No Method & Description 1 arraySize This is a read/write property you can set the number of rows returned by the fetchmany() method. 2 description This is a read only property which returns the list containing the description of columns in a result-set. 3 lastrowid This is a read only property, if there are any auto-incremented columns in the table, this returns the value generated for that column in the last INSERT or, UPDATE operation. 4 rowcount This returns the number of rows returned/updated in case of SELECT and UPDATE operations. 5 connection This read-only attribute provides the SQLite database Connection used by the Cursor object. Print Page Previous Next Advertisements ”;

Python MongoDB – Create Collection ”; Previous Next A collection in MongoDB holds a set of documents, it is analogous to a table in relational databases. You can create a collection using the createCollection() method. This method accepts a String value representing the name of the collection to be created and an options (optional) parameter. Using this you can specify the following − The size of the collection. The max number of documents allowed in the capped collection. Whether the collection we create should be capped collection (fixed size collection). Whether the collection we create should be auto-indexed. Syntax Following is the syntax to create a collection in MongoDB. db.createCollection(“CollectionName”) Example Following method creates a collection named ExampleCollection. > use mydb switched to db mydb > db.createCollection(“ExampleCollection”) { “ok” : 1 } > Similarly, following is a query that creates a collection using the options of the createCollection() method. >db.createCollection(“mycol”, { capped : true, autoIndexId : true, size : 6142800, max : 10000 } ) { “ok” : 1 } > Creating a collection using python Following python example connects to a database in MongoDB (mydb) and, creates a collection in it. Example from pymongo import MongoClient #Creating a pymongo client client = MongoClient(”localhost”, 27017) #Getting the database instance db = client[”mydb”] #Creating a collection collection = db[”example”] print(“Collection created……..”) Output Collection created…….. Print Page Previous Next Advertisements ”;

Python MongoDB – Insert Document ”; Previous Next You can store documents into MongoDB using the insert() method. This method accepts a JSON document as a parameter. Syntax Following is the syntax of the insert method. >db.COLLECTION_NAME.insert(DOCUMENT_NAME) Example > use mydb switched to db mydb > db.createCollection(“sample”) { “ok” : 1 } > doc1 = {“name”: “Ram”, “age”: “26”, “city”: “Hyderabad”} { “name” : “Ram”, “age” : “26”, “city” : “Hyderabad” } > db.sample.insert(doc1) WriteResult({ “nInserted” : 1 }) > Similarly, you can also insert multiple documents using the insert() method. > 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” : “1001”, “name” : “Ram”, “age” : “26”, “city” : “Hyderabad” }, { “_id” : “1002”, “name” : “Rahim”, “age” : 27, “city” : “Bangalore” }, { “_id” : “1003”, “name” : “Robert”, “age” : 28, “city” : “Mumbai” } ] > db.sample.insert(data) BulkWriteResult ({ “writeErrors” : [ ], “writeConcernErrors” : [ ], “nInserted” : 3, “nUpserted” : 0, “nMatched” : 0, “nModified” : 0, “nRemoved” : 0, “upserted” : [ ] }) > Creating a collection using python Pymongo provides a method named insert_one() to insert a document in MangoDB. To this method, we need to pass the document in dictionary format. Example Following example inserts a document in the collection named example. 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 doc1 = {“name”: “Ram”, “age”: “26”, “city”: “Hyderabad”} coll.insert_one(doc1) print(coll.find_one()) Output { ”_id”: ObjectId(”5d63ad6ce043e2a93885858b”), ”name”: ”Ram”, ”age”: ”26”, ”city”: ”Hyderabad” } To insert multiple documents into MongoDB using pymongo, you need to invoke the insert_many() method. 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 ……”) print(res.inserted_ids) Output Data inserted …… [”101”, ”102”, ”103”] Print Page Previous Next Advertisements ”;

Python PostgreSQL – Cursor Object ”; Previous Next The Cursor class of the psycopg library provide methods to execute the PostgreSQL commands in the database using python code. Using the methods of it you can execute SQL statements, fetch data from the result sets, call procedures. You can create Cursor object using the cursor() method of the Connection object/class. 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() Methods Following are the various methods provided by the Cursor class/object. Sr.No Method & Description 1 callproc() This method is used to call existing procedures PostgreSQL database. 2 close() This method is used to close the current cursor object. 3 executemany() This method accepts a list series of parameters list. Prepares an MySQL query and executes it with all the parameters. 4 execute() This method accepts a MySQL query as a parameter and executes the given query. 5 fetchall() This method retrieves all the rows in the result set of a query and returns them as list of tuples. (If we execute this after retrieving few rows it returns the remaining ones) 6 fetchone() This method fetches the next row in the result of a query and returns it as a tuple. 7 fetchmany() This method is similar to the fetchone() but, it retrieves the next set of rows in the result set of a query, instead of a single row. Properties Following are the properties of the Cursor class − Sr.No Property & Description 1 description This is a read only property which returns the list containing the description of columns in a result-set. 2 astrowid This is a read only property, if there are any auto-incremented columns in the table, this returns the value generated for that column in the last INSERT or, UPDATE operation. 3 rowcount This returns the number of rows returned/updated in case of SELECT and UPDATE operations. 4 closed This property specifies whether a cursor is closed or not, if so it returns true, else false. 5 connection This returns a reference to the connection object using which this cursor was created. 6 name This property returns the name of the cursor. 7 scrollable This property specifies whether a particular cursor is scrollable. Print Page Previous Next Advertisements ”;