Handling complex SQL statements with Python and SQLAlchemy
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I am building an analytics page backed up with Python(Flask) and Redshift as data source. Data is presented in a start schema, so all I want to do is basic aggregation and filtering in specified time frame (sounds not like a rocket science). Though I can't find any elegant way to do this.
Let's say I have a SQL query that nicely provides daily stats for current month.
with current_month as (
select date
from date_d
where month_name = 'November' AND year_actual = '2018'
order by date
),
filtered as (
select date ,fact.id, fact.created_id,
from fact
join date_d ON date_d.id = fact.created_id
where date_d.month_name = 'November' AND date_d.year_actual = '2018' AND fact.foo = 'bar'
),
total as (
SELECT COUNT(id),DATE(date)
from filtered GROUP BY 2),
SELECT current_month.date, COALESCE(total.count,0) as total
from current_month
LEFT JOIN total ON current_month.date = total.date
group by 1,
order by current_month.date
Is there any way I can execute this query and load results into some predefined data structure? I looked at SQLAlchemy, because I didn't feel like executing raw SQL Queries, but ORM looks useless here.
Looks like the only way is to execute raw SQL and load it into some sort of schema (using marshmallow).
I looked at this article which is close but not that elaborate.
Maybe I am missing something? People should do such things rather often.
Or maybe my approach is wrong over all?
P.S. Simple filtering works perfectly on my schema with Flask-Restless
python sql sqlalchemy data-analysis star-schema
asked Nov 16 '18 at 16:40
Semant1kaSemant1ka
307414
add a comment |
I am building an analytics page backed up with Python(Flask) and Redshift as data source. Data is presented in a start schema, so all I want to do is basic aggregation and filtering in specified time frame (sounds not like a rocket science). Though I can't find any elegant way to do this.
Let's say I have a SQL query that nicely provides daily stats for current month.
with current_month as (
select date
from date_d
where month_name = 'November' AND year_actual = '2018'
order by date
),
filtered as (
select date ,fact.id, fact.created_id,
from fact
join date_d ON date_d.id = fact.created_id
where date_d.month_name = 'November' AND date_d.year_actual = '2018' AND fact.foo = 'bar'
),
total as (
SELECT COUNT(id),DATE(date)
from filtered GROUP BY 2),
SELECT current_month.date, COALESCE(total.count,0) as total
from current_month
LEFT JOIN total ON current_month.date = total.date
group by 1,
order by current_month.date
Is there any way I can execute this query and load results into some predefined data structure? I looked at SQLAlchemy, because I didn't feel like executing raw SQL Queries, but ORM looks useless here.
Looks like the only way is to execute raw SQL and load it into some sort of schema (using marshmallow).
I looked at this article which is close but not that elaborate.
Maybe I am missing something? People should do such things rather often.
Or maybe my approach is wrong over all?
P.S. Simple filtering works perfectly on my schema with Flask-Restless
python sql sqlalchemy data-analysis star-schema
asked Nov 16 '18 at 16:40
Semant1kaSemant1ka
307414
add a comment |
I am building an analytics page backed up with Python(Flask) and Redshift as data source. Data is presented in a start schema, so all I want to do is basic aggregation and filtering in specified time frame (sounds not like a rocket science). Though I can't find any elegant way to do this.
Let's say I have a SQL query that nicely provides daily stats for current month.
with current_month as (
select date
from date_d
where month_name = 'November' AND year_actual = '2018'
order by date
),
filtered as (
select date ,fact.id, fact.created_id,
from fact
join date_d ON date_d.id = fact.created_id
where date_d.month_name = 'November' AND date_d.year_actual = '2018' AND fact.foo = 'bar'
),
total as (
SELECT COUNT(id),DATE(date)
from filtered GROUP BY 2),
SELECT current_month.date, COALESCE(total.count,0) as total
from current_month
LEFT JOIN total ON current_month.date = total.date
group by 1,
order by current_month.date
Is there any way I can execute this query and load results into some predefined data structure? I looked at SQLAlchemy, because I didn't feel like executing raw SQL Queries, but ORM looks useless here.
Looks like the only way is to execute raw SQL and load it into some sort of schema (using marshmallow).
I looked at this article which is close but not that elaborate.
Maybe I am missing something? People should do such things rather often.
Or maybe my approach is wrong over all?
P.S. Simple filtering works perfectly on my schema with Flask-Restless
python sql sqlalchemy data-analysis star-schema
asked Nov 16 '18 at 16:40
Semant1kaSemant1ka
307414
I am building an analytics page backed up with Python(Flask) and Redshift as data source. Data is presented in a start schema, so all I want to do is basic aggregation and filtering in specified time frame (sounds not like a rocket science). Though I can't find any elegant way to do this.
Let's say I have a SQL query that nicely provides daily stats for current month.
with current_month as (
select date
from date_d
where month_name = 'November' AND year_actual = '2018'
order by date
),
filtered as (
select date ,fact.id, fact.created_id,
from fact
join date_d ON date_d.id = fact.created_id
where date_d.month_name = 'November' AND date_d.year_actual = '2018' AND fact.foo = 'bar'
),
total as (
SELECT COUNT(id),DATE(date)
from filtered GROUP BY 2),
SELECT current_month.date, COALESCE(total.count,0) as total
from current_month
LEFT JOIN total ON current_month.date = total.date
group by 1,
order by current_month.date
Is there any way I can execute this query and load results into some predefined data structure? I looked at SQLAlchemy, because I didn't feel like executing raw SQL Queries, but ORM looks useless here.
Looks like the only way is to execute raw SQL and load it into some sort of schema (using marshmallow).
I looked at this article which is close but not that elaborate.
Maybe I am missing something? People should do such things rather often.
Or maybe my approach is wrong over all?
P.S. Simple filtering works perfectly on my schema with Flask-Restless
python sql sqlalchemy data-analysis star-schema
python sql sqlalchemy data-analysis star-schema
asked Nov 16 '18 at 16:40
Semant1kaSemant1ka
307414
asked Nov 16 '18 at 16:40
Semant1kaSemant1ka
307414
asked Nov 16 '18 at 16:40
Semant1kaSemant1ka
307414
asked Nov 16 '18 at 16:40
Semant1kaSemant1ka
307414
asked Nov 16 '18 at 16:40
Semant1kaSemant1ka
307414
307414
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Try Pandas and Pyodbc:
import pandas as pd
import pyodbc
server = 'mysrvr'
db = 'mydb'
conn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+db+';Trusted_Connection=yes')
sql = "select col1, col2, col3 from mytable"
my_dataframe = pd.read_sql(sql,conn)
my_dataframe will be your data frame/data structure.
answered Nov 16 '18 at 16:55
CSYCSY
1155
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53342056%2fhandling-complex-sql-statements-with-python-and-sqlalchemy%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Try Pandas and Pyodbc:
import pandas as pd
import pyodbc
server = 'mysrvr'
db = 'mydb'
conn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+db+';Trusted_Connection=yes')
sql = "select col1, col2, col3 from mytable"
my_dataframe = pd.read_sql(sql,conn)
my_dataframe will be your data frame/data structure.
answered Nov 16 '18 at 16:55
CSYCSY
1155
add a comment |
Try Pandas and Pyodbc:
import pandas as pd
import pyodbc
server = 'mysrvr'
db = 'mydb'
conn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+db+';Trusted_Connection=yes')
sql = "select col1, col2, col3 from mytable"
my_dataframe = pd.read_sql(sql,conn)
my_dataframe will be your data frame/data structure.
answered Nov 16 '18 at 16:55
CSYCSY
1155
add a comment |
Try Pandas and Pyodbc:
import pandas as pd
import pyodbc
server = 'mysrvr'
db = 'mydb'
conn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+db+';Trusted_Connection=yes')
sql = "select col1, col2, col3 from mytable"
my_dataframe = pd.read_sql(sql,conn)
my_dataframe will be your data frame/data structure.
answered Nov 16 '18 at 16:55
CSYCSY
1155
Try Pandas and Pyodbc:
import pandas as pd
import pyodbc
server = 'mysrvr'
db = 'mydb'
conn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+db+';Trusted_Connection=yes')
sql = "select col1, col2, col3 from mytable"
my_dataframe = pd.read_sql(sql,conn)
my_dataframe will be your data frame/data structure.
answered Nov 16 '18 at 16:55
CSYCSY
1155
answered Nov 16 '18 at 16:55
CSYCSY
1155
answered Nov 16 '18 at 16:55
CSYCSY
1155
answered Nov 16 '18 at 16:55
CSYCSY
1155
1155
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53342056%2fhandling-complex-sql-statements-with-python-and-sqlalchemy%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
