Category: python Data Access

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 ”;

Python MySQL – Where Clause ”; Previous Next If you want to fetch, delete or, update particular rows of a table in MySQL, 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 − SELECT column1, column2, columnN FROM table_name WHERE [condition] 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 retrieves the records of the employees whose income is greater than 4000. mysql> SELECT * FROM EMPLOYEE WHERE INCOME > 4000; +————+———–+——+——+——–+ | FIRST_NAME | LAST_NAME | AGE | SEX | INCOME | +————+———–+——+——+——–+ | Raj | Kandukuri | 20 | M | 7000 | | Ramya | Ramapriya | 25 | F | 5000 | +————+———–+——+——+——–+ 2 rows in set (0.00 sec) WHERE clause using python To fetch specific records from a table using the python program − 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 SELECT statement with WHERE clause, by passing it as a parameter to the execute() method. 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 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() #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, ”F”, 5000),(”Mac”, ”Mohan”, 26, ”M”, 2000)] cursor.executemany(insert_stmt, data) conn.commit() #Retrieving specific records using the where clause cursor.execute(“SELECT * from EMPLOYEE WHERE AGE <23″) print(cursor.fetchall()) #Closing the connection conn.close() Output [(”Krishna”, ”Sharma”, 19, ”M”, 2000.0), (”Raj”, ”Kandukuri”, 20, ”M”, 7000.0)] Print Page Previous Next Advertisements ”;

