SELECT id, NewsHeadline as news_headline, NewsText as news_text, state CreatedDate as created_on FROM News WHERE CreatedDate >= DATEADD(day,-7, GETDATE())
Here is what the above code is Doing:
1. We are selecting the columns we want to return from the database.
2. We are using the AS keyword to rename the columns to something more Python-friendly.
3. We are using the DATEADD function to return only the rows where the CreatedDate is greater than or equal to 7 days ago.
Now that we have our SQL query, we can use the pandas read_sql_query function to read the results of the query into a DataFrame.
The read_sql_query needs a connection object that represents the database we want to query. We get this connection object by using the create_engine function from the sqlalchemy library.
The create_engine function takes a database url as an argument. The database url is made up of several parts:
The dialect and driver are specific to the database you are connecting to. For SQL Server, the dialect is mssql and the driver is pyodbc.
The username and password are the credentials you use to log into the database.
The host is the name of the server where the database is located and the port is the port that the database listens on.
The database is the name of the database you want to connect to.
Here’s what the database url looks like for our SQL Server database:
We can use the create_engine function to create an engine for our database:
engine = create_engine(‘mssql+pyodbc://sa:Password1@localhost:1433/NewsDB’)
Now that we have an engine, we can use the read_sql_query function to read the results of our query into a DataFrame:
df = pd.read_sql_query(sql, engine)
Let’s put all of this together into a function that we can use to get the latest news from our database: