MySQL in Python

Introduction

MySQL, one of the most popular open-source relational database management systems, seamlessly integrates with Python to empower developers in building robust and scalable applications. This article delves into the process of connecting and interacting with MySQL databases using Python, providing practical examples to illustrate each step.

• Setting Up MySQL with Python
• Installation:
• Common things across DBs.
• More on Cursor
• SQLite DB
• Executing the SQL queries
• Connecting to MySQL
• Task - READ, INSERT, UPDATE, DELETE,
• Conclusion
• FAQs

Setting Up MySQL with Python

Before embarking on the journey of MySQL and Python integration, it's crucial to set up the environment. This section walks you through the steps, ensuring a smooth and error-free configuration for your project.

You can read more about the MySQL.

Installation:

Before getting started, you need to install the pymysql package. You can do this using pip:

pip install pymysql

Common things across DBs.

Connections

  conn = connect(host="hostname", user="speed", password="Password12")

If successful, a connection object (here conn) is returned

Following method can be called using the connection object.

• conn.close(): Closes the connection to the server.
• conn.commit(): Commits all pending transactions to the database.
• conn.rollback(): Roll back the database to the start of any pending transactions.
• conn.cursor(): Creates a new cursor object that uses the connection. A cursor is an object that you can use to execute SQL queriews and obtain results.

More on Cursor

An instance cur of a cursor has a number of standard methods & attributes.

• cur.close(): Close the cursor, preventing any further operations on it.
• cur.execute(query[, parameters]): Executes a query or command query on the database.
• cur.fetchone(): Returns the next row of the result set produced by execute()
• cur.fetchmany([size]): Returns a sequence of.

SQLite DB

• It is not required to install this module seperately as it being shipped by default with python version 2.5.0 onwards.
• It follows DB.API.2.0.

Executing the SQL queries

import sqlite3 as dfb 
# creating a temporary database
conn = db.connect(":memory:")
# creating cursor
cur = conn.cursor()
query = '''
  CREATE TABLE Student (
    rollno int(3),
    name varchar(10),
    branch varchar(20)
  )
'''

# executing query
cur.execute(query)
print(conn, cur, sep="\n")
cur.close()
conn.close()

Creating a insert query

import sqlite3 as dfb 
# creating a temporary database
conn = db.connect(":memory:")
# creating cursor
cur = conn.cursor()

query = '''
  INSERT INTO Student VALUES 
  (
    100,
    "Speed",
    "CSE"
  )
'''
cur.execute(query)
query = '''
  SELECT * FROM Student
'''
cur.execute(query)
# fetching the records
result = cur.fetchall()
print(result)
cur.close();
conn.close();

for i in range(3):
  rollno = input("Enter Roll no:")
  name = input("Enter Name:")
  branch = input("Enter branch:")

  query = f'''
  INSERT INTO Student
  VALUES (
    {rollno},
    '{name}',
    '{branch}'
  )
  '''
  cur.execute(query)

# File creation & storing permanently in data
conn = db.connect('sms')

# Committing for storing data permanenlty 

conn.commit()

# Retriving data in tuple form

for row in result:
  print(row)

Connecting to MySQL

Task - READ, INSERT, UPDATE, DELETE,

import pymysql as db
conn = db.connect("127.0.0.1", "root", "$niper", "employee")
cur = conn.cursor()

query = '''
  SELECT employee_id, firstname, salary, department_id, from employee 
  WHERE department_id=60 
  ORDER BY employee_id
'''

cur.execute(query)

result = cur.fetchall()

for row in result:
  print(row)

cur.close()
conn.close()

import pymysql as db
conn = db.connect("127.0.0.1", "root", "$niper", "employee")
cur = conn.cursor()

rollno = int(input("Enter roll:"))
name = int(input("Enter name:"))
email = int(input("Enter email:"))
branch = int(input("Enter branch:"))

query = '''
  INSERT INTO employee
  VALUES (
    {rollno},
    '{name}',
    '{email}',
    '{branch}'
  )
'''

cur.execute(query)
conn.commit()

def viewAllRecord():
  query = '''
    SELECT * FROM employee
  '''

  cur.execute(query)
  result = cur.fetchall()

  for row in result:
    print(row)

viewAllRecord()

cur.close()
conn.close()

PRACTICE PROJECTS:

• DIGITAL DIARY
• PROSPECT ENCORE ANALYSIS

Conclusion

In conclusion, the integration of MySQL in Python opens doors to a world of possibilities for developers. From basic queries to advanced operations, this dynamic duo empowers you to build robust, data-driven applications with ease.

FAQs

1. Is MySQL the only database that Python can integrate with?

While MySQL is widely used, Python supports integration with various databases, including PostgreSQL, SQLite, and more.

2. Can I use an ORM (Object-Relational Mapping) with MySQL in Python?

Yes, Python supports popular ORMs like SQLAlchemy, providing a high-level interface for database interactions with MySQL.

3. How can I enhance the security of my MySQL database in a Python application?

Implement best practices such as parameterized queries, input validation, and secure connection handling to bolster MySQL security in Python.

4. Are there any performance considerations when using MySQL in Python?

Optimizing queries, proper indexing, and efficient connection management are crucial for maximizing performance in MySQL integration with Python.

5. Where can I learn more and get hands-on experience with MySQL and Python integration?

Explore tutorials, documentation, and online courses to deepen your understanding and gain practical experience in integrating MySQL with Python.