Python Data Access – Quick Guide ”; Previous Next Python MySQL – Introduction The Python standard for database interfaces is the Python DB-API. Most Python database interfaces adhere to this standard. You can choose the right database for your application. Python Database API supports a wide range of database servers such as − GadFly mSQL MySQL PostgreSQL Microsoft SQL Server 2000 Informix Interbase Oracle Sybase Here is the list of available Python database interfaces: Python Database Interfaces and APIs. You must download a separate DB API module for each database you need to access. For example, if you need to access an Oracle database as well as a MySQL database, you must download both the Oracle and the MySQL database modules. What is mysql-connector-python? MySQL Python/Connector is an interface for connecting to a MySQL database server from Python. It implements the Python Database API and is built on top of the MySQL. How do I Install mysql-connector-python? First of all, you need to make sure you have already installed python in your machine. To do so, open command prompt and type python in it and press Enter. If python is already installed in your system, this command will display its version as shown below − C:UsersTutorialspoint>python Python 3.7.3 (v3.7.3:ef4ec6ed12, Mar 25 2019, 22:22:05) [MSC v.1916 64 bit (AMD64)] on win32 Type “help”, “copyright”, “credits” or “license” for more information. >>> Now press ctrl+z and then Enter to get out of the python shell and create a folder (in which you intended to install Python-MySQL connector) named Python_MySQL as − >>> ^Z C:UsersTutorialspoint>d: D:>mkdir Python_MySQL Verify PIP PIP is a package manager in python using which you can install various modules/packages in Python. Therefore, to install Mysql-python mysql-connector-python you need to make sure that you have PIP installed in your computer and have its location added to path. You can do so, by executing the pip command. If you didn’t have PIP in your system or, if you haven’t added its location in the Path environment variable, you will get an error message as − D:Python_MySQL>pip ”pip” is not recognized as an internal or external command, operable program or batch file. To install PIP, download the get-pip.py to the above created folder and, from command navigate it and install pip as follows − D:>cd Python_MySQL D:Python_MySQL>python get-pip.py Collecting pip Downloading https://files.pythonhosted.org/packages/8d/07/f7d7ced2f97ca3098c16565efbe6b15fafcba53e8d9bdb431e09140514b0/pip-19.2.2-py2.py3-none-any.whl (1.4MB) |████████████████████████████████| 1.4MB 1.3MB/s Collecting wheel Downloading https://files.pythonhosted.org/packages/00/83/b4a77d044e78ad1a45610eb88f745be2fd2c6d658f9798a15e384b7d57c9/wheel-0.33.6-py2.py3-none-any.whl Installing collected packages: pip, wheel Consider adding this directory to PATH or, if you prefer to suppress this warning, use –no-warn-script-location. Successfully installed pip-19.2.2 wheel-0.33.6 Installing mysql-connector-python Once you have Python and PIP installed, open command prompt and upgrade pip (optional) as shown below − C:UsersTutorialspoint>python -m pip install –upgrade pip Collecting pip Using cached https://files.pythonhosted.org/packages/8d/07/f7d7ced2f97ca3098c16565efbe6b15fafcba53e8d9bdb431e09140514b0/pip-19.2.2-py2.py3-none-any.whl Python Data Access 4 Installing collected packages: pip Found existing installation: pip 19.0.3 Uninstalling pip-19.0.3: Successfully uninstalled pip-19.0.3 Successfully installed pip-19.2.2 Then open command prompt in admin mode and install python MySQL connect as − C:WINDOWSsystem32>pip install mysql-connector-python Collecting mysql-connector-python Using cached https://files.pythonhosted.org/packages/99/74/f41182e6b7aadc62b038b6939dce784b7f9ec4f89e2ae14f9ba8190dc9ab/mysql_connector_python-8.0.17-py2.py3-none-any.whl Collecting protobuf>=3.0.0 (from mysql-connector-python) Using cached https://files.pythonhosted.org/packages/09/0e/614766ea191e649216b87d331a4179338c623e08c0cca291bcf8638730ce/protobuf-3.9.1-cp37-cp37m-win32.whl Collecting six>=1.9 (from protobuf>=3.0.0->mysql-connector-python) Using cached https://files.pythonhosted.org/packages/73/fb/00a976f728d0d1fecfe898238ce23f502a721c0ac0ecfedb80e0d88c64e9/six-1.12.0-py2.py3-none-any.whl Requirement already satisfied: setuptools in c:program files (x86)python37-32libsite-packages (from protobuf>=3.0.0->mysql-connector-python) (40.8.0) Installing collected packages: six, protobuf, mysql-connector-python Successfully installed mysql-connector-python-8.0.17 protobuf-3.9.1 six-1.12.0 Verification To verify the installation of the create a sample python script with the following line in it. import mysql.connector If the installation is successful, when you execute it, you should not get any errors − D:Python_MySQL>python test.py D:Python_MySQL> Installing python from scratch Simply, if you need to install Python from scratch. Visit the Python Home Page. Click on the Downloads button, you will be redirected to the downloads page which provides links for latest version of python for various platforms choose one and download it. For instance, we have downloaded python-3.7.4.exe (for windows). Start the installation process by double-clicking the downloaded .exe file. Check the Add Python 3.7 to Path option and proceed with the installation. After completion of this process, python will be installed in your system. Python MySQL – Database Connection To connect with MySQL, (one way is to) open the MySQL command prompt in your system as shown below − It asks for password here; you need to type the password you have set to the default user (root) at the time of installation. Then a connection is established with MySQL displaying the following message − Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 4 Server version: 5.7.12-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ”help;” or ”h” for help. Type ”c” to clear the current input statement. You can disconnect from the MySQL database any time using the exit command at mysql> prompt. mysql> exit Bye Establishing connection with MySQL using python Before establishing connection to MySQL database using python, assume − That we have created a database with name mydb. We have created a table EMPLOYEE with columns FIRST_NAME, LAST_NAME, AGE, SEX and INCOME. The credentials we are using to connect with MySQL are username: root, password: password. You can establish a connection using the connect() constructor. This accepts username, password, host and, name of the database you need to connect with (optional) and, returns an object of the MySQLConnection class. Example Following is the example of connecting with MySQL database “mydb”. import mysql.connector #establishing the connection

