Adventures in Machine Learning

Connecting to Databases with Python SQL Libraries: A Step-by-Step Guide

Connecting to Databases with Python SQL Libraries

If you are someone who is looking to connect to a database using Python, then you have come to the right place. Connecting to a database is a crucial first step in applications that require the ability to read, write and manipulate data.

In this article, we will focus on how to connect to SQLite, MySQL and PostgreSQL using Python SQL libraries.

Connecting to SQLite

SQLite is a lightweight, serverless database that is highly preferred when you need to store small to medium data sets that need to be accessed locally. It is an embedded database that does not require a separate server process and allows quick and easy integration into the application.

To connect to SQLite in Python, SQLite3, a Python SQL library, is used. To connect to an SQLite database in Python, you will need to follow these steps:

1. Installing SQLite3

pip install sqlite3

2. Creating a Connection

import sqlite3
conn = sqlite3.connect('example.db')

3. Creating a Cursor

cursor = conn.cursor()

With these simple steps, you will be able to connect to an SQLite database in Python.

Connecting to MySQL

MySQL is a popular open-source relational database management system that is widely used for web applications. To connect to a MySQL database in Python, you will need to use a Python SQL driver such as PyMySQL or mysql-connector-python.

To connect to a MySQL database in Python, you will need to follow these steps:

1. Installing a MySQL Driver

pip install PyMySQL

or

pip install mysql-connector-python

2. Creating a Connection

import pymysql
conn = pymysql.connect(host='localhost', user='root', password='password', db='test_db')

3. Creating a Cursor

cursor = conn.cursor()

With these simple steps, you will be able to connect to MySQL in Python.

Connecting to PostgreSQL

PostgreSQL is an object-relational database management system that is commonly used in large enterprise settings. It is open-source, and its features include support for ACID transactions, foreign keys, and the ability to handle large amounts of data.

To connect to a PostgreSQL database in Python, you will need to use a Python SQL driver such as psycopg2. To connect to a PostgreSQL database in Python, you will need to follow these steps:

1. Installing psycopg2

pip install psycopg2

2. Creating a Connection

import psycopg2
conn = psycopg2.connect(
            host='localhost',
            database='test_db', 
            user='root', 
            password='password'
        )

3. Creating a Cursor

cursor = conn.cursor()

With these simple steps, you will be able to connect to PostgreSQL in Python.

Creating Tables in Databases

Once you have successfully established a connection to your database of choice, the next logical step would be to create tables. A table is a set of data elements that is organized in a tabular format.

Tables are the backbone of database systems because they store the data that is retrieved, updated, or removed from the database. In this section, we will focus on how to create tables in SQLite, MySQL and PostgreSQL.

Creating Tables in SQLite

When creating tables in SQLite, there are a few guidelines that you need to follow. The first step is to create a connection object as we have seen earlier.

Next, create a cursor object to execute SQL statements using the .cursor() method and call the cursor.execute() method to run the SQL statement. Here is an example of how to create a table in SQLite using Python:

import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# Creating a table
cursor.execute('''CREATE TABLE stocks (date text, trans text, symbol text, qty real, price real)''')

In this example, we create a table named ‘stocks’ that has five columns – ‘date’, ‘trans’, ‘symbol’, ‘qty’ and ‘price’ all with different data types.

Creating Tables in MySQL

To create a table in MySQL, you will need to use the same guidelines that were used for SQLite. The only difference will be the syntax used in the SQL query.

When creating the table, you can also define various constraints such as setting a primary key, defining default values, and specifying data types. Here is an example of how to create a table in MySQL using Python:

import pymysql
conn = pymysql.connect(host='localhost', user='root', password='password', db='test_db')
cursor = conn.cursor()
# Creating a table
cursor.execute('''CREATE TABLE stocks (date TEXT, trans TEXT, symbol TEXT, qty REAL, price REAL, PRIMARY KEY (symbol))''')

In this example, we create a table named ‘stocks’ that has five columns – ‘date’, ‘trans’, ‘symbol’, ‘qty’ and ‘price’.

The PRIMARY KEY clause specifies that the ‘symbol’ column will be the primary key for the table.

Creating Tables in PostgreSQL

Creating tables in PostgreSQL follows the same guidelines that were used for SQLite and MySQL. The only difference will be the syntax used in the SQL query.

import psycopg2
conn = psycopg2.connect(
            host='localhost',
            database='test_db', 
            user='root', 
            password='password'
        )
cursor = conn.cursor()
# Creating a table
cursor.execute('''CREATE TABLE stocks (date TEXT, trans TEXT, symbol TEXT, qty REAL, price REAL, PRIMARY KEY (symbol))''')

