In this article we want to talk about Python MySQL vs SQLite, so when it comes to databases in Python, there are two popular options that you can use, we have SQLite and also MySQL. these databases have their own strengths and weaknesses, and if you want to choose between them, then it will depend on your specific needs. in this article, we want to talk about differences between Python SQLite and MySQL and also we will learn that which one is good for your project.
Python SQLite
SQLite is lightweight and file based database engine, it is add by default in Python. it is used for small to medium sized applications and it is often used for mobile apps, desktop software and web applications that don’t require heavy database usage. SQLite is easy to set up and the usage is also simple, it is one of the best choice for applications with limited resources.
These are some pros and cons of using SQLite:
Pros:
- Easy to set up and use
- No need for a separate database server
- Supports all major platforms
- Good for small to medium sized applications
Cons:
- Not good for high traffic applications
- Limited support for concurrent connections
- Limited scalability
- Limited security features
How to Use SQLite in Python?
Using SQLite in Python is easy, because we have built in sqlite3 module in Python, and it is Python standard library. for working with SQLite and Python we need to follow these steps:
First, we need to import the sqlite3 module.
|
1 |
import sqlite3 |
After that we need create a connection to SQLite database file. If the file does not exist, SQLite will create it for you.
|
1 |
conn = sqlite3.connect('example.db') |
Also you can cursor object in here, a cursor object allows you to execute SQL commands.
|
1 |
cur = conn.cursor() |
Use the cursor to execute a SQL command to create a table.
|
1 2 3 4 5 |
cur.execute('''CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY, name TEXT, age INTEGER )''') |
After that, you can insert data into the table using the INSERT INTO statement.
|
1 2 |
cur.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Codeloop', 30)) cur.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Parwiz', 25)) |
For saving the changes, you need to commit the transaction.
|
1 |
conn.commit() |
You can retrieve data from the table using the SELECT statement.
|
1 2 3 4 |
cur.execute("SELECT * FROM users") rows = cur.fetchall() for row in rows: print(row) |
And lastly we need to close the cursor and connection to free up resources.
|
1 2 |
cur.close() conn.close() |
This is is the complete example that includes all above steps:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
import sqlite3 # Connect to the database (creates the file if it does not exist) conn = sqlite3.connect('example.db') # Create cursor object cur = conn.cursor() # Create table cur.execute('''CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY, name TEXT, age INTEGER )''') # Insert data cur.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Codeloop', 30)) cur.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Parwiz', 25)) # Commit changes conn.commit() # Query database cur.execute("SELECT * FROM users") rows = cur.fetchall() for row in rows: print(row) # Close the cursor and connection cur.close() conn.close() |
Now the data is added in the database and this is the selecting process of the data.
Python MySQL
MySQL is popular relational database management system, and it is used in web applications, including content management systems, ecommerce platforms and social networks. it is designed for large scale applications and it can handle thousands of concurrent connections. MySQL supports advanced security features, such as encryption and user management.
These are some of the pros and cons of using MySQL:
Pros:
- Supports thousands of concurrent connections
- Advanced security features
- Scalable and flexible
- Supports different platforms and programming languages
Cons:
- More complex to set up and use than SQLite
- Requires a separate database server
- May require more resources than SQLite
- Requires more knowledge of SQL
How to Use MySQL in Python?
Using MySQL in Python requires an additional library, typically mysql-connector-python or PyMySQL. This is a guide on how to use MySQL with Python, including installing the required package, connecting to a MySQL database, creating tables, inserting data, querying data and closing the connection.
First of all you need to install MySQL connector for Python. You can use pip to install mysql-connector-python.
|
1 |
pip install mysql-connector-python |
Also, you can use PyMySQL if you want:
|
1 |
pip install pymysql |
Import mysql.connector module or pymysql module depending on what you installed.
|
1 |
import mysql.connector |
You can create a connection to the MySQL database by providing the necessary connection parameters such as host, user, password and database name.
|
1 2 3 4 5 6 |
conn = mysql.connector.connect( host="localhost", user="root", password="", database="codeloop" ) |
A cursor object allows you to execute SQL commands.
|
1 |
cur = conn.cursor() |
Use cursor to execute a SQL command to create a table.
|
1 2 3 4 5 |
cur.execute('''CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT )''') |
You can insert data into the table using INSERT INTO statement.
|
1 2 |
cur.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ('Codeloop', 10)) cur.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ('Bob',20)) |
For saving the changes, you need to commit the transaction.
|
1 |
conn.commit() |
You can retrieve data from the table using SELECT statement.
|
1 2 3 4 |
cur.execute("SELECT * FROM users") rows = cur.fetchall() for row in rows: print(row) |
And lastly, close the cursor and connection to free up resources.
|
1 2 |
cur.close() conn.close() |
This is a complete example that includes all above steps using mysql-connector-python:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
import mysql.connector # Connect to the database conn = mysql.connector.connect( host="localhost", user="root", password="", database="codeloop" ) # Create a cursor object cur = conn.cursor() # Create a table cur.execute('''CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT )''') # Insert data cur.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ('Codeloop', 10)) cur.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ('Bob', 20)) # Commit the changes conn.commit() # Query the database cur.execute("SELECT * FROM users") rows = cur.fetchall() for row in rows: print(row) # Close the cursor and connection cur.close() conn.close() |
This will be the result
Python SQLite vs MySQL – Which One to Use ?
The choice between SQLite and MySQL will depend on the specific requirements of your project. if you are building small to medium sized application and want to keep things simple, then SQLite may be the best option. because it is easy to use, lightweight and can be embedded in your Python application. but if you want to build large application that requires advanced security features and can handle thousands of concurrent connections, then MySQL will be a better choice.
Subscribe and Get Free Video Courses & Articles in your Email