Python MongoDB – Delete Document ”; Previous Next You can delete documents in a collection using the remove() method of MongoDB. This method accepts two optional parameters − deletion criteria specifying the condition to delete documents. just one, if you pass true or 1 as second parameter, then only one document will be deleted. Syntax Following is the syntax of the remove() method − >db.COLLECTION_NAME.remove(DELLETION_CRITTERIA) 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 query deletes the document(s) of the collection which have name value as Sarmista. > db.sample.remove({“name”: “Sarmista”}) WriteResult({ “nRemoved” : 1 }) > db.sample.find() { “_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” : “1006”, “name” : “Rasajna”, “age” : 26, “city” : “Chennai” } If you invoke remove() method without passing deletion criteria, all the documents in the collection will be deleted. > db.sample.remove({}) WriteResult({ “nRemoved” : 5 }) > db.sample.find() Deleting documents using python To delete documents from a collection of MangoDB, you can delete documents from a collections using the methods delete_one() and delete_many() methods. These methods accept a query object specifying the condition for deleting documents. The detele_one() method deletes a single document, in case of a match. If no query is specified this method deletes the first document in the collection. Example Following python example deletes the document in the collection which has id value as 1006. from pymongo import MongoClient #Creating a pymongo client client = MongoClient(”localhost”, 27017) #Getting the database instance db = client[”lpaksgf”] #Creating a collection coll = db[”example”] #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 ……”) #Deleting one document coll.delete_one({“_id” : “1006”}) #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 after update operation: {”_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”} Similarly, the delete_many() method of pymongo deletes all the documents that satisfies the specified condition. Example Following example deletes all the documents in the collection whose age value is greater than 26 − from pymongo import MongoClient #Creating a pymongo client client = MongoClient(”localhost”, 27017) #Getting the database instance db = client[”sampleDB”] #Creating a collection coll = db[”example”] #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 ……”) #Deleting multiple documents coll.delete_many({“age”:{“$gt”:”26″}}) #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 after update operation: {”_id”: ”1001”, ”name”: ”Ram”, ”age”: ”26”, ”city”: ”Hyderabad”} {”_id”: ”1004”, ”name”: ”Romeo”, ”age”: ”25”, ”city”: ”Pune”} {”_id”: ”1005”, ”name”: ”Sarmista”, ”age”: ”23”, ”city”: ”Delhi”} {”_id”: ”1006”, ”name”: ”Rasajna”, ”age”: ”26”, ”city”: ”Chennai”} If you invoke the delete_many() method without passing any query, this method deletes all the documents in the collection. coll.delete_many({}) Print Page Previous Next Advertisements ”;

Python SQLite – Establishing Connection ”; Previous Next To establish connection with SQLite Open command prompt, browse through the location of where you have installed SQLite and just execute the command sqlite3 as shown below − Establishing connection using python You can communicate with SQLite2 database using the SQLite3 python module. To do so, first of all you need to establish a connection (create a connection object). To establish a connection with SQLite3 database using python you need to − Import the sqlite3 module using the import statement. The connect() method accepts the name of the database you need to connect with as a parameter and, returns a Connection object. Example import sqlite3 conn = sqlite3.connect(”example.db”) Output print(“Connection established ……….”) Print Page Previous Next Advertisements ”;

Python MongoDB – Query ”; Previous Next While retrieving using find() method, you can filter the documents using the query object. You can pass the query specifying the condition for the required documents as a parameter to this method. Operators Following is the list of operators used in the queries in MongoDB. Operation Syntax Example Equality {“key” : “value”} db.mycol.find({“by”:”tutorials point”}) Less Than {“key” :{$lt:”value”}} db.mycol.find({“likes”:{$lt:50}}) Less Than Equals {“key” :{$lte:”value”}} db.mycol.find({“likes”:{$lte:50}}) Greater Than {“key” :{$gt:”value”}} db.mycol.find({“likes”:{$gt:50}}) Greater Than Equals {“key” {$gte:”value”}} db.mycol.find({“likes”:{$gte:50}}) Not Equals {“key”:{$ne: “value”}} db.mycol.find({“likes”:{$ne:50}}) Example1 Following example retrieves the document in a collection whose name is sarmista. from pymongo import MongoClient #Creating a pymongo client client = MongoClient(”localhost”, 27017) #Getting the database instance db = client[”sdsegf”] #Creating a collection coll = db[”example”] #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 data print(“Documents in the collection: “) for doc1 in coll.find({“name”:”Sarmista”}): print(doc1) Output Data inserted …… Documents in the collection: {”_id”: ”1005”, ”name”: ”Sarmista”, ”age”: ”23”, ”city”: ”Delhi”} Example2 Following example retrieves the document in a collection whose age value is greater than 26. from pymongo import MongoClient #Creating a pymongo client client = MongoClient(”localhost”, 27017) #Getting the database instance db = client[”ghhj”] #Creating a collection coll = db[”example”] #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 data print(“Documents in the collection: “) for doc in coll.find({“age”:{“$gt”:”26″}}): print(doc) Output Data inserted …… Documents in the collection: {”_id”: ”1002”, ”name”: ”Rahim”, ”age”: ”27”, ”city”: ”Bangalore”} {”_id”: ”1003”, ”name”: ”Robert”, ”age”: ”28”, ”city”: ”Mumbai”} Print Page Previous Next Advertisements ”;

