{"id":25524374,"date":"2022-05-16T14:57:21","date_gmt":"2022-05-16T09:27:21","guid":{"rendered":"https:\/\/entri.app\/blog\/?p=25524374"},"modified":"2023-04-27T12:55:44","modified_gmt":"2023-04-27T07:25:44","slug":"introduction-to-databases-in-python-a-detailed-study","status":"publish","type":"post","link":"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/","title":{"rendered":"Introduction To Databases In Python- A Detailed Study"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_79_2 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-69e5ec83cfa3b\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-69e5ec83cfa3b\"  aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/#Databases_in_Python\" >Databases in Python<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/#Basics_of_Relational_Databases\" >Basics of Relational Databases<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/#Applying_Filtering_Ordering_and_Grouping_to_Queries\" >Applying Filtering, Ordering and Grouping to Queries<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/#Advanced_SQLAlchemy_Queries\" >Advanced SQLAlchemy Queries<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/#Creating_and_Manipulating_your_own_Databases\" >Creating and Manipulating your own Databases<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/#Putting_it_all_together\" >Putting it all together<\/a><\/li><\/ul><\/nav><\/div>\n<p>Python is one of the most popular programming language. Due to its simplified syntax, Programmers can develop a wide range of software applications, fast and easy. With Python, you can easily build Data Science and Machine Learning projects, as well as general purpose applications. When combined with the popular SQL Server Data Platform, it provides Software Developers and Data Scientists, with a powerful software development setup, that can be used for building just about anything.<\/p>\n<p style=\"text-align: center;\"><strong><a class=\"in-cell-link\" href=\"https:\/\/entri.app\/course\/python-programming-course\/\" target=\"_blank\" rel=\"noopener\">&#8220;Ready to take your python skills to the next level? Sign up for a free demo today!&#8221;<\/a><\/strong><\/p>\n<p>Structured Query Language (SQL or &#8220;Sequel&#8221;) is the way we communicate with a relational database. It&#8217;s an intuitive and powerful language and mastering it is rapidly becoming a prerequisite for career paths outside of software development.<\/p>\n<p>SQL has been one of the most common skill sets required by employers for software developers for years, according to research published by Indeed. That fact is not likely to change any time soon, as businesses have woken up to the reality that the data they capture, generate, and store has tremendous business value to improve performance and efficiency and uncover new paths for growth.<\/p>\n<p><a href=\"https:\/\/entri.app\/course\/python-programming-course\/\" target=\"_blank\" rel=\"noopener\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-25520910 size-full\" src=\"https:\/\/entri.app\/blog\/wp-content\/uploads\/2022\/04\/Python-and-Machine-Learning-Square.png\" alt=\"Python and Machine Learning Square\" width=\"345\" height=\"345\" srcset=\"https:\/\/entri.app\/blog\/wp-content\/uploads\/2022\/04\/Python-and-Machine-Learning-Square.png 345w, https:\/\/entri.app\/blog\/wp-content\/uploads\/2022\/04\/Python-and-Machine-Learning-Square-300x300.png 300w, https:\/\/entri.app\/blog\/wp-content\/uploads\/2022\/04\/Python-and-Machine-Learning-Square-150x150.png 150w, https:\/\/entri.app\/blog\/wp-content\/uploads\/2022\/04\/Python-and-Machine-Learning-Square-24x24.png 24w, https:\/\/entri.app\/blog\/wp-content\/uploads\/2022\/04\/Python-and-Machine-Learning-Square-48x48.png 48w, https:\/\/entri.app\/blog\/wp-content\/uploads\/2022\/04\/Python-and-Machine-Learning-Square-96x96.png 96w, https:\/\/entri.app\/blog\/wp-content\/uploads\/2022\/04\/Python-and-Machine-Learning-Square-75x75.png 75w\" sizes=\"auto, (max-width: 345px) 100vw, 345px\" \/><\/a><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Databases_in_Python\"><\/span><strong>Databases in Python<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h2><span class=\"ez-toc-section\" id=\"Basics_of_Relational_Databases\"><\/span><strong>Basics of Relational Databases<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3><strong style=\"font-style: inherit;\">Introduction to Databases<\/strong><\/h3>\n<h4><strong style=\"font-style: inherit;\">Relational model<\/strong><\/h4>\n<p data-adtags-visited=\"true\">Tables, Columns, Rows, and Relationships are part of the relational model.<\/p>\n<h3><strong>Connecting to your database<\/strong><\/h3>\n<p data-adtags-visited=\"true\">Database types:<\/p>\n<ul>\n<li>SQLite<\/li>\n<li>PostgreSQL<\/li>\n<li>MySQL<\/li>\n<li>MS SQL<\/li>\n<li>Oracle<\/li>\n<\/ul>\n<h4><strong>Autoloading Tables from a database<\/strong><\/h4>\n<p data-adtags-visited=\"true\">SQLAlchemy can be used to automatically load tables from a database using something called reflection. Reflection is the process of reading the database and building the metadata based on that information. It\u2019s the opposite of creating a Table by hand and is very useful for working with existing databases.<\/p>\n<p data-adtags-visited=\"true\">To perform reflection, you will first need to import and initialize a\u00a0MetaData\u00a0object.\u00a0MetaData\u00a0objects contain information about tables stored in a database. During reflection, the\u00a0MetaData\u00a0object will be populated with information about the reflected table automatically, so we only need to initialize it before reflecting by calling\u00a0MetaData().<\/p>\n<p data-adtags-visited=\"true\">You will also need to import the\u00a0Table\u00a0object from the SQLAlchemy package. Then, you use this\u00a0Table\u00a0object to read your table from the engine, autoload the columns, and populate the metadata. This can be done with a single call to\u00a0Table(): using the\u00a0Table\u00a0object in this manner is a lot like passing arguments to a function. For example, to autoload the columns with the engine, you have to specify the keyword arguments\u00a0autoload=True\u00a0and\u00a0autoload_with=engine\u00a0to\u00a0Table().<\/p>\n<p>Finally, to view information about the object you just created, you will use the\u00a0repr()\u00a0function. For any Python object,\u00a0repr()\u00a0returns a text representation of that object. For SQLAlchemy\u00a0Table\u00a0objects, it will return the information about that table contained in the metadata.<\/p>\n<p style=\"text-align: center;\"><strong><a class=\"in-cell-link\" href=\"https:\/\/entri.app\/course\/python-programming-course\/\" target=\"_blank\" rel=\"noopener\">&#8220;Experience the power of our web development course with a free demo &#8211; enroll now!&#8221;<\/a><\/strong><\/p>\n<h4><strong>Viewing Table details<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">Now you can learn more about the columns and structure of your table. It is important to get an understanding of your database by examining the column names. This can be done by using the .columns attribute and accessing the .keys() method. For example, census.columns.keys() would return a list of column names of the census table.<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">Following this, we can use the metadata container to find out more details about the reflected table such as the columns and their types. For example, information about the table objects are stored in the metadata.tables dictionary, so you can get the metadata of your census table with metadata.tables[&#8216;census&#8217;]. This is similar to your use of the repr() function on the census table.<\/p>\n<table width=\"461\">\n<tbody>\n<tr>\n<td width=\"16\">&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td width=\"445\"><strong>from<\/strong> sqlalchemy <strong>import<\/strong> create_engine, MetaData, Table<\/p>\n<p>engine <strong>=<\/strong> create_engine(&#8216;sqlite:\/\/\/census.sqlite&#8217;)<\/p>\n<p>metadata <strong>=<\/strong> MetaData()<\/p>\n<p># Reflect the census table from the engine: census<\/p>\n<p>census <strong>=<\/strong> Table(&#8216;census&#8217;, metadata, autoload<strong>=<\/strong>True, autoload_with<strong>=<\/strong>engine)<\/p>\n<p># Print the column names<\/p>\n<p>print(census.columns.keys())<\/p>\n<p># Print full metadata of census<\/p>\n<p>print(repr(metadata.tables[&#8216;census&#8217;]))<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h3><strong>Introduction to SQL: <\/strong><strong style=\"font-style: inherit;\">raw SQL vs SQLAlchemy<\/strong><\/h3>\n<h4><strong style=\"font-style: inherit;\">Selecting data from a Table: raw SQL<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">We first need to establish a connection to it by using the\u00a0.connect()\u00a0method on the engine. This is because the\u00a0create_engine()\u00a0function that you have used before returns an instance of an engine, but it does not actually open a connection until an action is called that would require a connection, such as a query.<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">Using what we just learned about SQL and applying the\u00a0.execute()\u00a0method on our connection, we can leverage a raw SQL query to query all the records in our\u00a0census\u00a0table. The object returned by the\u00a0.execute()\u00a0method is a\u00a0<strong style=\"font-style: inherit;\">ResultProxy<\/strong>. On this ResultProxy, we can then use the\u00a0.fetchall()\u00a0method to get our results \u2013 that is, the\u00a0<strong style=\"font-style: inherit;\">ResultSet<\/strong>.<\/p>\n<p style=\"text-align: center;\"><strong><a class=\"in-cell-link\" href=\"https:\/\/entri.app\/course\/python-programming-course\/\" target=\"_blank\" rel=\"noopener\">&#8220;Get hands-on with our python course &#8211; sign up for a free demo!&#8221;<\/a><\/strong><\/p>\n<h4><strong>Selecting data from a Table with SQLAlchemy<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">\u00a0It\u2019s now time to build your first select statement using SQLAlchemy. SQLAlchemy provides a nice \u201cPythonic\u201d way of interacting with databases. When you used raw SQL in the last exercise, you queried the database directly. When using SQLAlchemy, you will go through a\u00a0Table\u00a0object instead, and SQLAlchemy will take case of translating your query to an appropriate SQL statement for you. So rather than dealing with the differences between specific dialects of traditional SQL such as MySQL or PostgreSQL, you can leverage the Pythonic framework of SQLAlchemy to streamline your workflow and more efficiently query your data. For this reason, it is worth learning even if you may already be familiar with traditional SQL.<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">You will also fetch only a few records of the ResultProxy by using\u00a0.fetchmany()\u00a0with a\u00a0size\u00a0argument specifying the number of records to fetch.<\/p>\n<h4><strong>Handling a ResultSet<\/strong><\/h4>\n<ul>\n<li>ResultProxy: The object returned by the\u00a0.execute() It can be used in a variety of ways to get the data returned by the query.<\/li>\n<li>ResultSet: The actual data asked for in the query when using a fetch method such as\u00a0.fetchall()on a ResultProxy.<\/li>\n<\/ul>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">This separation between the ResultSet and ResultProxy allows us to fetch as much or as little data as we desire.<\/p>\n<h2 style=\"font-style: inherit;\"><span class=\"ez-toc-section\" id=\"Applying_Filtering_Ordering_and_Grouping_to_Queries\"><\/span><strong style=\"font-style: inherit;\">Applying Filtering, Ordering and Grouping to Queries<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">Filtering and targeting data<\/strong><\/h3>\n<h4 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">Connecting to a PostgreSQL database<\/strong><\/h4>\n<p><span style=\"font-style: inherit; font-weight: inherit;\">There are three components to the connection string in this exercise: the dialect and driver (&#8216;postgresql+psycopg2:\/\/&#8217;), followed by the username and password (&#8216;username:password&#8217;), followed by the host and port (&#8216;@postgresql.csrrinzqubik.us-east-1.rds.amazonaws.com:1234\/&#8217;), and finally, the database name (&#8216;census&#8217;). You will have to pass this string as an argument to\u00a0create_engine()\u00a0in order to connect to the database.<\/span><\/p>\n<table width=\"1211\">\n<tbody>\n<tr>\n<td width=\"17\">&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td width=\"1195\"># Import create_engine function<\/p>\n<p><strong>from<\/strong> sqlalchemy <strong>import<\/strong> create_engine<\/p>\n<p>&nbsp;<\/p>\n<p># Create an engine to the census database<\/p>\n<p>engine <strong>=<\/strong> create_engine(&#8216;postgresql+psycopg2:\/\/username:password@postgresql.csrrinzqubik.us-east-1.rds.amazonaws.com:1234\/census&#8217;)<\/p>\n<p>&nbsp;<\/p>\n<p># Use the .table_names() method on the engine to print the table names<\/p>\n<p>print(engine.table_names())<\/p>\n<p>&nbsp;<\/p>\n<p># [&#8216;census&#8217;, &#8216;state_fact&#8217;, &#8216;vrska&#8217;, &#8216;census1&#8217;, &#8216;data&#8217;, &#8216;data1&#8242;, &#8217;employees3&#8242;, &#8216;users&#8217;, &#8217;employees&#8217;, &#8217;employees_2&#8242;]<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p style=\"text-align: center;\"><strong><a class=\"in-cell-link\" href=\"https:\/\/entri.app\/course\/python-programming-course\/\" target=\"_blank\" rel=\"noopener\">&#8220;Ready to take your python skills to the next level? Sign up for a free demo today!&#8221;<\/a><\/strong><\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">In addition to standard Python comparators, we can also use methods such as\u00a0in_()\u00a0to create more powerful\u00a0where()\u00a0clauses. You can see a full list of expressions in the\u00a0SQLAlchemy Documentation.<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">Method\u00a0in_(), when used on a column, allows us to include records where the value of a column is among a list of possible values. For example,\u00a0where(census.columns.age.in_([20, 30, 40]))\u00a0will return only records for people who are exactly 20, 30, or 40 years old.<\/p>\n<p>Along with\u00a0in_, you can also use methods like\u00a0and_,any_\u00a0to create more powerful\u00a0where()\u00a0clauses. You might have noticed that we did not use any of the fetch methods to retrieve a ResultSet like in the previous exercises. Indeed, if you are only interested in manipulating one record at a time, you can iterate over the ResultProxy directly!<\/p>\n<p>&nbsp;<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">SQLAlchemy also allows users to use conjunctions such as\u00a0and_(),\u00a0or_(), and\u00a0not_()\u00a0to build more complex filtering. For example, we can get a set of records for people in New York who are 21 or 37 years old with the following code:<\/p>\n<table width=\"665\">\n<tbody>\n<tr>\n<td width=\"0\">&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td width=\"630\">select([census]).where(<\/p>\n<p>and_(census.columns.state <strong>==<\/strong> &#8216;New York&#8217;,<\/p>\n<p>or_(census.columns.age <strong>==<\/strong> 21,<\/p>\n<p>census.columns.age <strong>==<\/strong> 37<\/p>\n<p>)<\/p>\n<p>)<\/p>\n<p>)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h3 style=\"font-style: inherit;\"><strong>Overview of ordering<\/strong><\/h3>\n<h4 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">Ordering by a single column<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">To sort the result output by a field, we use the\u00a0.order_by()\u00a0method. By default, the\u00a0.order_by()\u00a0method sorts from lowest to highest on the supplied column.<\/p>\n<table width=\"1326\">\n<tbody>\n<tr>\n<td width=\"0\">&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td width=\"1281\"># Build a query to select the state column: stmt<\/p>\n<p>stmt <strong>=<\/strong> select([census.columns.state])<\/p>\n<p>&nbsp;<\/p>\n<p># Order stmt by the state column<\/p>\n<p>stmt <strong>=<\/strong> stmt.order_by(census.columns.state)<\/p>\n<p>&nbsp;<\/p>\n<p># Execute the query and store the results: results<\/p>\n<p>results <strong>=<\/strong> connection.execute(stmt).fetchall()<\/p>\n<p>&nbsp;<\/p>\n<p># Print the first 10 results<\/p>\n<p>print(results[:10])<\/p>\n<p>&nbsp;<\/p>\n<p># [(&#8216;Alabama&#8217;,), (&#8216;Alabama&#8217;,), (&#8216;Alabama&#8217;,), (&#8216;Alabama&#8217;,), (&#8216;Alabama&#8217;,), (&#8216;Alabama&#8217;,), (&#8216;Alabama&#8217;,), (&#8216;Alabama&#8217;,), (&#8216;Alabama&#8217;,), (&#8216;Alabama&#8217;,)]<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">Ordering in descending order by a single column<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">You can also use\u00a0.order_by()\u00a0to sort from highest to lowest by wrapping a column in the\u00a0desc()\u00a0function. Although you haven\u2019t seen this function in action, it generalizes what you have already learned.<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">Pass\u00a0desc()\u00a0(for \u201cdescending\u201d) inside an\u00a0.order_by()\u00a0with the name of the column you want to sort by. For instance,\u00a0stmt.order_by(desc(table.columns.column_name))\u00a0sorts\u00a0column_name\u00a0in descending order.<\/p>\n<h4><strong>Ordering by multiple columns<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">We can pass multiple arguments to the\u00a0.order_by()\u00a0method to order by multiple columns. In fact, we can also sort in ascending or descending order for each individual column.<\/p>\n<p style=\"text-align: center;\" data-adtags-visited=\"true\"><strong><a class=\"in-cell-link\" href=\"https:\/\/entri.app\/course\/python-programming-course\/\" target=\"_blank\" rel=\"noopener\">&#8220;Experience the power of our web development course with a free demo &#8211; enroll now!&#8221;<\/a><\/strong><\/p>\n<h3 style=\"font-style: inherit;\"><strong>Counting, summing and grouping data<\/strong><\/h3>\n<h4 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">Counting distinct data<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">SQLAlchemy\u2019s\u00a0func\u00a0module provides access to built-in SQL functions that can make operations like counting and summing faster and more efficient.<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">We can use\u00a0func.sum()\u00a0to get a\u00a0<strong style=\"font-style: inherit;\">sum<\/strong>\u00a0of the\u00a0pop2008\u00a0column of\u00a0census\u00a0as shown below:<\/p>\n<table width=\"665\">\n<tbody>\n<tr>\n<td width=\"0\"><\/td>\n<td width=\"630\">select([func.sum(census.columns.pop2008)])<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">If instead you want to\u00a0<strong style=\"font-style: inherit;\">count<\/strong>\u00a0the number of values in\u00a0pop2008, you could use\u00a0func.count()\u00a0like this:<\/p>\n<table width=\"665\">\n<tbody>\n<tr>\n<td width=\"0\"><\/td>\n<td width=\"630\">select([func.count(census.columns.pop2008)])<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Furthermore, if you only want to count the\u00a0<strong style=\"font-style: inherit;\">distinct<\/strong>\u00a0values of\u00a0pop2008, you can use the\u00a0.distinct()\u00a0method:<\/p>\n<table width=\"665\">\n<tbody>\n<tr>\n<td width=\"8\"><\/td>\n<td width=\"657\">select([func.count(census.columns.pop2008.distinct())])<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4><strong>Count of records by state<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">Often, we want to get a count for each record with a particular value in another column. The\u00a0.group_by()\u00a0method helps answer this type of query. You can pass a column to the\u00a0.group_by()\u00a0method and use in an aggregate function like\u00a0sum()\u00a0or\u00a0count(). Much like the\u00a0.order_by()\u00a0method,\u00a0.group_by()\u00a0can take multiple columns as arguments.<\/p>\n<table width=\"684\">\n<tbody>\n<tr>\n<td width=\"0\">&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td width=\"639\"># Import func<\/p>\n<p><strong>from<\/strong> sqlalchemy <strong>import<\/strong> func<\/p>\n<p>&nbsp;<\/p>\n<p># Build a query to select the state and count of ages by state: stmt<\/p>\n<p>stmt <strong>=<\/strong> select([census.columns.state, func.count(census.columns.age)])<\/p>\n<p>&nbsp;<\/p>\n<p># Group stmt by state<\/p>\n<p>stmt <strong>=<\/strong> stmt.group_by(census.columns.state)<\/p>\n<p>&nbsp;<\/p>\n<p># Execute the statement and store all the records: results<\/p>\n<p>results <strong>=<\/strong> connection.execute(stmt).fetchall()<\/p>\n<p>&nbsp;<\/p>\n<p># Print results<\/p>\n<p>print(results)<\/p>\n<p>&nbsp;<\/p>\n<p># Print the keys\/column names of the results returned<\/p>\n<p>print(results[0].keys())<\/td>\n<td width=\"220\"><\/td>\n<\/tr>\n<tr>\n<td width=\"0\">&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td colspan=\"2\" width=\"868\">[(&#8216;Alabama&#8217;, 172), (&#8216;Alaska&#8217;, 172), (&#8216;Arizona&#8217;, 172), (&#8216;Arkansas&#8217;, 172), (&#8216;California&#8217;, 172),<\/p>\n<p>&#8230;<\/p>\n<p>(&#8216;Wisconsin&#8217;, 172), (&#8216;Wyoming&#8217;, 172)]\n<p>&nbsp;<\/p>\n[&#8216;state&#8217;, &#8216;count_1&#8217;]<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4><strong>Determining the population sum by state<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">To avoid confusion with query result column names like\u00a0count_1, we can use the\u00a0.label()\u00a0method to provide a name for the resulting column. This gets appended to the function method we are using, and its argument is the name we want to use.<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">We can pair\u00a0func.sum()\u00a0with\u00a0.group_by()\u00a0to get a sum of the population by\u00a0State\u00a0and use the\u00a0label()\u00a0method to name the output.<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">We can also create the\u00a0func.sum()\u00a0expression before using it in the select statement. We do it the same way we would inside the select statement and store it in a variable. Then we use that variable in the select statement where the\u00a0func.sum()\u00a0would normally be.<\/p>\n<p style=\"text-align: center;\"><strong><a class=\"in-cell-link\" href=\"https:\/\/entri.app\/course\/python-programming-course\/\" target=\"_blank\" rel=\"noopener\">&#8220;Get hands-on with our python course &#8211; sign up for a free demo!&#8221;<\/a><\/strong><\/p>\n<h3 style=\"font-style: inherit;\"><strong>Use pandas and matplotlib to visualize our data<\/strong><\/h3>\n<h4 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">ResultsSets and pandas dataframes<\/strong><\/h4>\n<table width=\"665\">\n<tbody>\n<tr>\n<td width=\"17\">&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td width=\"649\"># import pandas<\/p>\n<p><strong>import<\/strong> pandas as pd<\/p>\n<p>&nbsp;<\/p>\n<p># Create a DataFrame from the results: df<\/p>\n<p>df <strong>=<\/strong> pd.DataFrame(results)<\/p>\n<p>&nbsp;<\/p>\n<p># Set column names<\/p>\n<p>df.columns <strong>=<\/strong> results[0].keys()<\/p>\n<p>&nbsp;<\/p>\n<p># Print the Dataframe<\/p>\n<p>print(df)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2 style=\"font-style: inherit;\"><span class=\"ez-toc-section\" id=\"Advanced_SQLAlchemy_Queries\"><\/span><strong>Advanced SQLAlchemy Queries<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">Calculating values in a query<\/strong><\/h3>\n<h4 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">Connecting to a MySQL database<\/strong><\/h4>\n<table width=\"1132\">\n<tbody>\n<tr>\n<td width=\"0\">&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td width=\"1088\"># Import create_engine function<\/p>\n<p><strong>from<\/strong> sqlalchemy <strong>import<\/strong> create_engine<\/p>\n<p>&nbsp;<\/p>\n<p># Create an engine to the census database<\/p>\n<p>engine <strong>=<\/strong> create_engine(&#8216;mysql+pymysql:\/\/username:password@courses.csrrinzqubik.us-east-1.rds.amazonaws.com:3306\/census&#8217;)<\/p>\n<p>&nbsp;<\/p>\n<p># Print the table names<\/p>\n<p>print(engine.table_names())<\/p>\n<p>&nbsp;<\/p>\n<p>#\u00a0 [&#8216;census&#8217;, &#8216;state_fact&#8217;]<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">Calculating a difference between two columns<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-width=\"809\" data-adtags-visited=\"true\">Often, you\u2019ll need to perform math operations as part of a query, such as if you wanted to calculate the change in population from 2000 to 2008. For math operations on numbers, the operators in SQLAlchemy work the same way as they do in Python.<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">You can use these operators to perform addition (+), subtraction (-), multiplication (*), division (\/), and modulus (%) operations. Note: They behave differently when used with non-numeric column types.<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">Let\u2019s now find the top 5 states by population growth between 2000 and 2008.<\/p>\n<table width=\"665\">\n<tbody>\n<tr>\n<td width=\"0\">&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td width=\"630\">census.columns.keys()<\/p>\n[&#8216;state&#8217;, &#8216;sex&#8217;, &#8216;age&#8217;, &#8216;pop2000&#8217;, &#8216;pop2008&#8217;]\n<p>&nbsp;<\/p>\n<p>connection.execute(select([census])).fetchmany(3)<\/p>\n[(&#8216;Illinois&#8217;, &#8216;M&#8217;, 0, 89600, 95012),<\/p>\n<p>(&#8216;Illinois&#8217;, &#8216;M&#8217;, 1, 88445, 91829),<\/p>\n<p>(&#8216;Illinois&#8217;, &#8216;M&#8217;, 2, 88729, 89547)]<\/td>\n<td width=\"344\"><\/td>\n<\/tr>\n<tr>\n<td width=\"0\">&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td colspan=\"2\" width=\"965\"># Build query to return state names by population difference from 2008 to 2000: stmt<\/p>\n<p>stmt <strong>=<\/strong> select([census.columns.state, (census.columns.pop2008<strong>&#8211;<\/strong>census.columns.pop2000).label(&#8216;pop_change&#8217;)])<\/p>\n<p>&nbsp;<\/p>\n<p># Append group by for the state: stmt_grouped<\/p>\n<p>stmt_grouped <strong>=<\/strong> stmt.group_by(census.columns.state)<\/p>\n<p>&nbsp;<\/p>\n<p># Append order by for pop_change descendingly: stmt_ordered<\/p>\n<p>stmt_ordered <strong>=<\/strong> stmt_grouped.order_by(desc(&#8216;pop_change&#8217;))<\/p>\n<p>&nbsp;<\/p>\n<p># Return only 5 results: stmt_top5<\/p>\n<p>stmt_top5 <strong>=<\/strong> stmt_ordered.limit(5)<\/p>\n<p>&nbsp;<\/p>\n<p># Use connection to execute stmt_top5 and fetch all results<\/p>\n<p>results <strong>=<\/strong> connection.execute(stmt_top5).fetchall()<\/p>\n<p>&nbsp;<\/p>\n<p># Print the state and population change for each record<\/p>\n<p><strong>for<\/strong> result <strong>in<\/strong> results:<\/p>\n<p>print(&#8216;{}:{}&#8217;.format(result.state, result.pop_change))<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&nbsp;<\/p>\n<table width=\"665\">\n<tbody>\n<tr>\n<td width=\"0\">&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td width=\"630\">California:105705<\/p>\n<p>Florida:100984<\/p>\n<p>Texas:51901<\/p>\n<p>New York:47098<\/p>\n<p>Pennsylvania:42387<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p style=\"text-align: center;\"><strong><a class=\"in-cell-link\" href=\"https:\/\/entri.app\/course\/python-programming-course\/\" target=\"_blank\" rel=\"noopener\">&#8220;Ready to take your python skills to the next level? Sign up for a free demo today!&#8221;<\/a><\/strong><\/p>\n<h4 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">Determining the overall percentage of women<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-width=\"809\" data-adtags-visited=\"true\">It\u2019s possible to combine functions and operators in a single select statement as well. These combinations can be exceptionally handy when we want to calculate percentages or averages, and we can also use the\u00a0case()\u00a0expression to operate on data that meets specific criteria while not affecting the query as a whole. The\u00a0case()\u00a0expression accepts a list of conditions to match and the column to return if the condition matches, followed by an\u00a0else_\u00a0if none of the conditions match. We can wrap this entire expression in any function or math operation we like.<\/p>\n<h3 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">SQL relationships<\/strong><\/h3>\n<h4 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">Automatic joins with an established relationship<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">If you have two tables that already have an established relationship, you can automatically use that relationship by just adding the columns we want from each table to the select statement. Recall that Jason constructed the following query:<\/p>\n<table width=\"701\">\n<tbody>\n<tr>\n<td width=\"0\"><\/td>\n<td width=\"665\">stmt <strong>=<\/strong> select([census.columns.pop2008, state_fact.columns.abbreviation])<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-width=\"809\" data-adtags-visited=\"true\">In order to join the\u00a0census\u00a0and\u00a0state_fact\u00a0tables and select the\u00a0pop2008\u00a0column from the first and the\u00a0abbreviation\u00a0column from the second. In this case, the\u00a0census\u00a0and\u00a0state_fact\u00a0tables had a pre-defined relationship: the\u00a0state\u00a0column of the former corresponded to the\u00a0name\u00a0column of the latter.<\/p>\n<h4><strong>Joins<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">If you aren\u2019t selecting columns from both tables or the two tables don\u2019t have a defined relationship, you can still use the\u00a0.join()\u00a0method on a table to join it with another table and get extra data related to our query.<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">The\u00a0join()\u00a0takes the table object you want to join in as the first argument and a condition that indicates how the tables are related to the second argument.<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">Finally, you use the\u00a0.select_from()\u00a0method on the select statement to wrap the join clause.<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">For example, the following code joins the\u00a0census\u00a0table to the\u00a0state_fact\u00a0table such that the\u00a0state\u00a0column of the\u00a0census\u00a0table corresponded to the\u00a0name\u00a0column of the\u00a0state_fact\u00a0table.<\/p>\n<table width=\"476\">\n<tbody>\n<tr>\n<td width=\"16\">&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td width=\"459\">stmt <strong>=<\/strong> stmt.select_from(<\/p>\n<p>census.join(<\/p>\n<p>state_fact, census.columns.state <strong>==<\/strong><\/p>\n<p>state_fact.columns.name)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4><strong>More practice with joins<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-width=\"809\" data-adtags-visited=\"true\">You can use the same select statement you built in the last exercise, however, let\u2019s add a twist and only return a few columns and use the other table in a\u00a0group_by()\u00a0clause.<\/p>\n<table width=\"574\">\n<tbody>\n<tr>\n<td width=\"1\">&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td width=\"572\">stmt <strong>=<\/strong> select([<\/p>\n<p>census.columns.state,<\/p>\n<p>func.sum(census.columns.pop2008),<\/p>\n<p>state_fact.columns.census_division_name<\/p>\n<p>])<\/p>\n<p>&nbsp;<\/p>\n<p>connection.execute(stmt).fetchmany(3)<\/p>\n<p># [(&#8216;Texas&#8217;, 15446707263, &#8216;South Atlantic&#8217;)]<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p style=\"text-align: center;\"><strong><a class=\"in-cell-link\" href=\"https:\/\/entri.app\/course\/python-programming-course\/\" target=\"_blank\" rel=\"noopener\">&#8220;Experience the power of our web development course with a free demo &#8211; enroll now!&#8221;<\/a><\/strong><\/p>\n<h3 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">Working with hierarchical tables<\/strong><\/h3>\n<h4 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">Using alias to handle same table joined queries<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">Often, you\u2019ll have tables that contain hierarchical data, such as employees and managers who are also employees. For this reason, you may wish to join a table to itself on different columns. The\u00a0.alias()\u00a0method, which creates a copy of a table, helps accomplish this task. Because it\u2019s the same table, you only need a where clause to specify the join condition.<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-visited=\"true\">Here, you\u2019ll use the\u00a0.alias()\u00a0method to build a query to join the\u00a0employees\u00a0table against itself to determine to whom everyone reports.<\/p>\n<table width=\"842\">\n<tbody>\n<tr>\n<td width=\"16\">&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td width=\"825\">employees.columns.keys()<\/p>\n<p># [&#8216;id&#8217;, &#8216;name&#8217;, &#8216;job&#8217;, &#8216;mgr&#8217;, &#8216;hiredate&#8217;, &#8216;sal&#8217;, &#8216;comm&#8217;, &#8216;dept&#8217;]\n<p>&nbsp;<\/p>\n<p>connection.execute(select([employees.columns.name, employees.columns.mgr])).fetchmany(5)<\/p>\n<p># [(&#8216;JOHNSON&#8217;, 6), (&#8216;HARDING&#8217;, 9), (&#8216;TAFT&#8217;, 2), (&#8216;HOOVER&#8217;, 2), (&#8216;LINCOLN&#8217;, 6)]<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4><strong>\u00a0<\/strong><strong>Leveraging functions and group_bys with hierarchical data<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-width=\"809\" data-adtags-visited=\"true\">It\u2019s also common to want to roll up data which is in a hierarchical table. Rolling up data requires making sure you\u2019re careful which alias you use to perform the group_bys and which table you use for the function.<\/p>\n<h3 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">Dealing with large ResultSets<\/strong><\/h3>\n<h4 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">Working on blocks of records<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\" data-adtags-width=\"809\" data-adtags-visited=\"true\">Sometimes you may have the need to work on a large ResultProxy, and you may not have the memory to load all the results at once.<br \/>\nTo work around that issue, you can get blocks of rows from the ResultProxy by using the\u00a0.fetchmany()\u00a0method inside a loop. With\u00a0.fetchmany(), give it an argument of the number of records you want. When you reach an empty list, there are no more rows left to fetch, and you have processed all the results of the query.<br \/>\nThen you need to use the\u00a0.close()\u00a0method to close out the connection to the database.<\/p>\n<h2 style=\"font-style: inherit;\"><span class=\"ez-toc-section\" id=\"Creating_and_Manipulating_your_own_Databases\"><\/span><strong style=\"font-style: inherit;\">Creating and Manipulating your own Databases<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">Creating databases and tables<\/strong><\/h3>\n<h4 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">Creating tables with SQLAlchemy<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\">Previously, you used the\u00a0Table\u00a0object to reflect a table from an\u00a0existing\u00a0database, but what if you wanted to create a\u00a0<em style=\"font-weight: inherit;\">new<\/em>\u00a0table? You\u2019d still use the\u00a0Table\u00a0object; however, you\u2019d need to replace the\u00a0autoload\u00a0and\u00a0autoload_with\u00a0parameters with Column objects.<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\">The\u00a0Column\u00a0object takes a name, a SQLAlchemy type with an optional format, and optional keyword arguments for different constraints.<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\">When defining the table, recall how in the video Jason passed in\u00a0255\u00a0as the maximum length of a String by using\u00a0Column(&#8216;name&#8217;, String(255)). Checking out the slides from the video may help: you can download them by clicking on \u2018Slides\u2019 next to the IPython Shell.<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\">After defining the table, you can create the table in the database by using the\u00a0.create_all()\u00a0method on metadata and supplying the engine as the only parameter. Go for it!<\/p>\n<table width=\"631\">\n<tbody>\n<tr>\n<td width=\"16\">&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td width=\"615\">metadata<\/p>\n<p>MetaData(bind<strong>=<\/strong>None)<\/p>\n<p>&nbsp;<\/p>\n<p>engine<\/p>\n<p>Engine(sqlite:<strong>\/\/\/<\/strong>:memory:)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4><strong>Constraints and data defaults<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\">You\u2019re now going to practice creating a table with some constraints! Often, you\u2019ll need to make sure that a column is unique, nullable, a positive value, or related to a column in another table. This is where constraints come in.<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\">You can also set a default value for the column if no data is passed to it via the\u00a0default\u00a0keyword on the column.<\/p>\n<h3 style=\"font-style: inherit;\"><strong>Inserting data into a table<\/strong><\/h3>\n<h4 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">Inserting a single row<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\">There are several ways to perform an insert with SQLAlchemy; however, we are going to focus on the one that follows the same pattern as the\u00a0select\u00a0statement.<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\">It uses an\u00a0insert\u00a0statement where you specify the table as an argument, and supply the data you wish to insert into the value via the\u00a0.values()\u00a0method as keyword arguments<\/p>\n<h4><strong>Inserting multiple records at once<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\">When inserting multiple records at once, you do not use the\u00a0.values()\u00a0method. Instead, you\u2019ll want to first build a\u00a0<strong style=\"font-style: inherit;\">list of dictionaries<\/strong>\u00a0that represents the data you want to insert, with keys being the names of the columns.<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\">In the\u00a0.execute()\u00a0method, you can pair this list of dictionaries with an\u00a0insert\u00a0statement, which will insert all the records in your list of dictionaries.<\/p>\n<h4><strong>Loading a CSV into a table<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\">One way to do that would be to read a CSV file line by line, create a dictionary from each line, and then use\u00a0insert(), like you did in the previous exercise.<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\">But there is a faster way using\u00a0pandas. You can read a CSV file into a DataFrame using the\u00a0read_csv()\u00a0function (this function should be familiar to you, but you can run\u00a0help(pd.read_csv)\u00a0in the console to refresh your memory!). Then, you can call the\u00a0.to_sql()\u00a0method on the DataFrame to load it into a SQL table in a database. The columns of the DataFrame should match the columns of the SQL table.<\/p>\n<p style=\"text-align: center;\"><strong><a class=\"in-cell-link\" href=\"https:\/\/entri.app\/course\/python-programming-course\/\" target=\"_blank\" rel=\"noopener\">&#8220;Get hands-on with our python course &#8211; sign up for a free demo!&#8221;<\/a><\/strong><\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\">.to_sql()\u00a0has many parameters, but in this exercise we will use the following:<\/p>\n<ul>\n<li>nameis the name of the SQL table (as a string).<\/li>\n<li>conis the connection to the database that you will use to upload the data.<\/li>\n<li>if_existsspecifies how to behave if the table already exists in the database; possible values are\u00a0&#8220;fail&#8221;,\u00a0&#8220;replace&#8221;, and\u00a0&#8220;append&#8221;.<\/li>\n<li>index(True\u00a0or\u00a0False) specifies whether to write the DataFrame\u2019s index as a column.<\/li>\n<\/ul>\n<table width=\"665\">\n<tbody>\n<tr>\n<td width=\"8\">&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td width=\"570\">connection<\/p>\n<p>&lt;sqlalchemy.engine.base.Connection at 0x7feca8d64e10&gt;<\/td>\n<td width=\"87\"><\/td>\n<\/tr>\n<tr>\n<td width=\"8\">&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td colspan=\"2\" width=\"657\"># append the data from census_df to the &#8220;census&#8221; table via connection<\/p>\n<p>census_df.to_sql(name<strong>=<\/strong>&#8216;census&#8217;, con<strong>=<\/strong>connection, if_exists<strong>=<\/strong>&#8216;append&#8217;, index<strong>=<\/strong>False)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h3><strong>Updating data in a database<\/strong><\/h3>\n<h4 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">Updating individual records<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\">The\u00a0update\u00a0statement is very similar to an\u00a0insert\u00a0statement. For example, you can update all wages in the\u00a0employees\u00a0table as follows:<\/p>\n<table width=\"665\">\n<tbody>\n<tr>\n<td width=\"0\"><\/td>\n<td width=\"630\">stmt <strong>=<\/strong> update(employees).values(wage<strong>=<\/strong>100.00)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p style=\"font-style: inherit; font-weight: inherit;\">The\u00a0update\u00a0statement also typically uses a\u00a0where\u00a0clause to help us determine what data to update. For example, to only update the record for the employee with ID 15, you would append the previous statement as follows:<\/p>\n<h4><strong>\u00a0<\/strong><strong>Updating multiple records<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\">By using a\u00a0where\u00a0clause that selects more records, you can update multiple records at once.<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\">Unlike inserting, updating multiple records works exactly the same way as updating a single record (as long as you are updating them with the same value).<\/p>\n<h4><strong>Correlated updates<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\">You can also update records with data from a select statement. This is called a correlated update. It works by defining a\u00a0select\u00a0statement that returns the value you want to update the record with and assigning that select statement as the value in\u00a0update.<\/p>\n<h3 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">Removing data from a database<\/strong><\/h3>\n<h4 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">Deleting all the records from a table<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\">Often, you\u2019ll need to empty a table of all of its records so you can reload the data. You can do this with a\u00a0delete\u00a0statement with just the table as an argument. For example, delete the table\u00a0extra_employees\u00a0by executing as follows:<\/p>\n<table width=\"665\">\n<tbody>\n<tr>\n<td width=\"0\">&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td width=\"630\">delete_stmt <strong>=<\/strong> delete(extra_employees)<\/p>\n<p>result_proxy <strong>=<\/strong> connection.execute(delete_stmt)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4><strong style=\"font-style: inherit;\">Deleting specific records<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\">By using a\u00a0where()\u00a0clause, you can target the\u00a0delete\u00a0statement to remove only certain records. For example, delete all rows from the\u00a0employees\u00a0table that had\u00a0id\u00a03 with the following delete statement:<\/p>\n<table width=\"665\">\n<tbody>\n<tr>\n<td width=\"0\"><\/td>\n<td width=\"630\">delete(employees).where(employees.columns.id <strong>==<\/strong> 3)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">Deleting a table completely<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\">You\u2019re now going to practice dropping individual tables from a database with the\u00a0.drop()\u00a0method, as well as\u00a0all\u00a0tables in a database with the\u00a0.drop_all()\u00a0method!<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\">Do be careful when deleting tables, as it\u2019s not simple or fast to restore large databases!<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\">Remember, you can check to see if a table exists on an\u00a0engine\u00a0with the\u00a0.exists(engine)\u00a0method.<\/p>\n<p style=\"text-align: center;\"><strong><a href=\"https:\/\/entri.app\/course\/python-programming-course\/\" target=\"_blank\" rel=\"noopener\">Grab the opportunity to learn Python with Entri! Click Here<\/a><\/strong><\/p>\n<h2 style=\"font-style: inherit;\"><span class=\"ez-toc-section\" id=\"Putting_it_all_together\"><\/span><strong style=\"font-style: inherit;\">Putting it all together<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">Census case study<\/strong><\/h3>\n<h4 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">Setup the engine and metadata<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\">In this exercise, your job is to create an engine to the database that will be used in this chapter. Then, you need to initialize its metadata.<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\">Recall how you did this by leveraging\u00a0create_engine()\u00a0and\u00a0MetaData().<\/p>\n<table width=\"665\">\n<tbody>\n<tr>\n<td width=\"0\">&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td width=\"630\"># Import create_engine, MetaData<\/p>\n<p><strong>from<\/strong> sqlalchemy <strong>import<\/strong> create_engine, MetaData<\/p>\n<p>&nbsp;<\/p>\n<p># Define an engine to connect to chapter5.sqlite: engine<\/p>\n<p>engine <strong>=<\/strong> create_engine(&#8216;sqlite:\/\/\/chapter5.sqlite&#8217;)<\/p>\n<p>&nbsp;<\/p>\n<p># Initialize MetaData: metadata<\/p>\n<p>metadata <strong>=<\/strong> MetaData()<\/td>\n<\/tr>\n<tr>\n<td width=\"0\">&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/td>\n<td width=\"621\">engine<\/p>\n<p># Engine(sqlite:\/\/\/chapter5.sqlite)<\/p>\n<p>&nbsp;<\/p>\n<p>type(engine)<\/p>\n<p># sqlalchemy.engine.base.Engine<\/p>\n<p>&nbsp;<\/p>\n<p>metadata<\/p>\n<p># MetaData(bind=None)<\/p>\n<p>&nbsp;<\/p>\n<p>type(metadata)<\/p>\n<p># sqlalchemy.sql.schema.MetaData<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4 style=\"font-style: inherit;\"><strong style=\"font-style: inherit;\">Create the table to the database<\/strong><\/h4>\n<p style=\"font-style: inherit; font-weight: inherit;\">Having setup the engine and initialized the metadata, you will now define the\u00a0census\u00a0table object and then create it in the database using the\u00a0metadata\u00a0and\u00a0engine.<\/p>\n<p style=\"font-style: inherit; font-weight: inherit;\">To create it in the database, you will have to use the\u00a0.create_all()\u00a0method on the\u00a0metadata\u00a0with\u00a0engine\u00a0as the argument.<\/p>\n<p>When creating columns of type\u00a0String(), it\u2019s important to spend some time thinking about what their maximum lengths should be.<\/p>\n<p><a href=\"https:\/\/entri.app\/course\/python-programming-course\/\" target=\"_blank\" rel=\"noopener\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-25522670 size-full\" src=\"https:\/\/entri.app\/blog\/wp-content\/uploads\/2022\/04\/Python-and-Machine-Learning-Rectangle-1.png\" alt=\"Python and Machine Learning Rectangle\" width=\"970\" height=\"250\" srcset=\"https:\/\/entri.app\/blog\/wp-content\/uploads\/2022\/04\/Python-and-Machine-Learning-Rectangle-1.png 970w, https:\/\/entri.app\/blog\/wp-content\/uploads\/2022\/04\/Python-and-Machine-Learning-Rectangle-1-300x77.png 300w, https:\/\/entri.app\/blog\/wp-content\/uploads\/2022\/04\/Python-and-Machine-Learning-Rectangle-1-768x198.png 768w, https:\/\/entri.app\/blog\/wp-content\/uploads\/2022\/04\/Python-and-Machine-Learning-Rectangle-1-750x193.png 750w\" sizes=\"auto, (max-width: 970px) 100vw, 970px\" \/><\/a><\/p>\n<p><strong>\u201cYOU DON\u2019T HAVE TO BE GREAT TO START, BUT YOU HAVE TO START TO BE GREAT.\u201d<\/strong><\/p>\n<h4><strong>Related Articles<\/strong><\/h4>\n<div class=\"table-responsive wprt_style_display\">\n<div class=\"table-responsive wprt_style_display\">\n<div class=\"table-responsive wprt_style_display\">\n<div class=\"table-responsive wprt_style_display\">\n<table class=\"table\" dir=\"ltr\" border=\"1\" cellspacing=\"0\" cellpadding=\"0\">\n<colgroup>\n<col width=\"329\" \/>\n<col width=\"309\" \/><\/colgroup>\n<tbody>\n<tr>\n<td data-sheets-value=\"{&quot;1&quot;:2,&quot;2&quot;:&quot;Kerala PSC VFA Syllabus&quot;}\" data-sheets-hyperlink=\"https:\/\/entri.app\/blog\/kerala-psc-village-field-assistant-vfa-syllabus-exam-pattern\/\"><strong><a class=\"in-cell-link\" href=\"https:\/\/entri.app\/blog\/step-by-step-guide-for-getting-a-job-as-a-python-developer\/42\" target=\"_blank\" rel=\"noopener\">A Step-by-Step Guide for Getting a Job as a Python Developer<\/a><\/strong><\/td>\n<td data-sheets-value=\"{&quot;1&quot;:2,&quot;2&quot;:&quot;Kerala PSC VFA Mock Test&quot;}\" data-sheets-hyperlink=\"https:\/\/entri.app\/blog\/kerala-psc-vfa-free-mock-test\/\"><strong><a class=\"in-cell-link\" href=\"https:\/\/entri.app\/blog\/why-python-is-used-for-data-science\/\" target=\"_blank\" rel=\"noopener\">Why Python Is Used For Data Science?<\/a><\/strong><\/td>\n<\/tr>\n<tr>\n<td data-sheets-value=\"{&quot;1&quot;:2,&quot;2&quot;:&quot;Kerala PSC VFA Exam Date&quot;}\" data-sheets-hyperlink=\"https:\/\/entri.app\/blog\/kerala-psc-vfa-exam-date\/\"><strong><a class=\"in-cell-link\" href=\"https:\/\/entri.app\/blog\/step-by-step-guide-for-getting-a-job-as-a-python-developer\/\" target=\"_blank\" rel=\"noopener\">Guide for getting a job as a Python Developer<\/a><\/strong><\/td>\n<td data-sheets-value=\"{&quot;1&quot;:2,&quot;2&quot;:&quot;Kerala PSC VFA Video Course&quot;}\"><strong><a class=\"in-cell-link\" href=\"https:\/\/entri.app\/blog\/top-python-interview-questions-and-answers\/\" target=\"_blank\" rel=\"noopener\">Python Advanced Interview Questions and Answers<\/a><\/strong><\/td>\n<\/tr>\n<tr>\n<td data-sheets-value=\"{&quot;1&quot;:2,&quot;2&quot;:&quot;Kerala PSC VFA Application Form&quot;}\" data-sheets-hyperlink=\"https:\/\/entri.app\/blog\/kerala-psc-vfa-apply-online\/\"><strong><a class=\"in-cell-link\" href=\"https:\/\/entri.app\/blog\/python-online-course\/\" target=\"_blank\" rel=\"noopener\">Best Online Python Course with Certificate<\/a><\/strong><\/td>\n<td data-sheets-value=\"{&quot;1&quot;:2,&quot;2&quot;:&quot;Kerala PSC VFA Study Materials&quot;}\" data-sheets-hyperlink=\"https:\/\/entri.app\/blog\/kerala-psc-vfa-study-material\/\"><strong><a class=\"in-cell-link\" href=\"https:\/\/entri.app\/blog\/type-conversion-in-python\/\" target=\"_blank\" rel=\"noopener\">What is Type Conversion in Python?<\/a><\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Python is one of the most popular programming language. Due to its simplified syntax, Programmers can develop a wide range of software applications, fast and easy. With Python, you can easily build Data Science and Machine Learning projects, as well as general purpose applications. When combined with the popular SQL Server Data Platform, it provides [&hellip;]<\/p>\n","protected":false},"author":55,"featured_media":25524381,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[802,558,1888],"tags":[],"class_list":["post-25524374","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-articles","category-general-knowledge","category-python-programming"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.6 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Introduction To Databases In Python- A Detailed Study - Entri Blog<\/title>\n<meta name=\"description\" content=\"Introduction To Databases In Python- Structured Query Language (SQL or &quot;Sequel&quot;) is the way we communicate with a relational database. It&#039;s an intuitive and powerful language and mastering it is rapidly becoming a prerequisite for career paths outside of software development.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Introduction To Databases In Python- A Detailed Study - Entri Blog\" \/>\n<meta property=\"og:description\" content=\"Introduction To Databases In Python- Structured Query Language (SQL or &quot;Sequel&quot;) is the way we communicate with a relational database. It&#039;s an intuitive and powerful language and mastering it is rapidly becoming a prerequisite for career paths outside of software development.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/\" \/>\n<meta property=\"og:site_name\" content=\"Entri Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/entri.me\/\" \/>\n<meta property=\"article:published_time\" content=\"2022-05-16T09:27:21+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-04-27T07:25:44+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/entri.app\/blog\/wp-content\/uploads\/2022\/05\/Introduction-To-Databases-In-Python-A-Detailed-Study.png\" \/>\n\t<meta property=\"og:image:width\" content=\"820\" \/>\n\t<meta property=\"og:image:height\" content=\"615\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Ayesha Surayya\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@entri_app\" \/>\n<meta name=\"twitter:site\" content=\"@entri_app\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Ayesha Surayya\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"22 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/\"},\"author\":{\"name\":\"Ayesha Surayya\",\"@id\":\"https:\/\/entri.app\/blog\/#\/schema\/person\/568cc9d6e77fd5d01033b61c88343097\"},\"headline\":\"Introduction To Databases In Python- A Detailed Study\",\"datePublished\":\"2022-05-16T09:27:21+00:00\",\"dateModified\":\"2023-04-27T07:25:44+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/\"},\"wordCount\":4421,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/entri.app\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/entri.app\/blog\/wp-content\/uploads\/2022\/05\/Introduction-To-Databases-In-Python-A-Detailed-Study.png\",\"articleSection\":[\"Articles\",\"General Knowledge\",\"Python Programming\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/\",\"url\":\"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/\",\"name\":\"Introduction To Databases In Python- A Detailed Study - Entri Blog\",\"isPartOf\":{\"@id\":\"https:\/\/entri.app\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/entri.app\/blog\/wp-content\/uploads\/2022\/05\/Introduction-To-Databases-In-Python-A-Detailed-Study.png\",\"datePublished\":\"2022-05-16T09:27:21+00:00\",\"dateModified\":\"2023-04-27T07:25:44+00:00\",\"description\":\"Introduction To Databases In Python- Structured Query Language (SQL or \\\"Sequel\\\") is the way we communicate with a relational database. It's an intuitive and powerful language and mastering it is rapidly becoming a prerequisite for career paths outside of software development.\",\"breadcrumb\":{\"@id\":\"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/#primaryimage\",\"url\":\"https:\/\/entri.app\/blog\/wp-content\/uploads\/2022\/05\/Introduction-To-Databases-In-Python-A-Detailed-Study.png\",\"contentUrl\":\"https:\/\/entri.app\/blog\/wp-content\/uploads\/2022\/05\/Introduction-To-Databases-In-Python-A-Detailed-Study.png\",\"width\":820,\"height\":615,\"caption\":\"Introduction To Databases In Python- A Detailed Study\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/entri.app\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Python Programming\",\"item\":\"https:\/\/entri.app\/blog\/category\/python-programming\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Introduction To Databases In Python- A Detailed Study\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/entri.app\/blog\/#website\",\"url\":\"https:\/\/entri.app\/blog\/\",\"name\":\"Entri Blog\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/entri.app\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/entri.app\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/entri.app\/blog\/#organization\",\"name\":\"Entri App\",\"url\":\"https:\/\/entri.app\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/entri.app\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/entri.app\/blog\/wp-content\/uploads\/2019\/10\/Entri-Logo-1.png\",\"contentUrl\":\"https:\/\/entri.app\/blog\/wp-content\/uploads\/2019\/10\/Entri-Logo-1.png\",\"width\":989,\"height\":446,\"caption\":\"Entri App\"},\"image\":{\"@id\":\"https:\/\/entri.app\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/entri.me\/\",\"https:\/\/x.com\/entri_app\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/entri.app\/blog\/#\/schema\/person\/568cc9d6e77fd5d01033b61c88343097\",\"name\":\"Ayesha Surayya\",\"url\":\"https:\/\/entri.app\/blog\/author\/ayesha-surayya\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Introduction To Databases In Python- A Detailed Study - Entri Blog","description":"Introduction To Databases In Python- Structured Query Language (SQL or \"Sequel\") is the way we communicate with a relational database. It's an intuitive and powerful language and mastering it is rapidly becoming a prerequisite for career paths outside of software development.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/","og_locale":"en_US","og_type":"article","og_title":"Introduction To Databases In Python- A Detailed Study - Entri Blog","og_description":"Introduction To Databases In Python- Structured Query Language (SQL or \"Sequel\") is the way we communicate with a relational database. It's an intuitive and powerful language and mastering it is rapidly becoming a prerequisite for career paths outside of software development.","og_url":"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/","og_site_name":"Entri Blog","article_publisher":"https:\/\/www.facebook.com\/entri.me\/","article_published_time":"2022-05-16T09:27:21+00:00","article_modified_time":"2023-04-27T07:25:44+00:00","og_image":[{"width":820,"height":615,"url":"https:\/\/entri.app\/blog\/wp-content\/uploads\/2022\/05\/Introduction-To-Databases-In-Python-A-Detailed-Study.png","type":"image\/png"}],"author":"Ayesha Surayya","twitter_card":"summary_large_image","twitter_creator":"@entri_app","twitter_site":"@entri_app","twitter_misc":{"Written by":"Ayesha Surayya","Est. reading time":"22 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/#article","isPartOf":{"@id":"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/"},"author":{"name":"Ayesha Surayya","@id":"https:\/\/entri.app\/blog\/#\/schema\/person\/568cc9d6e77fd5d01033b61c88343097"},"headline":"Introduction To Databases In Python- A Detailed Study","datePublished":"2022-05-16T09:27:21+00:00","dateModified":"2023-04-27T07:25:44+00:00","mainEntityOfPage":{"@id":"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/"},"wordCount":4421,"commentCount":0,"publisher":{"@id":"https:\/\/entri.app\/blog\/#organization"},"image":{"@id":"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/#primaryimage"},"thumbnailUrl":"https:\/\/entri.app\/blog\/wp-content\/uploads\/2022\/05\/Introduction-To-Databases-In-Python-A-Detailed-Study.png","articleSection":["Articles","General Knowledge","Python Programming"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/","url":"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/","name":"Introduction To Databases In Python- A Detailed Study - Entri Blog","isPartOf":{"@id":"https:\/\/entri.app\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/#primaryimage"},"image":{"@id":"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/#primaryimage"},"thumbnailUrl":"https:\/\/entri.app\/blog\/wp-content\/uploads\/2022\/05\/Introduction-To-Databases-In-Python-A-Detailed-Study.png","datePublished":"2022-05-16T09:27:21+00:00","dateModified":"2023-04-27T07:25:44+00:00","description":"Introduction To Databases In Python- Structured Query Language (SQL or \"Sequel\") is the way we communicate with a relational database. It's an intuitive and powerful language and mastering it is rapidly becoming a prerequisite for career paths outside of software development.","breadcrumb":{"@id":"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/#primaryimage","url":"https:\/\/entri.app\/blog\/wp-content\/uploads\/2022\/05\/Introduction-To-Databases-In-Python-A-Detailed-Study.png","contentUrl":"https:\/\/entri.app\/blog\/wp-content\/uploads\/2022\/05\/Introduction-To-Databases-In-Python-A-Detailed-Study.png","width":820,"height":615,"caption":"Introduction To Databases In Python- A Detailed Study"},{"@type":"BreadcrumbList","@id":"https:\/\/entri.app\/blog\/introduction-to-databases-in-python-a-detailed-study\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/entri.app\/blog\/"},{"@type":"ListItem","position":2,"name":"Python Programming","item":"https:\/\/entri.app\/blog\/category\/python-programming\/"},{"@type":"ListItem","position":3,"name":"Introduction To Databases In Python- A Detailed Study"}]},{"@type":"WebSite","@id":"https:\/\/entri.app\/blog\/#website","url":"https:\/\/entri.app\/blog\/","name":"Entri Blog","description":"","publisher":{"@id":"https:\/\/entri.app\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/entri.app\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/entri.app\/blog\/#organization","name":"Entri App","url":"https:\/\/entri.app\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/entri.app\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/entri.app\/blog\/wp-content\/uploads\/2019\/10\/Entri-Logo-1.png","contentUrl":"https:\/\/entri.app\/blog\/wp-content\/uploads\/2019\/10\/Entri-Logo-1.png","width":989,"height":446,"caption":"Entri App"},"image":{"@id":"https:\/\/entri.app\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/entri.me\/","https:\/\/x.com\/entri_app"]},{"@type":"Person","@id":"https:\/\/entri.app\/blog\/#\/schema\/person\/568cc9d6e77fd5d01033b61c88343097","name":"Ayesha Surayya","url":"https:\/\/entri.app\/blog\/author\/ayesha-surayya\/"}]}},"_links":{"self":[{"href":"https:\/\/entri.app\/blog\/wp-json\/wp\/v2\/posts\/25524374","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/entri.app\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/entri.app\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/entri.app\/blog\/wp-json\/wp\/v2\/users\/55"}],"replies":[{"embeddable":true,"href":"https:\/\/entri.app\/blog\/wp-json\/wp\/v2\/comments?post=25524374"}],"version-history":[{"count":6,"href":"https:\/\/entri.app\/blog\/wp-json\/wp\/v2\/posts\/25524374\/revisions"}],"predecessor-version":[{"id":25559316,"href":"https:\/\/entri.app\/blog\/wp-json\/wp\/v2\/posts\/25524374\/revisions\/25559316"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/entri.app\/blog\/wp-json\/wp\/v2\/media\/25524381"}],"wp:attachment":[{"href":"https:\/\/entri.app\/blog\/wp-json\/wp\/v2\/media?parent=25524374"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/entri.app\/blog\/wp-json\/wp\/v2\/categories?post=25524374"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/entri.app\/blog\/wp-json\/wp\/v2\/tags?post=25524374"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}