What is SQL?

SQL is a query language which is used to communicate with a database. It is the standard language for relational database management systems.
SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc.
SQL programming can be used to perform multiple actions on data such as :
- Query data from a database
- Insert data into a database
- Update data in a database
- Delete data from a database
- Extract data from a database etc.
There are specific SQL commands which can be used to accomplish almost everything that one needs to do with a database, they are:
- SELECT: To query data from a database.
- INSERT: To insert data into a database.
- UPDATE: To update data in a database.
- DELETE: To delete data from a database.
- CREATE: To create a table in a database.
- DROP: To drop a table in a database.
- ALTER: To alter a table in a database.

All about Table in SQL.

A relational database system contains one or more objects called tables. The data or information for the database are stored in these tables. Tables are uniquely identified by their names and are comprised of columns and rows. Columns contain the column name, data type, and any other attributes for the column. Rows contain the records or data for the columns. Here is a sample table called "employee".
Before creating a table in a database, you need to create a database, and then create a table in that database. command to create a database is:
- CREATE DATABASE database_name;
We have created database called "employee" in Mysql Workbench with following code:
- CREATE DATABASE employee

Database Connection

To connect to a database employee using python we need a library called pymysql.

import pymysql
dbconnection = pymysql.connect(host="localhost",user= "root",password= "@sadhguru123",database= "employee")
dbconnection

<pymysql.connections.Connection at 0x19e942d3348>

Creating a Table in Database Employee

Enter the keywords create table followed by the table name,
Followed by an open parenthesis,
Followed by the first column name,
Eollowed by the data type for that column,
Followed by any optional constraints, and followed by a closing parenthesis.
To create a table in a database, we need to use the following command:
- CREATE TABLE table_name (column_name data_type, column_name data_type, ...);
Table name and column name must start with a letter, and can be followed by letters, numbers, or underscores.
Do not use any SQL reserved keywords as names for tables or column names (such as "select", "create", "insert", etc).
Data types specify what the type of data can be for that particular column. If a column called "Last_Name", is to be used to hold names, then that particular column should have a "varchar" (variable-length character) data type.

with dbconnection:
    with dbconnection.cursor() as cursor:
        #Create Table
        sql = """CREATE TABLE emp (EmployeeName VARCHAR(20) COLLATE utf8_bin NOT NULL,
                  EmployeeID INT(6) NOT NULL,
                  ManagerName VARCHAR(20) COLLATE utf8_bin NOT NULL,
                  Division INT(1) NOT NULL)"""
        cursor.execute(sql)

Inserting Data in Database Employee

To insert records into a table, enter the key words insert into followed by the table name, followed by an open parenthesis, followed by a list of column names separated by commas, followed by a closing parenthesis, followed by the keyword values, followed by the list of values enclosed in parenthesis.
The values that you enter will be held in the rows and they will match up with the column names that you specify.
Strings should be enclosed in single quotes, and numbers should not.
To insert data into a table, we need to use the following command:
- INSERT INTO table_name (column_name, column_name, ...) VALUES (value, value, ...);
Example:
- INSERT INTO empinfo (first, last, id, age, city, state) VALUES ('Luke', 'Duke', 45454, '22', 'Hazard Co', 'Georgia');

with dbconnection:
    with dbconnection.cursor()  as cursor:
        sql = """INSERT INTO emp5 (EmployeeName,EmployeeID,ManagerName,Division)
        VALUES ('Dipesh Silwal','1234', 'Bikash Mallik','1'),
        ('Mukesh Silwal', '1123', 'Bikash Masslik', '0'),
        ('Rita khadka', '1122', 'Ramesh Bista', '0')"""
        cursor.execute(sql)
        
        # the connection is not autocommited by default. So we must commit to save our changes.
        dbconnection.commit()
        
        number_of_rows= cursor.execute("""SELECT * FROM emp5""") 
        rows = cursor.fetchall()
        for row in rows:
            print (row)

('Dipesh Silwal', 1234, 'Bikash Mallik', 1)
('Mukesh Silwal', 1123, 'Bikash Masslik', 0)
('Rita khadka', 1122, 'Ramesh Bista', 0)

Updating Table

The update statement is used to update or change records that match a specified criteria. This is accomplished by carefully constructing a where clause.
Command to Update a table:
- UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
- Example :
  - UPDATA emp SET EmployeeName = 'Rakesh Thapa', 'ManagerName = 'Kedar Bista'' WHERE Division = 0;'