Python MySQL – Order By ”; Previous Next While fetching data using SELECT query, you can sort the results in desired order (ascending or descending) using the OrderBy clause. By default, this clause sorts results in ascending order, if you need to arrange them in descending order you need to use “DESC” explicitly. Syntax Following is the syntax SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2,.. columnN] [ASC | DESC]; of the ORDER BY 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 statement retrieves the contents of the EMPLOYEE table in ascending order of the age. mysql> SELECT * FROM EMPLOYEE ORDER BY AGE; +————+———–+——+——+——–+ | FIRST_NAME | LAST_NAME | AGE | SEX | INCOME | +————+———–+——+——+——–+ | Krishna | Sharma | 19 | M | 2000 | | Raj | Kandukuri | 20 | M | 7000 | | Ramya | Ramapriya | 25 | F | 5000 | | Mac | Mohan | 26 | M | 2000 | +————+———–+——+——+——–+ 4 rows in set (0.04 sec) You can also retrieve data in descending order using DESC as − mysql> SELECT * FROM EMPLOYEE ORDER BY FIRST_NAME, INCOME DESC; +————+———–+——+——+——–+ | FIRST_NAME | LAST_NAME | AGE | SEX | INCOME | +————+———–+——+——+——–+ | Krishna | Sharma | 19 | M | 2000 | | Mac | Mohan | 26 | M | 2000 | | Raj | Kandukuri | 20 | M | 7000 | | Ramya | Ramapriya | 25 | F | 5000 | +————+———–+——+——+——–+ 4 rows in set (0.00 sec) 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 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() #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”, 26, ”M”, 2000), (”Raj”, ”Kandukuri”, 20, ”M”, 7000), (”Ramya”, ”Ramapriya”, 29, ”F”, 5000), (”Mac”, ”Mohan”, 26, ”M”, 2000)] 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()) #Closing the connection conn.close() Output [(”Raj”, ”Kandukuri”, 20, ”M”, 7000.0), (”Krishna”, ”Sharma”, 26, ”M”, 2000.0), (”Mac”, ”Mohan”, 26, ”M”, 2000.0), (”Ramya”, ”Ramapriya”, 29, ”F”, 5000.0) ] In the same way you can retrieve data from a table in descending order using the ORDER BY clause. Example 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 specific records using the ORDERBY clause cursor.execute(“SELECT * from EMPLOYEE ORDER BY INCOME DESC”) print(cursor.fetchall()) #Closing the connection conn.close() Output [(”Raj”, ”Kandukuri”, 20, ”M”, 7000.0), (”Ramya”, ”Ramapriya”, 29, ”F”, 5000.0), (”Krishna”, ”Sharma”, 26, ”M”, 2000.0), (”Mac”, ”Mohan”, 26, ”M”, 2000.0) ] Print Page Previous Next Advertisements ”;

Python PostgreSQL – Create Database ”; Previous Next You can create a database in PostgreSQL using the CREATE DATABASE statement. You can execute this statement in PostgreSQL shell prompt by specifying the name of the database to be created after the command. Syntax Following is the syntax of the CREATE DATABASE statement. CREATE DATABASE dbname; Example Following statement creates a database named testdb in PostgreSQL. postgres=# CREATE DATABASE testdb; CREATE DATABASE You can list out the database in PostgreSQL using the l command. If you verify the list of databases, you can find the newly created database as follows − postgres=# l List of databases Name | Owner | Encoding | Collate | Ctype | ———–+———-+———-+—————————-+————-+ mydb | postgres | UTF8 | English_United States.1252 | ……….. | postgres | postgres | UTF8 | English_United States.1252 | ……….. | template0 | postgres | UTF8 | English_United States.1252 | ……….. | template1 | postgres | UTF8 | English_United States.1252 | ……….. | testdb | postgres | UTF8 | English_United States.1252 | ……….. | (5 rows) You can also create a database in PostgreSQL from command prompt using the command createdb, a wrapper around the SQL statement CREATE DATABASE. C:Program FilesPostgreSQL11bin> createdb -h localhost -p 5432 -U postgres sampledb Password: Creating a database using python The cursor class of psycopg2 provides various methods execute various PostgreSQL commands, fetch records and copy data. You can create a cursor object using the cursor() method of the Connection class. The execute() method of this class accepts a PostgreSQL query as a parameter and executes it. Therefore, to create a database in PostgreSQL, execute the CREATE DATABASE query using this method. Example Following python example creates a database named mydb in PostgreSQL database. import psycopg2 #establishing the connection conn = psycopg2.connect( database=”postgres”, user=”postgres”, password=”password”, host=”127.0.0.1”, port= ”5432” ) conn.autocommit = True #Creating a cursor object using the cursor() method cursor = conn.cursor() #Preparing query to create a database sql = ”””CREATE database mydb”””; #Creating a database cursor.execute(sql) print(“Database created successfully……..”) #Closing the connection conn.close() Output Database created successfully…….. Print Page Previous Next Advertisements ”;