In this example, we create a table named ‘stocks’ that has five columns – ‘date’, ‘trans’, ‘symbol’, ‘qty’ and ‘price’.

Conclusion

In this article, we have explored how to connect to SQLite, MySQL and PostgreSQL using their respective Python SQL libraries. We have also seen how to create tables in each of these databases.

I hope you found this article informative and useful. With these skills, you can easily start manipulating data in your database using Python.

Inserting Records into Tables

Once you have created the tables in your database, the next step is to insert records into them. Records are the data entries stored within the tables.

When inserting data into a table, you need to specify the values for each column in the table. In this section, we will look at how to insert records into SQLite, MySQL and PostgreSQL tables.

Inserting Records into SQLite Tables

To insert data into an SQLite table, you need to use the INSERT INTO statement. This statement will allow you to add new records to the table.

You can add a single record at a time, or you can add multiple records at once using the executemany() method. Here’s an example of how to insert records into an SQLite table using Python:

import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# Inserting a single record
cursor.execute("INSERT INTO stocks VALUES ('2022-11-03', 'BUY', 'AAPL', 100, 135.2)")
# Inserting multiple records
data = [('2022-11-05', 'SELL', 'GOOG', 50, 1230.4), ('2022-11-07', 'BUY', 'MSFT', 75, 250.6)]
cursor.executemany('INSERT INTO stocks VALUES (?, ?, ?, ?, ?)', data)
conn.commit()

In this example, we first create a connection object to the database and create a cursor object to execute SQL statements using the .cursor() method. We then insert a single record and multiple records using the cursor.execute() and cursor.executemany() methods, respectively.

Finally, we commit the changes using the commit() method. Inserting Records into MySQL Tables

To insert data into a MySQL table, you can use the same INSERT INTO statement that we used for SQLite.

However, the syntax for the statement will be slightly different. Here’s an example of how to insert records into a MySQL table using Python:

import pymysql
conn = pymysql.connect(host='localhost', user='root', password='password', db='test_db')
cursor = conn.cursor()
# Inserting a single record
cursor.execute("INSERT INTO stocks VALUES ('2022-11-03', 'BUY', 'AAPL', 100, 135.2)")
# Inserting multiple records
data = [('2022-11-05', 'SELL', 'GOOG', 50, 1230.4), ('2022-11-07', 'BUY', 'MSFT', 75, 250.6)]
cursor.executemany('INSERT INTO stocks VALUES (%s,%s,%s,%s,%s)', data)
conn.commit()

In this example, we establish a connection to the database and create a cursor object using the .cursor() method.

We insert a single record and multiple records using cursor.execute() and cursor.executemany(), respectively. Note that the placeholders for values in the executemany() method use the ‘%s’ syntax instead of the ‘?’ syntax used by SQLite.

Finally, we commit the changes using commit(). Inserting Records into PostgreSQL Tables

To insert data into a PostgreSQL table, we use the same INSERT INTO statement as we did for SQLite and MySQL.

But, again, the syntax for this statement in PostgreSQL is slightly different. Here’s an example of how to insert records into a PostgreSQL table using Python:

import psycopg2
conn = psycopg2.connect(
            host='localhost',
            database='test_db', 
            user='root', 
            password='password'
        )
cursor = conn.cursor()
# Inserting a single record
cursor.execute("INSERT INTO stocks VALUES ('2022-11-03', 'BUY', 'AAPL', 100, 135.2)")
# Inserting multiple records
data = [('2022-11-05', 'SELL', 'GOOG', 50, 1230.4), ('2022-11-07', 'BUY', 'MSFT', 75, 250.6)]
cursor.executemany('INSERT INTO stocks VALUES (%s,%s,%s,%s,%s)', data)
conn.commit()

In this example, we create a connection to the test_db database and create a cursor object using .cursor().

We insert a single record and multiple records using cursor.execute() and cursor.executemany() methods, respectively. The placeholders for the values in psycopg2 would also be ‘%s’, similar to MySQL.

Selecting Records from Tables

Selecting records from a table is one of the most important and commonly used operations in databases. It allows you to retrieve specific data from the table that matches certain criteria.

In this section, we will learn how to select records from SQLite, MySQL and PostgreSQL tables. Selecting Records from SQLite Tables

To select records from an SQLite table in Python, you need to use the SELECT statement.

This statement allows you to retrieve data from specific columns of a table based on certain criteria, such as filtering by a specific value or sorting by a specific column. Here’s an example of how to select records from an SQLite table using Python:

