<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Vivienne Ordonez on Medium]]></title>
        <description><![CDATA[Stories by Vivienne Ordonez on Medium]]></description>
        <link>https://medium.com/@vivienne.ordonez?source=rss-b86fc846a77c------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*WRYkApsfE6cVpPKJ5gDdSA.jpeg</url>
            <title>Stories by Vivienne Ordonez on Medium</title>
            <link>https://medium.com/@vivienne.ordonez?source=rss-b86fc846a77c------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 21:42:36 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@vivienne.ordonez/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Using SQL with Python]]></title>
            <link>https://medium.com/@vivienne.ordonez/using-sql-with-python-81e46afa6ad5?source=rss-b86fc846a77c------2</link>
            <guid isPermaLink="false">https://medium.com/p/81e46afa6ad5</guid>
            <category><![CDATA[sql-server]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[sqlite3]]></category>
            <dc:creator><![CDATA[Vivienne Ordonez]]></dc:creator>
            <pubDate>Tue, 23 May 2023 13:55:51 GMT</pubDate>
            <atom:updated>2023-05-23T13:55:51.302Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*enP3L_nmnFftCw9a" /><figcaption>Photo by <a href="https://unsplash.com/@casparrubin?utm_source=medium&amp;utm_medium=referral">Caspar Camille Rubin</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>Hi! In this article, we’ll be looking at how to use SQL with Python through the sqlite3 package. SQL makes data manipulation and aggregation much simpler through queries, while Python has extensive packages for visualization and modeling. In conjunction, these two languages provide a seamless data pipeline that has the added bonus of creating a database for safekeeping.</p><p>By the end of this tutorial, you will have learned how to:</p><ol><li>Set up the sqlite3 package to create a database connection to a Python script</li><li>Use SQL queries to modify and aggregate your data</li></ol><h3>How can SQL be used within Python?</h3><p>There are many ways to integrate SQL databases within a Python workspace, such as database drivers and object relational mappers.</p><p><strong>Database drivers</strong> commonly need an existing data warehouse such as a MySQL or PostgreSQL server. The driver then takes the server information (i.e. the server host, username, and password) in order to connect it to the Python program.</p><p>Object relational mappers such as <strong>SQLAlchemy </strong>makes it easier to link databases to your workflow and are more Python-like, as these don’t use traditional SQL commands—instead, they read and process the database through Python functions.</p><p><strong>SQLite </strong>lies somewhere between the two, as it is a self-contained database engine and does not require a server to get it up and running. It uses its own SQL dialect which doesn’t differ too much from the more commonly-used ones. For more information on SQLite within Python, you can read the <a href="https://docs.python.org/3/library/sqlite3.html">documentation</a> here.</p><p>To use SQLite within Python, both sqlite3 and SQLAlchemy can be used. For the purposes of this tutorial, however, we will focus on sqlite3.</p><h3>How do I use sqlite3?</h3><p>This demonstration consists of three main parts: preparing the data, creating a database, and finally, executing SQL commands. We will mainly be using the pandas and sqlite3 packages in order to show how the SQL-processed data can be fed into a dataframe.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/66198cb504db3824201c4653f9c77e45/href">https://medium.com/media/66198cb504db3824201c4653f9c77e45/href</a></iframe><p>After importing the needed packages (including scikit-learn datasets), we’ll load in the iris dataset and confirm its data type.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/2cdd02f87304fc6193972405bd8689e0/href">https://medium.com/media/2cdd02f87304fc6193972405bd8689e0/href</a></iframe><p>The dataset is split into two parts: the features (x-variables) and the target (y-variable). We’ll create two dataframes that extract these parts and then concatenate them using pd.concat().</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/787b8bc131da3d3e6844e371b8b64d20/href">https://medium.com/media/787b8bc131da3d3e6844e371b8b64d20/href</a></iframe><p>Before moving on, we’ll take a quick peek at our dataframe to make sure everything’s working nicely.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/3eae76a12f282c94772cf38f5962ce40/href">https://medium.com/media/3eae76a12f282c94772cf38f5962ce40/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/682/1*doLpcBm99bKhiJsOQY68Qw.png" /><figcaption>This is the dataframe containing the features and target variable of the iris data package.</figcaption></figure><p>Great! Now we can move on to the fun part. There are two main things we need to instantiate: the <strong>database connection</strong> and the <strong>cursor</strong>.</p><p>Calling the sqlite3.connect() function opens a connection to a database (.db) file. You can call an existing database file if you have one, provided that it is in the same folder as your Python workspace. Otherwise, sqlite3 will simply create a new database and store it on your local disk.</p><p>After creating our connection, we need to link a cursor to it. The cursor is what interacts with the database connection and allows us to pass SQL commands through.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/4ea7376d04cf31e9fba9e5f903b90007/href">https://medium.com/media/4ea7376d04cf31e9fba9e5f903b90007/href</a></iframe><p>The next step will be to write our dataframe to the database. A schema can be created if you’re adding more than one dataset, but since we only have one, we simply have to specify the name and the connection from earlier.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/0d933d8e7e24f916130f8a2a425f84d8/href">https://medium.com/media/0d933d8e7e24f916130f8a2a425f84d8/href</a></iframe><p>You may have noticed that the species column in the dataframe consists of numerical values. Luckily for us, we know what those correspond to. If we wanted to edit our data to reflect that using SQL, then a CASE WHEN command would come in handy.</p><p>Below is a simple query that creates a new column, species_name. As it is a query, it will not change the database contents.</p><p>To run it, we will pass the query to the cursor and fetch the results.</p><p>The example below shows fetchall(), which returns all results, but you can also use fetchone() to get one result, or fetchmany() to get a certain number of results, with the required number passed within the function (e.g. fetchmany(5) returns 5 results).</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/cb1e393ec3a045f2c5c18b27531e3d60/href">https://medium.com/media/cb1e393ec3a045f2c5c18b27531e3d60/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/352/1*LAbVLbPJvgw7_sbgP4FCUg.png" /><figcaption>The query output shows up as a list of the fetched results.</figcaption></figure><p>To save our query results to a dataframe, we will use the read_sql_query() function from pandas and pass in our query and database connection.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/c0bc470a645ed854d02376d3229c19d7/href">https://medium.com/media/c0bc470a645ed854d02376d3229c19d7/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/821/1*YCi2NeBzc2vn0RIGOF2SEA.png" /><figcaption>This dataframe now contains the rename query result.</figcaption></figure><p>We can also use aggregate queries to process our data. Here, we have the same CASE WHEN and some summary statistics for the rest of the columns. As they are aggregated, we can group them by species name.</p><p>Pandas can directly interact with the database and query through pd.read_sql_query().</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/c34e1a6c96c07d3f36a58ef630b093b3/href">https://medium.com/media/c34e1a6c96c07d3f36a58ef630b093b3/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/790/1*xyOhg4d4kAfD2kBBleqPkg.png" /><figcaption>This dataframe now contains the aggregation query result.</figcaption></figure><p>And we’re done! Before wrapping up, don’t forget to close the connection and cursor. Closing the connection ensures that all transactions or queries are wrapped up and the data stays safe. If there were uncommitted changes, then these wouldn’t be applied to the database once the data connection is closed.</p><p>The connection will also be terminated when you exit the program.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/9ae4526b9a46aab5a0838bc83d10bad4/href">https://medium.com/media/9ae4526b9a46aab5a0838bc83d10bad4/href</a></iframe><p>That’s it! You are on your way to mastering SQLite. If you’re interested in delving deeper, then check out these helpful links:</p><ul><li><a href="https://www.geeksforgeeks.org/python-sqlite/"><strong>GeeksforGeeks</strong></a></li><li><a href="https://www.sqlitetutorial.net/sqlite-python/"><strong>SQLite Tutorial</strong></a></li></ul><p>If you have any questions, or if you’d just like to chat about anything data-related, you can reach me via <a href="https://www.linkedin.com/in/vmbordonez/">LinkedIn</a>.</p><p>See you next time!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=81e46afa6ad5" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>