We may sometimes require to get the list of all the tables present in our database. This can be done by using the SHOW TABLES command.The SHOW TABLES command is used to display the table names in a database as well as the server.SyntaxTo show the tables present in a database −SHOW TABLESThe above statement when executed using the cursor object returns the names of the tables present in our database.To show the tables present in a serverSELECT table_name FROM information_schema.tablesSteps to show all tables present in a database and server using MySQL in pythonimport MySQL connectorestablish connection with the ... Read More

It may be required to count the number of columns present in a SQL table.This is done using count(*) function with information_schema.columns and the WHERE clause. The WHERE clause is used to specify the name of the table whose columns are to be counted.SyntaxSELECT COUNT(*) FROM information_schema.columns WHERE table_name= ‘your_table_name’Steps to count columns in a table using MySQL in pythonimport MySQL connectorestablish connection with the connector using connect()create the cursor object using cursor() methodcreate a query using the appropriate mysql statementsexecute the SQL query using execute() methodclose the connectionSuppose we have a table named “Students” as below −+----------+---------+-----------+------------+ |   ... Read More

We may at times need to check if a particular record exists in a table or not.This can de done using the EXISTS statement. The EXISTS statement returns true if the following subquery returns one or more records.SyntaxSELECT * FROM table_name WHERE EXISTS(sub_query)The subquery if returns one or more rows, the EXISTS will return true.Steps to check if a record exists in a table using MySQL in pythonimport MySQL connectorestablish connection with the connector using connect()create the cursor object using cursor() methodcreate a query using the appropriate mysql statementsexecute the SQL query using execute() methodclose the connectionSuppose we have the ... Read More

It may be sometimes required to add a new column in a existing table. Suppose we have a “Students” table with columns such as Name, Age, Roll no. We want to add a new column “Address” into our existing table.This can be done by using the ALTER command. The ALTER command is used to modify, drop or update columns in the database. This can also be used to add a new column into the table using ADD clause.SyntaxALTER TABLE table_name ADD new_column_name column_definition [FIRST | AFTER exisiting_column]Here, table_name refers to the name of the table, new_column_name refers to the name ... Read More

The arithmetic operations as the name suggests are used to perform the operations such as additon, subtraction, division, multiplication or modulus.The arithmetic operations are operated on the numeric data in your table.SyntaxTo perform additionSELECT op1+op2 FROM table_nameHere, the op1 and op2 are the column names or the numeric values. If the op1 and op2 are the numeric values, then FROM clause are not required.The + in the above syntax can be replaced by -, *, %, / to perform other arithmetic operations.Steps to perform arithmetic operations in a table using MySQL in pythonimport MySQL connectorestablish connection with the connector using ... Read More

A comment is a readable explanation provided about something. The comments in the code are provided to explain the purpose of the code staement used. This enables the outside person to understand the need and use of that particular code.The comments are ignored by the compilers and are not executed. These are just for the explanatory purpose and are of great importance.We can add comments to columns in a sql table. These comments will explain the function or tell something about the column. If some other person is viewing and working on that table, he can get idea about the ... Read More

IF statement is a conditional statement in python. It checks for a particular condition and performs some action accordingly.Here, we will discuss the use of IF statement using python to interact with the sql database.SyntaxIF(condition, value_if_true, value_if_false)The IF statemen tcan be used with the SELECT clause to perform selection based on some criteria .Steps to use IF statement to select data from a table using MySQL in pythonimport MySQL connectorestablish connection with the connector using connect()create the cursor object using cursor() methodcreate a query using the appropriate mysql statementsexecute the SQL query using execute() methodclose the connectionSuppose we the following ... Read More

The data is inserted into the table in Mysql using the INSERT statement. While inserting the data into the table, either the data must be provided in the same order as the column definition in the database or the column names must be provided along with the data while using INSERT statement.To get and print the ID of the last inserted row, lastrowid is used. This is a special keyword which is used to get the ID of the last inserted row. There are certain prerequisites to be taken care of before using this method.The ID column must be the ... Read More

These are the functions used to perform the arithmetic operations on the column values in a table.The COUNT() function is used to return the number of rows which satisfy a certain condition.The SUM() function is used to return the sum of numerical values in a column in the table.The NULL values are ignored.SyntaxCOUNT()SELECT COUNT(column_name) FROM table_name WHERE conditionSUM()SELECT SUM(column_name) FROM table_nameSteps invloved to use count() and sum() functions on a table using MySQL in pythonimport MySQL connectorestablish connection with the connector using connect()create the cursor object using cursor() methodcreate a query using the appropriate mysql statementsexecute the SQL query using ... Read More

We can create copy of an existing table in mysql using Python. The entire table will be copied including the columns, the column definitions and all the rows of the table.SyntaxCREATE TABLE table_name SELECT * FROM existing_tabletable_name is the name of the new table to be created. existing_table is the name of the table which is to be copied.Steps to copy a table using MySQL in pythonimport MySQL connectorestablish connection with the connector using connect()create the cursor object using cursor() methodcreate a query using the appropriate mysql statementsexecute the SQL query using execute() methodclose the connectionSuppose, we have a table ... Read More