Create a Database for Your Python App

Most Python applications need a reliable, efficient way to store and retrieve data—in other words, a database.

Using Databases in Python

Let’s take a look at databases in the world of Python. We’ll look at some of the most common databases used by Python apps, a few concepts to help you decide which database you should use, and finally, we’ll dive into some details of how to create a database using MongoDB Atlas and how to connect to and retrieve data from your database.

This article covers:

Most Common Databases for Python Web Apps

When building a Python application that needs a database, you’ll find that there are a number of excellent options. Here are some of the most common ones:

  • SQLite: This is a small, serverless relational database that is built into Python. It’s used by importing the sqlite3 library into your application. As an embedded database, it's not really suitable for large web applications.
  • MySQL: This is a widely used and known open-source relational database that runs as a server.
  • PostgreSQL: This is another popular open-source relational database that focuses on extensibility and enterprise features.
  • MongoDB: This is a document-oriented modern database. Instead of storing records in tables like a relational database, it stores JSON-like documents in collections.

What Database to Use With Python

With so many options for databases, which solution is best for your application? Which you choose depends on your use case.

Relational databases like MySQL and PostgreSQL can be a good choice if you’re dealing with data models that have a fixed structure (think: “tables”)—that is, the set of attributes for any given model changes rarely. In practice, this is very unusual while an application is under active development. A relational database, as you might expect, is also often the best choice for highly interrelated data.

For example, consider a school database to manage teachers, students, and courses. Teachers will have IDs, names, basic contact information, and maybe a role (“homeroom,” “substitute”). Students will have a similar set of attributes. Courses might have titles, descriptions, start and end dates, and so on. Most likely, though, these sets of attributes for each model will not change very much for the purposes of your application, and it’s pretty likely that each record will have all of their attributes.

This type of data structure lends itself well to management by a relational database system (like MySQL or PostgreSQL). For smaller-scale applications, or for local testing, SQLite is also a good option to use.

When your data structure might be more fluid, with a schema that is less fixed, a document database like MongoDB might be more appropriate. With their document-oriented (as opposed to table-oriented) structure, document databases support a varying data structure easily; some records might have certain attributes, while other records don’t have those attributes. Non-relational databases like MongoDB can store large amounts of data with varying structures. It’s easily scalable, flexible, and relatively simple for beginners. Although MongoDB supports modeling and querying relationships between documents, it really excels when you wish to embed related data within a document itself.

For our tutorial, let’s take a look at how you might import data from a cryptocurrency API into a MongoDB collection using Python. To see how easy it is to get started with Python, let’s first create a database on MongoDB Atlas.

How to Create a Database in Your Python App Using MongoDB Atlas

To get started on using MongoDB, you’ll first need to sign up for a free MongoDB Atlas account here. Once you have created your account, you will be prompted to name your organization, name your project, and choose the language for code samples and help.

image of MongoDB Atlas account set up screen


Next, choose the type of account you need.

image of MongoDB Atlas path selection

I chose the free option for this example. It’s worth noting that the free tier here remains free, as opposed to other products which might offer a free trial period only.


Next, create a cluster. Unless you want to modify the cluster, you can choose the default and click Create Cluster. image of MongoDB Atlas cluster creation


It will take a few minutes for the cluster to provision. Once complete, you will see a screen like below: image of MongoDB Atlas cluster


Click on the Connect button to start setting up your connection. Here you will have to add your local IP address and create a user for your database. The IP address will auto-populate with your local IP address. Add a description if you want and click Add IP Address. Then add a Username and Password and click Create Database User. After that, click the Choose a connection method button, which will now be active.

image of MongoDB Atlas cluster connection


The next screen will give you the option to choose how you will connect to your new database. Since you are going to be connecting with Python, choose Connect your application. image of MongoDB Atlas cluster connection set up


Choose Python as a language and your version of Python, and you will be presented with a connection string. You will have to replace <password> in this string with your actual password and <dbname> with the name you will be giving your database. You will be adding this database connection string to your Python scripts later. image of MongoDB Atlas cluster connection finalization

How to Connect to a Database in Python

Now, let's create a Python script that will connect to the CoinGecko API—an open API for cryptocurrency prices—and store that data in our database. First, be sure to install all the Python packages you need for the script using Python version 3.11 or later. Run the command below in a terminal to install those packages.

pip install pymongo[srv] requests

The requests package will enable you to make the API call. The pymongo package is a library that makes it easy to interact with a MongoDB database in Python. After that, you can create the script like below:

# Import the libraries we need
from pymongo import MongoClient
import requests

# Connect to the database with the connection string we got from Atlas, replacing user and password.
client = MongoClient('mongodb+srv://myUser:password@cluster0.sqdyt.mongodb.net/test?retryWrites=true&w=majority')

# Next we define the database we are using.
# It does not have to exist first, like with relational databases.
db = client.get_database('coin_markets')

# Now, we make the API call and prices the results to the terminal.
prices = requests.get('https://api.coingecko.com/api/v3//coins/markets?vs_currency=usd&order=market_cap_desc&per_page=100&page=1&sparkline=false')
prices = prices.json()
print(prices)

# We define the collection we will store this data in,
# which is created dynamically like the database,
# and insert the data into the collection.
db_prices = db.get_collection('prices')
inserted = db_prices.insert_many(prices)
# Print a count of documents inserted.
print(str(len(inserted.inserted_ids)) + " documents inserted")

After you run the script, you will see an output in the terminal like the following:

[{'id': 'bitcoin', 'symbol': 'btc', 'name': 'Bitcoin', 'image': 'https://assets.coingecko.com/coins/images/1/large/bitcoin.png?1547033579', 'current_price': 55645, 'market_cap': 1036847905457, 'market_cap_rank': 1, 'fully_diluted_valuation': 1168542287375, 'total_volume': 70405393543, 'high_24h': 56259, 'low_24h': 50907, 'price_change_24h': 3793.73, 'price_change_percentage_24h': 7.31657, 'market_cap_change_24h': 69931519176, 'market_cap_change_percentage_24h': 7.23243, 'circulating_supply': 18633306.0, 'total_supply': 21000000.0, 'max_supply': 21000000.0, 'ath': 56259, 'ath_change_percentage': -1.09187, 'ath_date': '2021-02-19T21:30:45.381Z', 'atl': 67.81, 'atl_change_percentage': 81961.18779, 'atl_date': '2013-07-06T00:00:00.000Z', 'roi': None, 'last_updated': '2021-02-19T22:39:36.735Z'}, ...]
100 Documents Inserted

When you go back to MongoDB Atlas and click on the Collections tab, you will see the data you just inserted into your database. You will find the primary key that was generated for each record in the _id field.

image displaying data within cluster

How to Get Data from a Database in Python

The script to retrieve data from your database will be similar.

# Import the only library we need.
from pymongo import MongoClient

# Connect to the MongoDB database using our connection string.
client = MongoClient('mongodb+srv://myUser:passwords@cluster0.sqdyt.mongodb.net/test?retryWrites=true&w=majority')

# Connect to the coin_markets database and the prices collection.
db = client.get_database('coin_markets')
db_prices = db.get_collection('prices')

# Search for records where the price_change_24h value is greater than 1000, loop the results, and print them to the terminal.
for doc in db_prices.find({"price_change_24h": {"$gt": 1000} }):
    print(doc)

When we run this, we will see our documents print out in the console:

{'_id': ObjectId('603043733dde30d2c55cbe9e'), 'id': 'bitcoin', 'symbol': 'btc', 'name': 'Bitcoin', 'image': 'https://assets.coingecko.com/coins/images/1/large/bitcoin.png?1547033579', 'current_price': 55681, 'market_cap': 1039423646069, 'market_cap_rank': 1, 'fully_diluted_valuation': 1171444429137, 'total_volume': 70691539006, 'high_24h': 56259, 'low_24h': 50907, 'price_change_24h': 3821.43, 'price_change_percentage_24h': 7.36877, 'market_cap_change_24h': 75347439610, 'market_cap_change_percentage_24h': 7.81551, 'circulating_supply': 18633318.0, 'total_supply': 21000000.0, 'max_supply': 21000000.0, 'ath': 56259, 'ath_change_percentage': -1.02725, 'ath_date': '2021-02-19T21:30:45.381Z', 'atl': 67.81, 'atl_change_percentage': 82014.80834, 'atl_date': '2013-07-06T00:00:00.000Z', 'roi': None, 'last_updated': '2021-02-19T22:59:31.408Z'}...

Note: Although we added connection information directly to our code in this example, you would never do this in a real-world scenario. Always use an environment variable or configuration file so that you don’t end up committing sensitive information to version control.

Python Database Libraries

The pymongo package made it pretty easy to interact with MongoDB in Python, but there are a few other libraries you might want to look at, depending on what you are trying to do. Here is a list of popular libraries for using MongoDB with Python:

  • Pymongo: Pymongo is the library we used in this example. It provides a basic set of tools for interacting with a MongoDB database.
  • Arctic: Arctic is a time series and data frame database that sits on top of MongoDB. It adds features that come in handy when dealing with time-based data, like the results from a stock ticker.
  • Motor: Motor is an asyncio library that allows non-blocking access to MongoDB.

Conclusion

Depending on the data modeling needs for your Python application, you might find that a document database will serve you well. When that’s the case, MongoDB might lend you the ease and speed of setup to get you up and running quickly. The flexibility and scalability of the database will keep your application running smoothly as your user base grows. What’s more, you already have at your fingertips several robust packages that make connecting Python with MongoDB straightforward. All of this will help you take your Python application to the next level.

FAQs

Which database is used for Python?

There are many databases that you can use with Python. SQLite is a database that is built-in, but you can also find packages that will allow you to use MongoDB, PostgreSQL, MySQL, Microsoft SQL Server, or Oracle as a database for your application.

Can I create a database in Python?

Yes, there are multiple ways to create a database in Python. One easy way is to create an SQLite database with the built-in sqlite3 package.

Can you use Python with SQL?

Yes, you can execute SQL with Python. You could do this with an ORM library like SQLAlchemy that abstracts away the syntax of your SQL query into Python syntax, or with lower-level libraries where you must write your own SQL statements.

Should I learn Python or SQL first?

You should probably learn Python first unless you are planning on being a database developer. SQL is the language that relational databases use. By learning Python first, you can use one of the Python packages available that make SQL easier and then pick up SQL knowledge as you go.

What can Python do that SQL cannot?

Python is a programming language used to build web applications, services, desktop applications, scripts, and more. SQL is a language that interacts with databases. You cannot use it to create a complete application.