with dbconnection:
    with dbconnection.cursor()  as cursor:
        sql = """UPDATE emp5  SET EmployeeName = 'Rakesh Thapa', ManagerName = 'Kedar Bista' WHERE EmployeeID = '1122'"""
        cursor.execute(sql)
        dbconnection.commit()
        
        number_of_rows= cursor.execute("""SELECT * FROM emp5""") 
        rows = cursor.fetchall()
        for row in rows:
            print (row)

('Dipesh Silwal', 1234, 'Bikash Mallik', 1)
('Mukesh Silwal', 1123, 'Bikash Masslik', 0)
('Rakesh Thapa', 1122, 'Kedar Bista', 0)

Deleting Records from Table

The delete statement is used to delete records or rows from the table.
Command to delete a record:
- DELETE FROM table_name WHERE condition;
- Example:
  - DELETE FROM emp5 WHERE EmployeeID = 1122;

with dbconnection:
    with dbconnection.cursor()  as cursor:
        sql = """DELETE FROM emp5 WHERE EmployeeID = 1122"""
        cursor.execute(sql)
        dbconnection.commit()
        
        number_of_rows= cursor.execute("""SELECT * FROM emp5""") 
        rows = cursor.fetchall()
        for row in rows:
            print (row)

('Dipesh Silwal', 1234, 'Bikash Mallik', 1)
('Mukesh Silwal', 1123, 'Bikash Masslik', 0)

Drop a Table

The drop table command is used to delete a table and all rows in the table.
To delete an entire table including all of its rows, issue the drop table command followed by the tablename.
Drop table is different from deleting all of the records in the table.
Deleting all of the records in the table leaves the table including column and constraint information.
Dropping the table removes the table definition as well as all of its rows.
Command to drop a table:
- DROP TABLE table_name;
- Example:
  - DROP TABLE emp5;

with dbconnection:
    with dbconnection.cursor()  as cursor:
        sql = """DROP TABLE emp5"""
        cursor.execute(sql)
        dbconnection.commit()
        
        number_of_rows= cursor.execute("""SELECT * FROM emp5""") 
        rows = cursor.fetchall()
        for row in rows:
            print (row)

---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
~\AppData\Local\Temp\ipykernel_14728\3595214741.py in <module>
      5         dbconnection.commit()
      6 
----> 7         number_of_rows= cursor.execute("""SELECT * FROM emp5""")
      8         rows = cursor.fetchall()
      9         for row in rows:

d:\AnacondaInstallation\envs\sql\lib\site-packages\pymysql\cursors.py in execute(self, query, args)
    146         query = self.mogrify(query, args)
    147 
--> 148         result = self._query(query)
    149         self._executed = query
    150         return result

d:\AnacondaInstallation\envs\sql\lib\site-packages\pymysql\cursors.py in _query(self, q)
    308         self._last_executed = q
    309         self._clear_result()
--> 310         conn.query(q)
    311         self._do_get_result()
    312         return self.rowcount

d:\AnacondaInstallation\envs\sql\lib\site-packages\pymysql\connections.py in query(self, sql, unbuffered)
    546             sql = sql.encode(self.encoding, "surrogateescape")
    547         self._execute_command(COMMAND.COM_QUERY, sql)
--> 548         self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    549         return self._affected_rows
    550 

d:\AnacondaInstallation\envs\sql\lib\site-packages\pymysql\connections.py in _read_query_result(self, unbuffered)
    773         else:
    774             result = MySQLResult(self)
--> 775             result.read()
    776         self._result = result
    777         if result.server_status is not None:

d:\AnacondaInstallation\envs\sql\lib\site-packages\pymysql\connections.py in read(self)
   1154     def read(self):
   1155         try:
-> 1156             first_packet = self.connection._read_packet()
   1157 
   1158             if first_packet.is_ok_packet():

d:\AnacondaInstallation\envs\sql\lib\site-packages\pymysql\connections.py in _read_packet(self, packet_type)
    723             if self._result is not None and self._result.unbuffered_active is True:
    724                 self._result.unbuffered_active = False
--> 725             packet.raise_for_error()
    726         return packet
    727 

d:\AnacondaInstallation\envs\sql\lib\site-packages\pymysql\protocol.py in raise_for_error(self)
    219         if DEBUG:
    220             print("errno =", errno)
--> 221         err.raise_mysql_exception(self._data)
    222 
    223     def dump(self):

d:\AnacondaInstallation\envs\sql\lib\site-packages\pymysql\err.py in raise_mysql_exception(data)
    141     if errorclass is None:
    142         errorclass = InternalError if errno < 1000 else OperationalError
--> 143     raise errorclass(errno, errval)

ProgrammingError: (1146, "Table 'employee.emp5' doesn't exist")

Summary

In this tutorial we see how to create a table, insert data into a table, update data in a table, delete data from a table, and drop a table using pymysql and sql command.