Python MySQL – Update Table ”; Previous Next UPDATE Operation on any database updates one or more records, which are already available in the database. You can update the values of existing records in MySQL 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 MySQL − UPDATE table_name SET column1 = value1, column2 = value2…., columnN = valueN WHERE [condition]; You can combine N number of conditions using the AND or the OR operators. 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 increases the age of all male employees by one year − mysql> UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = ”M”; Query OK, 3 rows affected (0.06 sec) Rows matched: 3 Changed: 3 Warnings: 0 If you retrieve the contents of the table, you can see the updated values as − 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 | | Mac | Mohan | 27 | M | 2000 | +————+———–+——+——+——–+ 4 rows in set (0.00 sec) Updating the contents of a table using Python To update the records in a table in MySQL using python − 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 UPDATE statement by passing it as a parameter to the execute() method. Example The following example increases age of all the males by one year. 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() #Preparing the query to update the records sql = ”””UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = ”M” ””” try: # Execute the SQL command cursor.execute(sql) # Commit your changes in the database conn.commit() except: # Rollback in case there is any error conn.rollback() #Retrieving data sql = ”””SELECT * from EMPLOYEE””” #Executing the query cursor.execute(sql) #Displaying the result print(cursor.fetchall()) #Closing the connection conn.close() Output [(”Krishna”, ”Sharma”, 22, ”M”, 2000.0), (”Raj”, ”Kandukuri”, 23, ”M”, 7000.0), (”Ramya”, ”Ramapriya”, 26, ”F”, 5000.0) ] Print Page Previous Next Advertisements ”;

Python MySQL – 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 MySQL − DROP TABLE table_name; Example Before deleting a table get the list of tables using the SHOW TABLES statement as follows − mysql> SHOW TABLES; +—————–+ | Tables_in_mydb | +—————–+ | contact | | cricketers_data | | employee | | sample | | tutorials | +—————–+ 5 rows in set (0.00 sec) Following statement removes the table named sample from the database completely − mysql> DROP TABLE sample; Query OK, 0 rows affected (0.29 sec) Since we have deleted the table named sample from MySQL, if you get the list of tables again you will not find the table name sample in it. mysql> SHOW TABLES; +—————–+ | Tables_in_mydb | +—————–+ | contact | | cricketers_data | | employee | | tutorials | +—————–+ 4 rows in set (0.00 sec) Removing 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. 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 table drops a table named EMPLOYEE from the database. 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 the list of tables print(“List of tables in the database: “) cursor.execute(“SHOW Tables”) print(cursor.fetchall()) #Doping EMPLOYEE table if already exists cursor.execute (“DROP TABLE EMPLOYEE”) print(“Table dropped… “) #Retrieving the list of tables print( “List of tables after dropping the EMPLOYEE table: “) cursor.execute(“SHOW Tables”) print(cursor.fetchall()) #Closing the connection conn.close() Output List of tables in the database: [(”employee”,), (”employeedata”,), (”sample”,), (”tutorials”,)] Table dropped… List of tables after dropping the EMPLOYEE table: [(”employeedata”,), (”sample”,), (”tutorials”,)] Drop table only if exists If you try to drop a table which does not exist in the database, an error occurs as − mysql.connector.errors.ProgrammingError: 1051 (42S02): Unknown table ”mydb.employee” You can prevent this error by verifying whether the table exists before deleting, by adding the IF EXISTS to the DELETE statement. 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 the list of tables print(“List of tables in the database: “) cursor.execute(“SHOW Tables”) print(cursor.fetchall()) #Doping EMPLOYEE table if already exists cursor.execute(“DROP TABLE IF EXISTS EMPLOYEE”) print(“Table dropped… “) #Retrieving the list of tables print(“List of tables after dropping the EMPLOYEE table: “) cursor.execute(“SHOW Tables”) print(cursor.fetchall()) #Closing the connection conn.close() Output List of tables in the database: [(”employeedata”,), (”sample”,), (”tutorials”,)] Table dropped… List of tables after dropping the EMPLOYEE table: [(”employeedata”,), (”sample”,), (”tutorials”,)] Print Page Previous Next Advertisements ”;