7 Powerful Databases Python Developers Should Know

Key Takeaways

we all know the usual database choices BOS SQL MySQL mongodb boring in this video I'm going to explore seven database types you may not know about that are really powerful and they're all free sort of you still need to host that somewhere fre database type I'll show you what makes it special and I'll include an example of how to use it in Python but there's one thing I learned from experience that you need to be very careful of otherwise you pay a high price the first database that I'm going to take a look at is influx DB influx DB is a Time series database that's designed for high performance handling of timestamped data this is useful for monitoring Internet of Things applications realtime analytics those kind of things in order to deal with time series data more effectively it doesn't use SQL but a new query language called flux and it has supports for storing time series data task dashboards directly within the database compared with standard databases like BOS SQL or mongodb influx DB is optimized for handling time series datas large volumes of it that makes it very suitable for realtime analytics here's an example of how you can use influx DB in Python so there's a python package called influxdb client that I'm using here this is influxdb 2 there's various versions of influxdb around this is version two and then in this case let's say you're running this database locally but of course you could also be running this somewhere in the cloud so you create a client and then you can write some time series data to the database and influx TV has a specific class for that called point that allows to do that so in this case I have a simple for Loop that just creates 100 data points and then we can tag them with values we can assign values to fields and you also have specific operations for adding time information it's a Time series database after all and then we simply write that point to the database and then once you have this database then you can actually query it using the flux query language and this is what that looks like so it's quite different from SQL but you do see there are some specific operations here that are really helpful for time series data such as picking a range of time or using an aggregate window windowing is something that you typically do with time series data and then finally you can also do things like calculate the meat so actually what all of this does that it computes a moving average of a particular value so that makes a database like influx to be very powerful because you can do these things right in the query and that's not possible in SQL or nosql databases but there's more things you can do with this for example you can also automate tasks so here I'm creating a task that automatically calculates and stores a rolling average every minute so we Define a task so just like the points that's a class that you import from the client and then we give that task a name we also have a flux query that defines what the task actually is supposed to do so in this case we want the task to run every minute and then what we do is that again we compute this rolling average and finally we pass the task to the database VI the API so in short this is a very powerful database offers lots of options that are aimed at time series data on top of let's say the basic SQL or nosql databases however it also introduces some new Concepts like the flux query language in particular that you will need to learn if you want to use this database so there's the trade-off do you want to learn a new query language in order to access that flexibility well that's kind of up to you to decide now choosing the right database it's just one piece of the puzzle in designing robust software if you want to make sure your architectural decisions are solid I've got a free software architecture checklist that can help you it's packed with practical advice to ensure you're covering all the bases when designing your system you can grab it at iron. gold/ checklist the link is also in description don't let these avoidable mistakes slow down your project just get the checklist today the second database that I want to mention is Neo 4G Neo 4G is a graph database so that means it's a database that uses nodes edges properties to represent and store data this structure is actually perfect for applications where relationships between entities are crucial such as social networks or recommendation systems so graph databases like Neo 4G they're really good where relational databases struggles specifically in handling complex relationships at scale here's an example of what using Neo 4G and python looks like so I'm using the Neo 4G package here that's URL of course again you can store this in the cloud or locally and then first we create the driver and of course it needs some authentication this is just um basic example I have a function called add friend that gets a managed transaction that's the way that we interact with the database and we get a name and the name of the friend and there you also see we have a specific query language which is called Cipher so it's again different from SQL but this allows you to specify these types of relationships so in this case we have a friend relationship and we Supply the name and the friend name and then in the main function I create a session I write the transaction that Alice and Bob are friends and then finally you can also do queries with this same language so we can match A and B persons that are friends and then it's going to print the records where A and B are friends so just like with influx DB if you want to use this there is a new query language so expect a learning curve but Neo 4G is really powerful if you have to deal with handling relationships at scale another database that I want to show you is actually pretty ridiculous but before I do that I want to cover one more other database and that is Doc DB docdb is a lightweight inprocess analytical database that's often described as a sort of SQL light for analytics and this is designed for fast analytical queries particular in data science workflows and because of that it can also seamlessly integrate with Panda's data frames which is really cool and perform complex analytical queries Direct ly within your python environment here's an example of using docdb in Python so you need the docdb package to add it to your pip project file but then you can do things that are really cool for example here is an example that queries data directly from a CSV file so I'm running this sort of SQL statement creating a table employees and we're creating that table from a CSV file the CSV file is actually pretty simple there is an employee ID name age department and S and we just have the entries right here but basically what this does is that it now creates the table from this CSV file which is really cool and then when we run that we can actually run queries on it for example we can count the number of employees it's what I'm doing here and as you can see it uses SQL but there are some extensions like being able to do this and that makes doc Tob also pretty easy to use if you already have some experience with SQL queries and databases so this is the first example second example here is that you can integrate doc DP directly with panas data frames so I'm creating a data frame here with employees 100s and I'm simply giving them a name and I also give them a random salary and then just like we created the employees table you can also create it directly from a data frame using a similar syntax which is really cool what you can also do with docdb is that you actually perform a query directly on the data frame object which which is really cool so there's a query. DF function in the docdb module that does this so you pass it the data frame you give that thing a name for for using your queries and then you have the actual queries so I'm simply going to select everybody from the data frame where salary is above 100,000 and then I can turn that again into a data frame and then simply print that let me run this code just to show you what this actually results in so the first thing is that we get the total number of employees which is 20 in this case because those are the employees that come from the CSV file that's this print statement right here so in the second example I performed a query directly on a data frame and then printed the high earners and that's also what you see here so these are all the employees with a salary above $100,000 dollars Euros Bitcoin that would be very high salary then there's more you can do for example I've I've talked about uh window in time series database actually docdb also has support for windowing which is nice so here we have a window query so I'm selecting employee salary I can directly compute the average as part of the query and also do a ranking and this particular query I'm running over the table that I created from the data frame in the beginning so then I get the ranked salaries by executing that query turn it into a data frame and then simply print that and as you can see this is what we get here so we have the average salary and we have a ranking as well and there's more cool stuff that you can do like joining tables or doing complex aggregations for example here I have a department data frame so for each employee I have a department I randomly pick them from engineering HR sales and marketing and then I'm creating the table departments from this data frame just like I did before but then you can do an aggregation query so you can join employees and department and compute the average salary as as well and then you can run this and again turn this into a data frame and then simply print the data frame and then you get this aggregated data of departments and average salaries in the Departments and of course as it's supposed to the engineers make the most money honestly I think doc DB is really cool I might have to do a more in-depth video just covering duck DB if you like me to do that let me know in the comments there's one thing you need to know about drb though is not really designed for large scale production environments High con currency transactions Etc it's really more focused on analytics but it's really powerful for doing exactly that all right so are you ready for a truly ridiculous database the first thing that I'm going to do is generate some base 64 data and then I'll show you what I do with that so I'm going to use the employees. CSV file there we go we have a bunch of Base 64 data now what I'm going to do is I'm going to switch to chat GPT and let's turn Chat GPT into a key value store so we store the value in the database so there you go we can use chat GPT as a key Value Store I've actually created a code example around this as well so here I have a simple open AI interface which is a chatter function that creates an open a client I'm using the GPT 40 Mini model here and then I have my messages so the first line I'm saying that chat gpts is now a key value store whenever I mention a uu ID respond with the data associated with that uu ID and then I have sent chat requests so it append the message to this list of messages so I am actually kind of cheating here because I am storing the queries here as well because unfortunately open ey doesn't have support for sessions yet in the API but when that happens we can actually remove this part and then I get the chat result and then I return that and then I have a fast API app where I'm setting up the open AI chat function I have a store data function that's generates a u ID base 64 encodes the data and then creates The Prompt and then I have a retrieve data function that sends the ID to the model and then Returns the response and base 64 decodes that and then returns that as result then I have a few end points so one for storing data and one for retrieving data so now I've started the API using uvicorn and here I have a new terminal so let's send a cur request and now we get back a uu ID so now let's do a get request with this particular uu ID like so and there we have the data it's amazing so here's a question do you see a use case for a very expensive slow and unpredictable cash let me know in the comments if you don't want to use a slow and expensive cash you could actually use a fast cache which is the other type of database that I want to mention today which is redus so this is an inmemory data structure store that's used typically as a database cache message broker those kind of things and unlike traditional nosql or SQL database redus operates entirely in memory and that makes it it really fast it supports a variety of data structur such as strings hashes list sets sorted sets geospatial indexes even and redus is often used as a caching layer on top of a more traditional database but it can also serve as the primary database for applications where speed is critical so it has extremely fast read and write operations because it's all in memory uh but it's also limited by the memory side that also makes it kind of expensive more expensive than a regular database also redis has adopted a dual license model which the open source Community is not all that happy about so that's another thing you need to be aware of here's an example of using the redis database in Python so I'm importing the redus package for this so I have a couple of helper functions here this to start the redus server I'm running this as a process just so that it's a bit easier to test and we can stop the server as well then what you can do with redus is once you have this redus instance is that you can very easily set realtime data so for example here I'm setting a page views value but then redis can perform Atomic increments like what I'm doing here with extremely low latency and this makes it ideal for real-time data like page views that I have here readus also has task cues built in in traditional database this can be cumbersome because you might have to set up more complex transactions for this and set up some data structure for this with redis that's already built in it also supports sets so you can track unique users so here I'm adding user one two and one to the unique users set but because there is already a user one this will actually not be added here so that makes it very easy with unique data another thing that's nice that's especially useful for cashes is that you can set an expiry date like here I'm setting an expir date here of 1 hour 3600 seconds for this particular session token and then when I get that session token after an hour it's no longer going to be available so you don't need to do additional configurations or things like that it this could be complex in SQL or nosql databases here's another example of what you can do with readus which really cool and that pops up messaging so we have a message Handler function that can do something with a message that it receives in this case just prints it then we have listen to messages that sets up the pops up mechanism so it gets the readus pops up object then it subscribes to my channel and then it listens for messages and calls the message Handler now this listen for messages I'm running that in a separate thread then you can send a message to the channel allow some time for the message to be processed and then finally we stop the thread and when you run this this is the result that you get so readis is incredibly fast and flexible but it does rely on in memory storage and that means it's not suited for use cases where data durability persistence are important for applications where losing data is unacceptable you should use readis only with its persistence options enabled or as a more of a complimentary technology alongside a more standard SQL or nosql database the next database that I want to show you is milus milus is a so-called Vector database that's optimized for storing and querying high-dimensional vectors that makes it ideal for applications in AI machine learning similarity search like I said milus is really designed to handle vectorized data and traditional databases are not optimized for this here's an example of how you can use milus in Python so there's the pi milus package that you could use uh they don't have stop files unfortunately so the typing here is a bit spotty but it is usable at least I'm connecting to the server I'm not supplying any credentials here but of course you should add this if you were to actually connect to a database instance then I Define a schema for a collection so there's a very simple collection example that just has for each ENT an ID and a vector with 128 dimensions then I create a bunch of random vectors and add them to the collection and then I query data so then as you can see you can do specific searches depending on particular metrics and parameters that you define and then for each hit you can print the ID and the distance which is something that milus also computes for you and that's where of course this database is very powerful this is not something that's in your standard SQL database milus is a highly specialized database it's going to be Overkill unless you're really working a lot with vectors in similarity search and if you're already using something like mongodb by the way this also already has support for Vector search so you may not need a specific solution like milus by the way if you enjoy watching these videos about software design architecture python make sure to subscribe to the channel by hitting the Subscribe button hitting the notification Bell and hitting a random person in your vicinity that last one is optional less an intern and you have a mechanical keyboard now final database I want to call is tile 38 this is also a specialized database but it's a geospatial database what is this this allows you to store query analyze geospatial data in real time that makes it ideal for applications such as Fleet Management asset tracking other location based Services now unlike traditional databases which may require complex extensions external tools to handle geospatial database T 38 has this specifically built in so that it can deal with it natively and this supports various types of operations you might want to do like proximity searches Geo fencing and realtime notifications based on location data there's also python library for interacting with tile 38 which is called pile 38 and this is how you use it so we connect to a tile 38 database as you can see tile actually uses the Reedus protocol so that's why there is a redis URL here and then you can do things like adding locations so here is an example of how to do that so I mentioned Fleet Management so we can set the location of various trucks in Fleet using the API directly then you can also create a geofence around a specific area so let's say you have a delivery area and you want to know when a truck enters or leaves that particular area so you define a circle and we detect entering and exiting this area then what you can do is also monitoring that Geo fence so we can subscribe to this particular Geo fence called delivery Zone and then for each message I'm going to print the alert I'm starting a thread to monitor those things and then I simply run my main functions so as you can see this is pretty straightforward and it's a really neat solution for geospatial data now again like most of the other database this is a pretty specialized use case you can't use tile 38 as a general purpose database so it's best use alongside another database that doesn't support geospatial data if you're using a nosql database like mongodb it actually also supports geospatial data already by itself so you may again not need something specific like this and that's also related to one really important thing that I learned so what we've seen today is that each of these databases that I mentioned whether that's a vector database or graph database or a geospatial database they have something unique that could be a game changer for your project right depending on your need and they all have three versions work really well with python that's why I selected them for this video but if you're thinking about adopting some of these databases that's something really important for you to consider which is that every database you add to your architectural design also adds complication you need to manage hosting access restrictions backups implement the interface to the database make sure there are no security risks data leaks and the sorts I remember in one of the first platforms I developed I went completely overboard with databases I had separate database for content for authentication for payment and more it was like complete pain to manage and very expensive as well because each database needed some sort of base level that of course cost money but then you do that times the number of databases that you have and that's something that I've really learned from building platforms and using database that it's way better to start simple don't overdo it limit the number of databases in your project keep it really simple for example I mentioned this before if you use a nosql database like MB this has features such as Vector search and geospatial queries so if that covers your needs just use a single database like mongodb it's way easier to manage and that also goes for caching you may be inclined to use readis immediately as a key value store but perhaps you don't need that extreme speed and low latency for example you could also initially decide to use your existing SQL database and just create a new table that contains cached data and then access that instead of setting up a whole redus instance with the extra cost that comes associated with it so optimize for making things easy to manage if you notice that a bottleneck starts to occur then you can always decide to move off things like caching to a specialized database now I'd like to hear from you have you used any of these uncommon databases before are there others you think should be on the list let me know in the comments now like I mentioned you still need to host these databases somewhere your cloud provider may already offer something similar right out of the box to learn more about the basic elements that a cloud provider offers check out this video next thanks for watching and see you soon