- [Introduction to MySQL and Queries](intro_mysql_queries.md)

MySQL is a widely-used open-source relational database management system (RDBMS) that utilizes SQL (Structured Query Language) for managing and querying data. In Python, the **mysql-connector-python** library allows you to connect to MySQL databases and execute SQL queries, providing a way to interact with the database from within a Python program.

* Python and MySQL Server must be installed and configured.

* The library: **mysql-connector-python** must be installed.

To establish a connection with the MySQL server, you need to import the **mysql.connector** module and create a connection object using the **connect()** function by providing the prompt server details as mentioned.

import mysql.connector

con = mysql.connector.connect(

host ="localhost",

user ="root",

passwd ="12345"

)

print((con.is_connected()))

Having established a connection with the server, you get the following output :

```

To create a database, you need to execute the **CREATE DATABASE** query. The following code snippet demonstrates how to create a database named **GSSOC**.

import mysql.connector

conn = mysql.connector.connect(

host="localhost",

user="root",

password="12345"

)

cursor = conn.cursor()

cursor.execute("SHOW DATABASES")

databases = cursor.fetchall()

for database in databases:

print(database[0])

cursor.execute("CREATE DATABASE GSSOC")

print("\nAfter creation of the database\n")

cursor.execute("SHOW DATABASES")

databases = cursor.fetchall()

for database in databases:

print(database[0])

cursor.close()

conn.close()

You can observe in the output below, after execution of the query a new database named **GSSOC** has been created.

Now, we will create a table in the database. We will create a table named **example_table** in the database **GSSOC**. We will execute **CREATE TABLE** query and provide the fields for the table as mentioned in the code below:

import mysql.connector

conn = mysql.connector.connect(

host="localhost",

user="root",

password="12345"

)

cursor = conn.cursor()

cursor.execute("USE GSSOC")

cursor.execute("SHOW TABLES")

tables = cursor.fetchall()

print("Before creation of table\n")

for table in tables:

print(table[0])

create_table_query = """

cursor.execute(create_table_query)

conn.commit()

print("\nAfter creation of Table\n")

cursor.execute("SHOW TABLES")

tables = cursor.fetchall()

for table in tables:

print(table[0])

cursor.close()

conn.close()

To insert data in an existing table, the **INSERT INTO** query is used, followed by the name of the table in which the data needs to be inserted. The following code demonstrates the insertion of multiple records in the table by **executemany()**.

import mysql.connector

conn = mysql.connector.connect(

host="localhost",

user="root",

password="12345"

)

cursor = conn.cursor()

cursor.execute("USE GSSOC")

insert_data_query = """

data_to_insert = [

("John Doe", 28, "john.doe@example.com"),

("Jane Smith", 34, "jane.smith@example.com"),

("Sam Brown", 22, "sam.brown@example.com")

]

cursor.executemany(insert_data_query, data_to_insert)

conn.commit()

cursor.close()

conn.close()

To display the data from a table, the **SELECT** query is used. The following code demonstrates the display of data from the table.

import mysql.connector

conn = mysql.connector.connect(

host="localhost",

user="root",

password="12345"

)

cursor = conn.cursor()

cursor.execute("USE GSSOC")

display_data_query = "SELECT * FROM example_table"

cursor.execute(display_data_query)

rows = cursor.fetchall()

column_names = [desc[0] for desc in cursor.description]

print(column_names)

for row in rows:

print(row)

cursor.close()

conn.close()

To update data in the table, **UPDATE** query is used. In the following code, we will be updating the email and age of the record where the name is John Doe.

import mysql.connector

conn = mysql.connector.connect(

host="localhost",

user="root",

password="12345"

)

cursor = conn.cursor()

cursor.execute("USE GSSOC")

display_data_query = "SELECT * FROM example_table"

update_data_query = """

data_to_update = (30, "new.email@example.com", "John Doe")

cursor.execute(update_data_query, data_to_update)

conn.commit()

cursor.execute(display_data_query)

rows = cursor.fetchall()

column_names = [desc[0] for desc in cursor.description]

print(column_names)

for row in rows:

print(row)

cursor.close()

conn.close()

In this segment, we will Delete the record named "John Doe" using the **DELETE** and **WHERE** statements in the query. The following code explains the same and the observe the change in output.

import mysql.connector

conn = mysql.connector.connect(

host="localhost",

user="root",

password="12345"

)

cursor = conn.cursor()

cursor.execute("USE GSSOC")

display_data_query = "SELECT * FROM example_table"

delete_data_query = "DELETE FROM example_table WHERE name = %s"

data_to_delete = ("John Doe",)

cursor.execute(delete_data_query, data_to_delete)

conn.commit()

cursor.execute(display_data_query)

rows = cursor.fetchall()

column_names = [desc[0] for desc in cursor.description]

print(column_names)

for row in rows:

print(row)

cursor.close()

conn.close()

For deleting a table, you can use the **DROP** query in the following manner:

import mysql.connector

conn = mysql.connector.connect(

host="localhost",

user="root",

password="12345"

)

cursor = conn.cursor()

cursor.execute("USE GSSOC")

delete_table_query = "DROP TABLE IF EXISTS example_table"

cursor.execute(delete_table_query)

cursor.execute("SHOW TABLES LIKE 'example_table'")

result = cursor.fetchone()

cursor.close()

conn.close()

if result:

print("Table deletion failed.")

else:

print("Table successfully deleted.")

Similarly, you can delete the database also by using the **DROP** and accordingly changing the query to be executed.