Using Python: how to get table names from sql query and add a word before the table schema where schemas are...
0
I have a json file that I am using as a dictionary in Python. The json file is very large. I am trying to write a python code to update each "query" by adding a "source." before the table schema. then use the updated dictionary for other programming purposes.
The SQL scripts could have joins, cartesian joins, subqueries, etc.
Expected output:
"query": "SELECT a.column1, b.column2
FROM source.abcd.hist a, source.efgh.present b
WHERE (select column3, column4 from UPS where a.id = b.id )"
"query": "SELECT a.column1, b.column2
FROM source.apple.hist a, source.mango.present b
WHERE (select column3, column4 from source.my.ORANGE where a.id = b.id
{"result":[{
"query": "SELECT a.column1, b.column2
FROM abcd.hist a, efgh.present b
WHERE (select column3, column4 from UPS where a.id = b.id )"
},
{"query": "SELECT a.column1, b.column2
FROM apple.hist a, mango.present b
WHERE (select column3, column4 from my.ORANGE where a.id = b.id )"}
]}
python sql python-3.x
edited Nov 15 '18 at 6:04
marc_s
579k12911181264
asked Nov 15 '18 at 0:59
MonaMona
265
add a comment |
0
I have a json file that I am using as a dictionary in Python. The json file is very large. I am trying to write a python code to update each "query" by adding a "source." before the table schema. then use the updated dictionary for other programming purposes.
The SQL scripts could have joins, cartesian joins, subqueries, etc.
Expected output:
"query": "SELECT a.column1, b.column2
FROM source.abcd.hist a, source.efgh.present b
WHERE (select column3, column4 from UPS where a.id = b.id )"
"query": "SELECT a.column1, b.column2
FROM source.apple.hist a, source.mango.present b
WHERE (select column3, column4 from source.my.ORANGE where a.id = b.id
{"result":[{
"query": "SELECT a.column1, b.column2
FROM abcd.hist a, efgh.present b
WHERE (select column3, column4 from UPS where a.id = b.id )"
},
{"query": "SELECT a.column1, b.column2
FROM apple.hist a, mango.present b
WHERE (select column3, column4 from my.ORANGE where a.id = b.id )"}
]}
python sql python-3.x
edited Nov 15 '18 at 6:04
marc_s
579k12911181264
asked Nov 15 '18 at 0:59
MonaMona
265
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSIJOINsyntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged
– marc_s
Nov 15 '18 at 6:04
add a comment |
0
0
0
I have a json file that I am using as a dictionary in Python. The json file is very large. I am trying to write a python code to update each "query" by adding a "source." before the table schema. then use the updated dictionary for other programming purposes.
The SQL scripts could have joins, cartesian joins, subqueries, etc.
Expected output:
"query": "SELECT a.column1, b.column2
FROM source.abcd.hist a, source.efgh.present b
WHERE (select column3, column4 from UPS where a.id = b.id )"
"query": "SELECT a.column1, b.column2
FROM source.apple.hist a, source.mango.present b
WHERE (select column3, column4 from source.my.ORANGE where a.id = b.id
{"result":[{
"query": "SELECT a.column1, b.column2
FROM abcd.hist a, efgh.present b
WHERE (select column3, column4 from UPS where a.id = b.id )"
},
{"query": "SELECT a.column1, b.column2
FROM apple.hist a, mango.present b
WHERE (select column3, column4 from my.ORANGE where a.id = b.id )"}
]}
python sql python-3.x
edited Nov 15 '18 at 6:04
marc_s
579k12911181264
asked Nov 15 '18 at 0:59
MonaMona
265
I have a json file that I am using as a dictionary in Python. The json file is very large. I am trying to write a python code to update each "query" by adding a "source." before the table schema. then use the updated dictionary for other programming purposes.
The SQL scripts could have joins, cartesian joins, subqueries, etc.
Expected output:
"query": "SELECT a.column1, b.column2
FROM source.abcd.hist a, source.efgh.present b
WHERE (select column3, column4 from UPS where a.id = b.id )"
"query": "SELECT a.column1, b.column2
FROM source.apple.hist a, source.mango.present b
WHERE (select column3, column4 from source.my.ORANGE where a.id = b.id
{"result":[{
"query": "SELECT a.column1, b.column2
FROM abcd.hist a, efgh.present b
WHERE (select column3, column4 from UPS where a.id = b.id )"
},
{"query": "SELECT a.column1, b.column2
FROM apple.hist a, mango.present b
WHERE (select column3, column4 from my.ORANGE where a.id = b.id )"}
]}
python sql python-3.x
python sql python-3.x
edited Nov 15 '18 at 6:04
marc_s
579k12911181264
asked Nov 15 '18 at 0:59
MonaMona
265
edited Nov 15 '18 at 6:04
marc_s
579k12911181264
asked Nov 15 '18 at 0:59
MonaMona
265
edited Nov 15 '18 at 6:04
marc_s
579k12911181264
edited Nov 15 '18 at 6:04
marc_s
579k12911181264
edited Nov 15 '18 at 6:04
marc_s
579k12911181264
579k12911181264
asked Nov 15 '18 at 0:59
MonaMona
265
asked Nov 15 '18 at 0:59
MonaMona
265
asked Nov 15 '18 at 0:59
MonaMona
265
265
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSIJOINsyntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged
– marc_s
Nov 15 '18 at 6:04
add a comment |
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSIJOINsyntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged
– marc_s
Nov 15 '18 at 6:04
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI
JOIN syntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged– marc_s
Nov 15 '18 at 6:04
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI
JOIN syntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged– marc_s
Nov 15 '18 at 6:04
add a comment |
1 Answer
1
active
oldest
votes
0
Your result is a dictionary {}, with the first key 'result' containing a list of dictionaries {} where each dictionary has a key 'query' that goes to a value that looks like a SQL query.
Assuming your output object is called op you can get your desired result as follows:
for k in op['result']: # For each dictionary in result
print(str(k)[1:-1]) # Cast to a string and strip curlies
EDIT:
def addSource(q):
lines = q.split("n")
for n,k in enumerate(lines):
if(k.startswith("FROM")):
q[n] = k.replace("FROM ","FROM source.").replace(", ",", source.")
return("n".join(q))
and with that,
for n,k in enumerate(op['result']):
op['result'][n]["query"] = addSource(k["query"])
edited Nov 15 '18 at 6:03
marc_s
579k12911181264
answered Nov 15 '18 at 1:05
kpiekpie
3,61341432
I need to add the "source." word before the table schemas and then update the dictionary with the updated value.
– Mona
Nov 15 '18 at 1:07
Does the code added under the edit make sense? / work?
– kpie
Nov 15 '18 at 1:25
I am getting this error " for n,k in op['result']: ValueError: not enough values to unpack (expected 2, got 1)"
– Mona
Nov 15 '18 at 1:50
my mistake, that has to be for n,k in enumerate(op['result'])
– kpie
Nov 15 '18 at 1:55
thanks but now getting this error " return("n".join(1)) TypeError: can only join an iterable"
– Mona
Nov 15 '18 at 1:59
|
show 3 more comments
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
});
}
});
draft saved
draft discarded
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%2f53310991%2fusing-python-how-to-get-table-names-from-sql-query-and-add-a-word-before-the-ta%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
0
Your result is a dictionary {}, with the first key 'result' containing a list of dictionaries {} where each dictionary has a key 'query' that goes to a value that looks like a SQL query.
Assuming your output object is called op you can get your desired result as follows:
for k in op['result']: # For each dictionary in result
print(str(k)[1:-1]) # Cast to a string and strip curlies
EDIT:
def addSource(q):
lines = q.split("n")
for n,k in enumerate(lines):
if(k.startswith("FROM")):
q[n] = k.replace("FROM ","FROM source.").replace(", ",", source.")
return("n".join(q))
and with that,
for n,k in enumerate(op['result']):
op['result'][n]["query"] = addSource(k["query"])
edited Nov 15 '18 at 6:03
marc_s
579k12911181264
answered Nov 15 '18 at 1:05
kpiekpie
3,61341432
I need to add the "source." word before the table schemas and then update the dictionary with the updated value.
– Mona
Nov 15 '18 at 1:07
Does the code added under the edit make sense? / work?
– kpie
Nov 15 '18 at 1:25
I am getting this error " for n,k in op['result']: ValueError: not enough values to unpack (expected 2, got 1)"
– Mona
Nov 15 '18 at 1:50
my mistake, that has to be for n,k in enumerate(op['result'])
– kpie
Nov 15 '18 at 1:55
thanks but now getting this error " return("n".join(1)) TypeError: can only join an iterable"
– Mona
Nov 15 '18 at 1:59
|
show 3 more comments
0
Your result is a dictionary {}, with the first key 'result' containing a list of dictionaries {} where each dictionary has a key 'query' that goes to a value that looks like a SQL query.
Assuming your output object is called op you can get your desired result as follows:
for k in op['result']: # For each dictionary in result
print(str(k)[1:-1]) # Cast to a string and strip curlies
EDIT:
def addSource(q):
lines = q.split("n")
for n,k in enumerate(lines):
if(k.startswith("FROM")):
q[n] = k.replace("FROM ","FROM source.").replace(", ",", source.")
return("n".join(q))
and with that,
for n,k in enumerate(op['result']):
op['result'][n]["query"] = addSource(k["query"])
edited Nov 15 '18 at 6:03
marc_s
579k12911181264
answered Nov 15 '18 at 1:05
kpiekpie
3,61341432
I need to add the "source." word before the table schemas and then update the dictionary with the updated value.
– Mona
Nov 15 '18 at 1:07
Does the code added under the edit make sense? / work?
– kpie
Nov 15 '18 at 1:25
I am getting this error " for n,k in op['result']: ValueError: not enough values to unpack (expected 2, got 1)"
– Mona
Nov 15 '18 at 1:50
my mistake, that has to be for n,k in enumerate(op['result'])
– kpie
Nov 15 '18 at 1:55
thanks but now getting this error " return("n".join(1)) TypeError: can only join an iterable"
– Mona
Nov 15 '18 at 1:59
|
show 3 more comments
0
0
0
Your result is a dictionary {}, with the first key 'result' containing a list of dictionaries {} where each dictionary has a key 'query' that goes to a value that looks like a SQL query.
Assuming your output object is called op you can get your desired result as follows:
for k in op['result']: # For each dictionary in result
print(str(k)[1:-1]) # Cast to a string and strip curlies
EDIT:
def addSource(q):
lines = q.split("n")
for n,k in enumerate(lines):
if(k.startswith("FROM")):
q[n] = k.replace("FROM ","FROM source.").replace(", ",", source.")
return("n".join(q))
and with that,
for n,k in enumerate(op['result']):
op['result'][n]["query"] = addSource(k["query"])
edited Nov 15 '18 at 6:03
marc_s
579k12911181264
answered Nov 15 '18 at 1:05
kpiekpie
3,61341432
Your result is a dictionary {}, with the first key 'result' containing a list of dictionaries {} where each dictionary has a key 'query' that goes to a value that looks like a SQL query.
Assuming your output object is called op you can get your desired result as follows:
for k in op['result']: # For each dictionary in result
print(str(k)[1:-1]) # Cast to a string and strip curlies
EDIT:
def addSource(q):
lines = q.split("n")
for n,k in enumerate(lines):
if(k.startswith("FROM")):
q[n] = k.replace("FROM ","FROM source.").replace(", ",", source.")
return("n".join(q))
and with that,
for n,k in enumerate(op['result']):
op['result'][n]["query"] = addSource(k["query"])
edited Nov 15 '18 at 6:03
marc_s
579k12911181264
answered Nov 15 '18 at 1:05
kpiekpie
3,61341432
edited Nov 15 '18 at 6:03
marc_s
579k12911181264
edited Nov 15 '18 at 6:03
marc_s
579k12911181264
edited Nov 15 '18 at 6:03
marc_s
579k12911181264
579k12911181264
answered Nov 15 '18 at 1:05
kpiekpie
3,61341432
answered Nov 15 '18 at 1:05
kpiekpie
3,61341432
answered Nov 15 '18 at 1:05
kpiekpie
3,61341432
3,61341432
I need to add the "source." word before the table schemas and then update the dictionary with the updated value.
– Mona
Nov 15 '18 at 1:07
Does the code added under the edit make sense? / work?
– kpie
Nov 15 '18 at 1:25
I am getting this error " for n,k in op['result']: ValueError: not enough values to unpack (expected 2, got 1)"
– Mona
Nov 15 '18 at 1:50
my mistake, that has to be for n,k in enumerate(op['result'])
– kpie
Nov 15 '18 at 1:55
thanks but now getting this error " return("n".join(1)) TypeError: can only join an iterable"
– Mona
Nov 15 '18 at 1:59
|
show 3 more comments
I need to add the "source." word before the table schemas and then update the dictionary with the updated value.
– Mona
Nov 15 '18 at 1:07
Does the code added under the edit make sense? / work?
– kpie
Nov 15 '18 at 1:25
I am getting this error " for n,k in op['result']: ValueError: not enough values to unpack (expected 2, got 1)"
– Mona
Nov 15 '18 at 1:50
my mistake, that has to be for n,k in enumerate(op['result'])
– kpie
Nov 15 '18 at 1:55
thanks but now getting this error " return("n".join(1)) TypeError: can only join an iterable"
– Mona
Nov 15 '18 at 1:59
I need to add the "source." word before the table schemas and then update the dictionary with the updated value.
– Mona
Nov 15 '18 at 1:07
I need to add the "source." word before the table schemas and then update the dictionary with the updated value.
– Mona
Nov 15 '18 at 1:07
Does the code added under the edit make sense? / work?
– kpie
Nov 15 '18 at 1:25
Does the code added under the edit make sense? / work?
– kpie
Nov 15 '18 at 1:25
I am getting this error " for n,k in op['result']: ValueError: not enough values to unpack (expected 2, got 1)"
– Mona
Nov 15 '18 at 1:50
I am getting this error " for n,k in op['result']: ValueError: not enough values to unpack (expected 2, got 1)"
– Mona
Nov 15 '18 at 1:50
my mistake, that has to be for n,k in enumerate(op['result'])
– kpie
Nov 15 '18 at 1:55
my mistake, that has to be for n,k in enumerate(op['result'])
– kpie
Nov 15 '18 at 1:55
thanks but now getting this error " return("n".join(1)) TypeError: can only join an iterable"
– Mona
Nov 15 '18 at 1:59
thanks but now getting this error " return("n".join(1)) TypeError: can only join an iterable"
– Mona
Nov 15 '18 at 1:59
|
show 3 more comments
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53310991%2fusing-python-how-to-get-table-names-from-sql-query-and-add-a-word-before-the-ta%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
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI
JOINsyntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged– marc_s
Nov 15 '18 at 6:04