<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Querying SQL (intro)
Reading in data
In this tutorial, we'll use the mtcars data (source) that comes packaged with siuba. This data contains information about 32 cars, like their miles per gallon (mpg), and number of cylinders (cyl). This data in siuba is a pandas DataFrame.
Step1:  First, we'll use sqlalchemy, and the pandas method create_engine to copy the data into a sqlite table.
Once we have that, siuba can use a class called LazyTbl to connect to the table.
Step2:  Notice that siuba by default prints a glimpse into the current data, along with some extra information about the database we're connected to. However, in this case, there are more than 5 rows of data. In order to get all of it back as a pandas DataFrame we need to collect() it.
Connecting to existing database
While we use sqlalchemy.create_engine to connect to a database in the previous section, LazyTbl also accepts a string as its first argument, followed by a table name.
This is shown below, with placeholder variables, like "username" and "password". See this SqlAlchemy doc for more.
python
tbl = LazyTbl(
    "postgresql
Step3:  Basic queries
A core goal of siuba is to make sure most column operations and methods that work on a pandas DataFrame, also work with a SQL table. As a result, the examples in these docs also work when applied to SQL.
This is shown below for filter, summarize, and mutate.
<ASSISTANT_TASK:>
Python Code:
from siuba.data import mtcars
mtcars.head()
Explanation: Querying SQL (intro)
Reading in data
In this tutorial, we'll use the mtcars data (source) that comes packaged with siuba. This data contains information about 32 cars, like their miles per gallon (mpg), and number of cylinders (cyl). This data in siuba is a pandas DataFrame.
End of explanation
from sqlalchemy import create_engine
from siuba.sql import LazyTbl
# copy in to sqlite
engine = create_engine("sqlite:///:memory:")
mtcars.to_sql("mtcars", engine, if_exists = "replace")
# connect with siuba
tbl_mtcars = LazyTbl(engine, "mtcars")
tbl_mtcars
Explanation: First, we'll use sqlalchemy, and the pandas method create_engine to copy the data into a sqlite table.
Once we have that, siuba can use a class called LazyTbl to connect to the table.
End of explanation
from siuba import head, collect, show_query
tbl_mtcars >> head(2) >> collect()
tbl_mtcars >> head(2) >> show_query()
Explanation: Notice that siuba by default prints a glimpse into the current data, along with some extra information about the database we're connected to. However, in this case, there are more than 5 rows of data. In order to get all of it back as a pandas DataFrame we need to collect() it.
Connecting to existing database
While we use sqlalchemy.create_engine to connect to a database in the previous section, LazyTbl also accepts a string as its first argument, followed by a table name.
This is shown below, with placeholder variables, like "username" and "password". See this SqlAlchemy doc for more.
python
tbl = LazyTbl(
    "postgresql://username:password@localhost:5432/dbname",
    "tablename"
    )
Collecting data and previewing queries
End of explanation
from siuba import _, filter, select, group_by, summarize, mutate
tbl_mtcars >> filter(_.cyl == 6)
(tbl_mtcars
  >> group_by(_.cyl)
  >> summarize(avg_mpg = _.mpg.mean())
  )
tbl_mtcars >> select(_.mpg, _.cyl, _.endswith('t'))
tbl_mtcars >> \
  mutate(feetpg = _.mpg * 5290, inchpg = _.feetpg * 12)
Explanation: Basic queries
A core goal of siuba is to make sure most column operations and methods that work on a pandas DataFrame, also work with a SQL table. As a result, the examples in these docs also work when applied to SQL.
This is shown below for filter, summarize, and mutate.
End of explanation
<END_TASK>