Blog

Connecting to databases e.g, SQLite, MySQL

In the Python programming language, external databases are used to import or store data, organize databases in a particular order and sequence, and manage them in a secondary storage location. Python programming can properly connect to and import various database software currently available. This allows Python users to create new data in a program, read existing data, update old database tables, and apply CRUD operations (delete operations) if needed.

Two popular databases commonly used with Python programming are SQLite and MySQL.

What is a database?

A database is a sequentially organized collection of data and information stored in a computerized digital row-column order format.

For example, a company might store digital tabular information about employees.

Instead of storing the above database information directly in a Python variable, Python users can store it as a database in database software.

A common database software allows Python users to.

• Store data and information in large-volume databases
• Search for data record information in an existing database
• Update existing database data when needed
• Delete data information from a database
• Organize database table data information in a tabular format
• Extract the desired data information when needed

Python Programming and Database Software.

The Python programming language provides multiple individual libraries and modules for Python user programmers to connect to a database. For example,

• SQLite → sqlite3
• MySQL → MySQL Connector/Python or another MySQL-compatible driver
• PostgreSQL → PostgreSQL driver such as psycopg

A common database data connection process in Python programming is the Python program.

Python Program

↓

Selected Database Driver

↓

Desire Database

↓

Finalized Data

Connecting the Python programming language to an SQLite database.

• SQLite is a lightweight, easy-to-use relational database management application software.
• One of the best advantages of SQLite database software is that Python users don’t need a separate database server to access SQLite database data. Database data in SQLite is typically stored in a single file.
• The Python programming language includes the sqlite3 module package in its standard library, so you generally don’t need a separate package library or module.

Step 1: Import sqlite3 into Python

import sqlite3

Step 2: Create a new database connection

connection = sqlite3.connect(“employee.db”)

If the employee.db database file does not exist in this connection method, the SQLite software will automatically create it.

Step 3: Create a sqlite3 database cursor

In SQLite database software, cursors are used to execute or run SQL statements.

cursor = connection.cursor()

Creating a SQLite3 Database Table.

In the Python programming language, we can create the employee table using the SQL database software.

cursor.execute(“””

CREATE TABLE IF NOT EXISTS Employee (

Emp_id INTEGER PRIMARY KEY,

Emp_name TEXT,

Department TEXT,

Salary INTEGER

)

“””)

connection.commit()

Here, four columns are defined in the Employee database table.

• Emp_id → A unique identification number for each employee
• Emp_name → The employee’s name in the database table
• Department → The employee’s current department column
• Salary → The employee’s salary column in the Employee table

Inserting data into the SQL Employee database table.

Here, we can manually enter employee data information into SQL database software.

cursor.execute(

“INSERT INTO Employee (Emp_id, Emp_name, Department, Salary) VALUES (?, ?, ?, ?)”,

(101 “Bhavishi Deora”, “Sales”, 99999)

)

connection.commit()

The ? placeholders in the database import are important here, as they help pass the values ​​individually instead of concatenating strings to create an SQL statement.

Reading database data from SQLite.

To retrieve table data information from an SQLite database.

cursor.execute(“SELECT * FROM Employee”)

Employee = cursor.fetchall()

for Employee in Employee:

print(Employee)

Possible Employee database table output.

(101 “Bhavishi Deora”, “Sales”, 99999)

Here, the fetchall() function in the database table returns all table row values ​​returned by the query.

Python users can also use this instead.

cursor.fetchone()

This is for displaying Employee table data one at a time.

Updating the Employee database table data.

Here, in the Employee table, let’s say Bhavishi Deora’s salary increases from 99999 to 100000.

So, we can update the table records as needed.

cursor.execute(

“UPDATE Employee SET Department = ? WHERE name = ?”,

(100000, “Bhavishi Deora”)

)

connection.commit()

Deleting Employee Database Table Data.

Here, you can use this method to delete a record in the Employee database table.

cursor.execute(

“DELETE FROM Employee WHERE name = ?”,

(“Bhavishi Deora”,)

)

connection.commit()

Closing the SQL database connection.

After finishing all data operations in the Employee database table, the data connection to the currently open database should be closed.

connection.close()

A complete simple SQLite program for a SQL database table might look like this.

import sqlite3

connection = sqlite3.connect(“Employee.db”)

cursor = connection.cursor()

cursor.execute(“””

CREATE TABLE IF NOT EXISTS Employee (

Emp_id INTEGER PRIMARY KEY,

Emp_name TEXT,

Department TEXT,

Salary INTEGER

)

“””)

cursor.execute(

“INSERT INTO Employee (Emp_id, Emp_name, Department, Salary) VALUES (?, ?, ?, ?)”,

(101 “Bhavishi Deora”, “Sales”, 99999)

)

connection.commit()

cursor.execute(“SELECT * FROM Employee”)

for Employee in cursor.fetchall():

print(Employee)

connection.close()

python Programming Connecting to MySQL Database Software.

Like SQLite, MySQL database software is also a popular client-server relational database management system software.

Unlike SQLite database software, MySQL typically runs as a database server. A Python software application connects to that server using the MySQL database driver.

A commonly used database option is MySQL Connector/Python.

Installing the Connector for MySQL Database Software.

Using the pip Python command.

pip install mysql-connector-python

This can be used by Python users, depending on the Python software version installed on your current system.

python -m pip install mysql-connector-python

Creating a MySQL Database Connection for Python.

After installing the connector in the Python programming language, you can follow the command syntax below.

import mysql.connector

connection = mysql.connector.connect(

host=”localhost”,

user=”root”,

password=”your_password”,

database=”employee”

)

print(“MySQL Database is properly connected with python”)

Here in MySQL Database Connection.

• host → This is the localhost location of the MySQL server containing the currently connected database.
• user → This is the username for the MySQL database.
• password → This is the password for the MySQL database.
• database → This is the name of the database table in MySQL to connect to.

Never hard-code your real password in a MySQL database production application. Use proper MySQL database configuration or secret-management methods.

Creating a MySQL database cursor in Python.

cursor = connection.cursor()

Here, the cursor allows you to send SQL commands to the MySQL database in Python programming.

MySQL database cursor example.

cursor.execute(“””

CREATE TABLE IF NOT EXISTS Employee (

Emp_id INT AUTO_INCREMENT PRIMARY KEY,

Emp_name VARCHAR(130),

Department VARCHAR(140),

Salary INT

)

“””)

connection.commit()

Inserts data into a MySQL database table.

sql = “””

INSERT INTO Employee (Emp_id, Emp_name, Department, Salary)

VALUES (%s, %s, %s, %s)

“””

values ​​​​= (102 “Siddhi Deora”, “Marketing”, 100000)

cursor.execute(sql, values)

connection.commit()

In this example, the %s placeholder symbol is used to perform queries from the parameterized MySQL database connector.

Reading data from a MySQL database table.

cursor.execute(“SELECT * FROM Employee”)

for Employee in cursor.fetchall():

print(Employee)

Possible output of a MySQL database table.

(102 “Siddhi Deora”, “Marketing”, 100000)

Main Difference Between SQLite vs. MySQL Database Software.

Conclusion of Connecting to Databases, e.g., SQLite, MySQL.

Database connectivity in the Python programming language makes working with database tables easy.

For SQLite database connectivity, Python provides the built-in sqlite3 module.

import sqlite3

For MySQL database connectivity in the Python programming language, connectors like MySQL Connector/Python are used.

import mysql.connector

A basic database workflow for database connectivity in the Python programming language is as follows.

Connect → Create cursor → Execute SQL → Commit changes → Retrieve results → Close connection