Category: python Data Access

Python PostgreSQL – Insert Data ”; Previous Next You can insert record into an existing table in PostgreSQL using the INSERT INTO statement. While executing this, you need to specify the name of the table, and values for the columns in it. 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 − 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=# Following PostgreSQL statement inserts a row in the above created table − postgres=# insert into CRICKETERS ( First_Name, Last_Name, Age, Place_Of_Birth, Country) values(”Shikhar”, ”Dhawan”, 33, ”Delhi”, ”India”); INSERT 0 1 postgres=# While inserting records using the INSERT INTO statement, if you skip any columns names Record will be inserted leaving empty spaces at columns which you have skipped. postgres=# insert into CRICKETERS (First_Name, Last_Name, Country) values(”Jonathan”, ”Trott”, ”SouthAfrica”); INSERT 0 1 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. 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 postgres=# After inserting the records into a table you can verify its contents using the SELECT statement as shown below − postgres=# SELECT * from CRICKETERS; first_name | last_name | age | place_of_birth | country ————+————+—–+—————-+————- Shikhar | Dhawan | 33 | Delhi | India Jonathan | Trott | | | SouthAfrica Kumara | Sangakkara | 41 | Matale | Srilanka Virat | Kohli | 30 | Delhi | India Rohit | Sharma | 32 | Nagpur | India (5 rows) Inserting data using python The cursor class of psycopg2 provides a method with name execute() method. This method accepts the query as a parameter and executes it. Therefore, to insert data into a table in PostgreSQL using python − Import psycopg2 package. Create a connection object using the connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it. Turn off the auto-commit mode by setting false as value to the attribute autocommit. The cursor() method of the Connection class of the psycopg2 library returns a cursor object. Create a cursor object using this method. Then, execute the INSERT statement(s) by passing it/them as a parameter to the execute() method. Example Following Python program creates a table with name EMPLOYEE in PostgreSQL database and inserts records into it using the execute() method − 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() # 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 – 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 – 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 ”;

Python MySQL – Introduction ”; Previous Next 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. Print Page Previous Next Advertisements ”;

Python MySQL – Insert Data ”; Previous Next You can add new rows to an existing table of MySQL 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 syntax of the INSERT INTO statement of MySQL. INSERT INTO TABLE_NAME (column1, column2,column3,…columnN) VALUES (value1, value2, value3,…valueN); Example Following query inserts a record into the table named EMPLOYEE. INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (” Mac”, ”Mohan”, 20, ”M”, 2000 ); You can verify the records of the table after insert operation using the SELECT statement as − mysql> select * from Employee; +————+———–+——+——+——–+ | FIRST_NAME | LAST_NAME | AGE | SEX | INCOME | +————+———–+——+——+——–+ | Mac | Mohan | 20 | M | 2000 | +————+———–+——+——+——–+ 1 row in set (0.00 sec) It is not mandatory to specify the names of the columns always, if you pass values of a record in the same order of the columns of the table you can execute the SELECT statement without the column names as follows − INSERT INTO EMPLOYEE VALUES (”Mac”, ”Mohan”, 20, ”M”, 2000); Inserting data in MySQL table using python The execute() method (invoked on the cursor object) accepts a query as parameter and executes the given query. To insert data, you need to pass the MySQL INSERT statement as a parameter to it. cursor.execute(“””INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Mac”, ”Mohan”, 20, ”M”, 2000)”””) To insert data into 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 INSERT statement by passing it as a parameter to the execute() method. Example The following example executes SQL INSERT statement to insert a record into the EMPLOYEE table − 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 SQL query to INSERT a record into the database. sql = “””INSERT INTO EMPLOYEE( FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Mac”, ”Mohan”, 20, ”M”, 2000)””” try: # Executing the SQL command cursor.execute(sql) # Commit your changes in the database conn.commit() except: # Rolling back in case of error conn.rollback() # Closing the connection conn.close() Inserting values dynamically You can also use “%s” instead of values in the INSERT query of MySQL and pass values to them as lists as shown below − cursor.execute(“””INSERT INTO EMPLOYEE VALUES (”Mac”, ”Mohan”, 20, ”M”, 2000)”””, (”Ramya”, ”Ramapriya”, 25, ”F”, 5000)) Example Following example inserts a record into the Employee table dynamically. 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 SQL query to INSERT a record into the database. insert_stmt = ( “INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)” “VALUES (%s, %s, %s, %s, %s)” ) data = (”Ramya”, ”Ramapriya”, 25, ”F”, 5000) try: # Executing the SQL command cursor.execute(insert_stmt, data) # Commit your changes in the database conn.commit() except: # Rolling back in case of error conn.rollback() print(“Data inserted”) # Closing the connection conn.close() Output Data inserted Print Page Previous Next Advertisements ”;

Python MySQL – Database Connection ”; Previous Next 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 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() #Executing an MYSQL function using the execute() method cursor.execute(“SELECT DATABASE()”) # Fetch a single row using fetchone() method. data = cursor.fetchone() print(“Connection established to: “,data) #Closing the connection conn.close() Output On executing, this script produces the following output − D:Python_MySQL>python EstablishCon.py Connection established to: (”mydb”,) You can also establish connection to MySQL by passing credentials (user name, password, hostname, and database name) to connection.MySQLConnection() as shown below − from mysql.connector import (connection) #establishing the connection conn = connection.MySQLConnection(user=”root”, password=”password”, host=”127.0.0.1”, database=”mydb”) #Closing the connection conn.close() Print Page Previous Next Advertisements ”;