import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# Selecting all records
cursor.execute("SELECT * FROM stocks")
rows = cursor.fetchall()
for row in rows:
  print(row)
# Selecting specific records based on a condition
cursor.execute("SELECT * FROM stocks WHERE symbol='AAPL'")
rows = cursor.fetchall()
for row in rows:
  print(row)

In this example, we first create a connection object to the database and create a cursor object to execute SQL statements using the .cursor() method. We then select all records from the ‘stocks’ table using the SELECT statement and fetch all the results using the fetchall() method.

We finally print out all the rows using a for loop. We do the same for selecting specific records based on the condition that the ‘symbol’ column equals ‘AAPL’.

Selecting Records from MySQL Tables

To select records from a MySQL table, you need to use the same SELECT statement that we used for SQLite. However, the syntax of the statement will be slightly different.

Here’s an example of how to select records from a MySQL table using Python:

import pymysql
conn = pymysql.connect(host='localhost', user='root', password='password', db='test_db')
cursor = conn.cursor()
# Selecting all records
cursor.execute("SELECT * FROM stocks")
rows = cursor.fetchall()
for row in rows:
  print(row)
# Selecting specific records based on a condition
cursor.execute("SELECT * FROM stocks WHERE symbol='AAPL'")
rows = cursor.fetchall()
for row in rows:
  print(row)

In this example, we establish a connection to the database and create a cursor object using the .cursor() method. We then select all records from the ‘stocks’ table and fetch all the results using fetchall().

We do the same for selecting specific records based on the condition that the ‘symbol’ column equals ‘AAPL’. Selecting Records from PostgreSQL Tables

To select records from a PostgreSQL table in Python, we use the same SELECT statement as we did for SQLite and MySQL.

But, again, the syntax for the statement is slightly different. Here’s an example of how to select records from a PostgreSQL table using Python:

import psycopg2
conn = psycopg2.connect(
            host='localhost',
            database='test_db', 
            user='root', 
            password='password'
        )
cursor = conn.cursor()
# Selecting all records
cursor.execute("SELECT * FROM stocks")
rows = cursor.fetchall()
for row in rows:
  print(row)
# Selecting specific records based on a condition
cursor.execute("SELECT * FROM stocks WHERE symbol='AAPL'")
rows = cursor.fetchall()
for row in rows:
  print(row)

In this example, we create a connection to the test_db database and create a cursor object using .cursor().

We select all records from the ‘stocks’ table and fetch all the results using the fetchall() method. We then print all the rows, and do the same for selecting specific records based on the condition the ‘symbol’ column equals ‘AAPL’.

Conclusion

In this article, we have examined how to insert records into tables and select records from tables using Python SQL libraries. We saw how the same basic principles apply to all three databases – SQLite, MySQL, and PostgreSQL.

By following the guidelines outlined in this article, you should be able to insert and select record data from your database tables effectively from Python. Updating Records in Tables

Updating records in a database is necessary when you want to make changes to the existing data.

For instance, you may want to update a customer’s address, an employee’s salary, or any other record in the system. In this section, we will cover how to update records in SQLite, MySQL, and PostgreSQL databases using Python SQL libraries.

Updating Records in SQLite Tables

To update data in an SQLite table, you need to use the UPDATE statement along with the SET clause. The UPDATE statement allows you to update existing records, while the SET clause lets you specify the particular columns and their new values.

Here’s an example of how to update records in an SQLite table using Python:

import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# Updating a single record
cursor.execute("UPDATE stocks SET price=137.5 WHERE symbol='AAPL' AND qty=100")
# Updating multiple records
data = [(130, 'GOOG'), (240, 'MSFT')]
cursor.executemany("UPDATE stocks SET price = ? WHERE symbol = ?", data)
conn.commit()

In this example, we create a connection object to the database and then update a single record where the stock symbol is ‘AAPL’ and the quantity is 100. We then update multiple records by providing a list of tuples, each containing the new price and symbol.

Finally, we commit the changes using the commit() method. Updating Records in MySQL Tables

To update records in a MySQL table, you can use the same UPDATE statement that we used for SQLite.

The syntax will be slightly different, however. Here’s an example of how to update records in a MySQL table using Python:

import pymysql
conn = pymysql.connect(host='localhost', user='root', password='password', db='test_db')
cursor = conn.cursor()
# Updating a single record
cursor.execute("UPDATE stocks SET price=137.5 WHERE symbol='AAPL' AND qty=100")
# Updating multiple records
data = [(130, 'GOOG'), (240, 'MSFT')]
cursor.executemany("UPDATE stocks SET price = %s WHERE symbol = %s", data)
conn.commit()

