Working with databases in Python

    python-logo

    Python is a popular programming language that can be used to work with databases. In this article, we will explore how to work with databases in Python, including connecting to a database, performing CRUD operations, and using an ORM.

    Connecting to a database

    In order to work with a database in Python, we need to connect to it. There are various Python libraries available to connect to different types of databases. Here is an example of connecting to a MySQL database using the mysql-connector-python library:

    import mysql.connector

# connect to the database
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

# create a cursor object
mycursor = mydb.cursor()

# execute a SQL query
mycursor.execute("SELECT * FROM customers")

# fetch all the results
results = mycursor.fetchall()

# print the results
for result in results:
  print(result)

    In this example, we connect to a MySQL database, create a cursor object, execute a SQL query, fetch all the results, and print them.

    Performing CRUD operations

    Once we have connected to a database, we can perform CRUD (Create, Read, Update, Delete) operations on it. Here is an example of inserting a record into a MySQL database:

    # create a new record
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)

# commit the changes
mydb.commit()

# print the ID of the inserted record
print("Record inserted, ID:", mycursor.lastrowid)

    In this example, we insert a new record into the customers table and print the ID of the inserted record.

    Using an ORM

    An ORM (Object-Relational Mapping) is a technique that allows us to work with databases using object-oriented programming concepts. There are various ORM libraries available in Python, such as SQLAlchemy and Django ORM. Here is an example of using the Django ORM to retrieve all the records from a table:

    from myapp.models import Customer

# retrieve all the customers
customers = Customer.objects.all()

# print the customers
for customer in customers:
  print(customer.name, customer.address)

    In this example, we import the Customer model from the myapp application, retrieve all the customers using the all() method, and print them.

    Conclusion

    In this article, we have explored how to work with databases in Python. We learned how to connect to a database using various Python libraries, perform CRUD operations on it, and use an ORM to work with databases using object-oriented programming concepts. With these techniques, we can work with databases in Python and build powerful database-driven applications.