In this example, we establish a connection to the database and create a cursor object using the .cursor() method.

We then update a single record where the stock symbol is ‘AAPL’ and the quantity is 100. We then update multiple records by providing a list of tuples, each containing the new price and symbol. Note that the placeholders in the executemany() method are ‘%s’ for MySQL.

Finally, we commit the changes using commit(). Updating Records in PostgreSQL Tables

To update records in a PostgreSQL table, we use the same UPDATE statement as we did for SQLite and MySQL.

The syntax for this statement in PostgreSQL is slightly different, however. Here’s an example of how to update records in a PostgreSQL table using Python:

import psycopg2
conn = psycopg2.connect(
            host='localhost',
            database='test_db', 
            user='root', 
            password='password'
        )
cursor = conn.cursor()
# Updating a single record
cursor.execute("UPDATE stocks SET price=137.5 WHERE symbol='AAPL' AND qty=100")
# Updating multiple records
data = [(130, 'GOOG'), (240, 'MSFT')]
cursor.executemany("UPDATE stocks SET price = %s WHERE symbol = %s", data)
conn.commit()

In this example, we create a connection to the test_db database and create a cursor object using .cursor().

We then update a single record where the stock symbol is ‘AAPL’ and the quantity is 100. We then update multiple records by providing a list of tuples, each containing the new price and symbol. Note that the placeholders in the executemany() method are ‘%s’ for PostgreSQL, similar to MySQL.

Finally, we commit the changes using commit(). Deleting Records from Tables

Deleting records from a database is another essential operation that allows you to remove unwanted or outdated data.

To delete records from a table, you need to use the DELETE statement. This statement allows you to delete specific records based on certain criteria. In this section, we will cover how to delete records from SQLite, MySQL, and PostgreSQL databases using Python SQL libraries.

Deleting Records in SQLite Tables

To delete records from an SQLite table, you need to use the DELETE statement along with the WHERE clause. The DELETE statement allows you to remove records from the table, while the WHERE clause lets you specify the specific records to delete.

Here’s an example of how to delete records from an SQLite table using Python:

import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# Deleting a single record
cursor.execute("DELETE FROM stocks WHERE symbol='AAPL' AND qty=100")
# Deleting multiple records
cursor.execute("DELETE FROM stocks WHERE symbol='GOOG'")
conn.commit()

In this example, we create a connection object to the database and then delete a single record where the stock symbol is ‘AAPL’ and the quantity is 100. We then delete all records where the stock symbol is ‘GOOG’.

Finally, we commit the changes using the commit() method. Deleting Records in MySQL Tables

To delete records from a MySQL table, you can use the same DELETE statement that we used for SQLite.

The syntax will be slightly different, however. Here’s an example of how to delete records from a MySQL table using Python:

import pymysql
conn = pymysql.connect(host='localhost', user='root', password='password', db='test_db')
cursor = conn.cursor()
# Deleting a single record
cursor.execute("DELETE FROM stocks WHERE symbol='AAPL' AND qty=100")
# Deleting multiple records
cursor.execute("DELETE FROM stocks WHERE symbol='GOOG'")
conn.commit()

In this example, we establish a connection to the database and create a cursor object using the .cursor() method.

We then delete a single record where the stock symbol is ‘AAPL’ and the quantity is 100. We then delete all records where the stock symbol is ‘GOOG’.

Finally, we commit the changes using commit(). Deleting Records in PostgreSQL Tables

To delete records from a PostgreSQL table, we use the same DELETE statement as we did for SQLite and MySQL.

The syntax for this statement in PostgreSQL is slightly different, however. Here’s an example of how to delete records from a PostgreSQL table using Python:

import psycopg2
conn = psycopg2.connect(
            host='localhost',
            database='test_db', 
            user='root', 
            password='password'
        )
cursor = conn.cursor()
# Deleting a single record
cursor.execute("DELETE FROM stocks WHERE symbol='AAPL' AND qty=100")
# Deleting multiple records
cursor.execute("DELETE FROM stocks WHERE symbol='GOOG'")
conn.commit()

In this example, we create a connection to the test_db database and create a cursor object using .cursor().

We then delete a single record where the stock symbol is ‘AAPL’ and the quantity is 100. We then delete all records where the stock symbol is ‘GOOG’.

Finally, we commit the changes using commit(). Conclusion

In this article, we have explored how to connect to SQLite, MySQL, and PostgreSQL databases using Python SQL libraries. We have also seen how to create tables, insert records, select records, update records, and delete records from these databases.

With these skills, you can effectively manipulate data in your databases using Python.

Popular Posts