Unable to insert all rows into SQL table using python script





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}







0















I have two data sets in my JSON API. I am unable to insert both into SQL Server. The Iteration using for loop doesnt seem to pick up the second data. Can someone please help me understand how to fix this. this is new for me, so am not able to find out whats wrong since the coding is bit different from SQL



import urllib, json
import pyodbc


#read data from API
url = "http://nagiosdatagateway.vestas.net/esq/ITE1452552/logstash- 2018.12.16/2/desc"
response = urllib.urlopen(url)
data = json.loads(response.read())

#define db connection
cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
"Server=DKCDCVDCP42DPA;"
"Database=VPDC;"
"Trusted_Connection=yes;")
cursor = cnxn.cursor()
i = 0
j = len(data)
print j
for i in range(i,j-1):
# print data[1]["_source"]["utc_timestamp"]
print i
print data[i]["_source"]["nagios_comment"]
print data[i]["_source"]["nagios_author"]
cursor.execute("insert into vpdc.pa.Pythontable(nagios_comment,nagios_author) values (?,?)",(data[i]
["_source"]["nagios_comment"],data[i]["_source"]["nagios_author"] ))
i += 1
print i
cnxn.commit()


both these two sets of values should be in the SQL table for columns



Nagios_comment & Nagios_author

307262828 Alex Christopher Ramos

307160348 Alex Christopher Ramos










share|improve this question




















  • 2





    Please fix code indentation.

    – Jérôme
    Jan 3 at 13:41






  • 1





    i += 1 is not needed in the for loop. That's because range(i, j-1) returns an iterable object and for automatically iterates over it.

    – MaJoR
    Jan 3 at 13:49






  • 1





    Also, the i += 1 does not change the range generator.

    – fcracker79
    Jan 3 at 13:51











  • Welcome to SO. In Python, indentation is significative (it's part of the languages syntax), so no one can help with your snippet's broken indentation. Please edit your post to fix the indentation so it exactly matches your own code.

    – bruno desthuilliers
    Jan 3 at 13:52











  • Yes, Thanks @brunodesthuilliers. when you mentioned about indentation, i tried again by indenting the cursor.execute statement. It was outside the loop before. I corrected it now and working perfectly fine.

    – Maria
    Jan 4 at 4:12


















0















I have two data sets in my JSON API. I am unable to insert both into SQL Server. The Iteration using for loop doesnt seem to pick up the second data. Can someone please help me understand how to fix this. this is new for me, so am not able to find out whats wrong since the coding is bit different from SQL



import urllib, json
import pyodbc


#read data from API
url = "http://nagiosdatagateway.vestas.net/esq/ITE1452552/logstash- 2018.12.16/2/desc"
response = urllib.urlopen(url)
data = json.loads(response.read())

#define db connection
cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
"Server=DKCDCVDCP42DPA;"
"Database=VPDC;"
"Trusted_Connection=yes;")
cursor = cnxn.cursor()
i = 0
j = len(data)
print j
for i in range(i,j-1):
# print data[1]["_source"]["utc_timestamp"]
print i
print data[i]["_source"]["nagios_comment"]
print data[i]["_source"]["nagios_author"]
cursor.execute("insert into vpdc.pa.Pythontable(nagios_comment,nagios_author) values (?,?)",(data[i]
["_source"]["nagios_comment"],data[i]["_source"]["nagios_author"] ))
i += 1
print i
cnxn.commit()


both these two sets of values should be in the SQL table for columns



Nagios_comment & Nagios_author

307262828 Alex Christopher Ramos

307160348 Alex Christopher Ramos










share|improve this question




















  • 2





    Please fix code indentation.

    – Jérôme
    Jan 3 at 13:41






  • 1





    i += 1 is not needed in the for loop. That's because range(i, j-1) returns an iterable object and for automatically iterates over it.

    – MaJoR
    Jan 3 at 13:49






  • 1





    Also, the i += 1 does not change the range generator.

    – fcracker79
    Jan 3 at 13:51











  • Welcome to SO. In Python, indentation is significative (it's part of the languages syntax), so no one can help with your snippet's broken indentation. Please edit your post to fix the indentation so it exactly matches your own code.

    – bruno desthuilliers
    Jan 3 at 13:52











  • Yes, Thanks @brunodesthuilliers. when you mentioned about indentation, i tried again by indenting the cursor.execute statement. It was outside the loop before. I corrected it now and working perfectly fine.

    – Maria
    Jan 4 at 4:12














0












0








0








I have two data sets in my JSON API. I am unable to insert both into SQL Server. The Iteration using for loop doesnt seem to pick up the second data. Can someone please help me understand how to fix this. this is new for me, so am not able to find out whats wrong since the coding is bit different from SQL



import urllib, json
import pyodbc


#read data from API
url = "http://nagiosdatagateway.vestas.net/esq/ITE1452552/logstash- 2018.12.16/2/desc"
response = urllib.urlopen(url)
data = json.loads(response.read())

#define db connection
cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
"Server=DKCDCVDCP42DPA;"
"Database=VPDC;"
"Trusted_Connection=yes;")
cursor = cnxn.cursor()
i = 0
j = len(data)
print j
for i in range(i,j-1):
# print data[1]["_source"]["utc_timestamp"]
print i
print data[i]["_source"]["nagios_comment"]
print data[i]["_source"]["nagios_author"]
cursor.execute("insert into vpdc.pa.Pythontable(nagios_comment,nagios_author) values (?,?)",(data[i]
["_source"]["nagios_comment"],data[i]["_source"]["nagios_author"] ))
i += 1
print i
cnxn.commit()


both these two sets of values should be in the SQL table for columns



Nagios_comment & Nagios_author

307262828 Alex Christopher Ramos

307160348 Alex Christopher Ramos










share|improve this question
















I have two data sets in my JSON API. I am unable to insert both into SQL Server. The Iteration using for loop doesnt seem to pick up the second data. Can someone please help me understand how to fix this. this is new for me, so am not able to find out whats wrong since the coding is bit different from SQL



import urllib, json
import pyodbc


#read data from API
url = "http://nagiosdatagateway.vestas.net/esq/ITE1452552/logstash- 2018.12.16/2/desc"
response = urllib.urlopen(url)
data = json.loads(response.read())

#define db connection
cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
"Server=DKCDCVDCP42DPA;"
"Database=VPDC;"
"Trusted_Connection=yes;")
cursor = cnxn.cursor()
i = 0
j = len(data)
print j
for i in range(i,j-1):
# print data[1]["_source"]["utc_timestamp"]
print i
print data[i]["_source"]["nagios_comment"]
print data[i]["_source"]["nagios_author"]
cursor.execute("insert into vpdc.pa.Pythontable(nagios_comment,nagios_author) values (?,?)",(data[i]
["_source"]["nagios_comment"],data[i]["_source"]["nagios_author"] ))
i += 1
print i
cnxn.commit()


both these two sets of values should be in the SQL table for columns



Nagios_comment & Nagios_author

307262828 Alex Christopher Ramos

307160348 Alex Christopher Ramos







python sql json






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jan 3 at 13:59









Yossarian

3,66512746




3,66512746










asked Jan 3 at 13:37









MariaMaria

14




14








  • 2





    Please fix code indentation.

    – Jérôme
    Jan 3 at 13:41






  • 1





    i += 1 is not needed in the for loop. That's because range(i, j-1) returns an iterable object and for automatically iterates over it.

    – MaJoR
    Jan 3 at 13:49






  • 1





    Also, the i += 1 does not change the range generator.

    – fcracker79
    Jan 3 at 13:51











  • Welcome to SO. In Python, indentation is significative (it's part of the languages syntax), so no one can help with your snippet's broken indentation. Please edit your post to fix the indentation so it exactly matches your own code.

    – bruno desthuilliers
    Jan 3 at 13:52











  • Yes, Thanks @brunodesthuilliers. when you mentioned about indentation, i tried again by indenting the cursor.execute statement. It was outside the loop before. I corrected it now and working perfectly fine.

    – Maria
    Jan 4 at 4:12














  • 2





    Please fix code indentation.

    – Jérôme
    Jan 3 at 13:41






  • 1





    i += 1 is not needed in the for loop. That's because range(i, j-1) returns an iterable object and for automatically iterates over it.

    – MaJoR
    Jan 3 at 13:49






  • 1





    Also, the i += 1 does not change the range generator.

    – fcracker79
    Jan 3 at 13:51











  • Welcome to SO. In Python, indentation is significative (it's part of the languages syntax), so no one can help with your snippet's broken indentation. Please edit your post to fix the indentation so it exactly matches your own code.

    – bruno desthuilliers
    Jan 3 at 13:52











  • Yes, Thanks @brunodesthuilliers. when you mentioned about indentation, i tried again by indenting the cursor.execute statement. It was outside the loop before. I corrected it now and working perfectly fine.

    – Maria
    Jan 4 at 4:12








2




2





Please fix code indentation.

– Jérôme
Jan 3 at 13:41





Please fix code indentation.

– Jérôme
Jan 3 at 13:41




1




1





i += 1 is not needed in the for loop. That's because range(i, j-1) returns an iterable object and for automatically iterates over it.

– MaJoR
Jan 3 at 13:49





i += 1 is not needed in the for loop. That's because range(i, j-1) returns an iterable object and for automatically iterates over it.

– MaJoR
Jan 3 at 13:49




1




1





Also, the i += 1 does not change the range generator.

– fcracker79
Jan 3 at 13:51





Also, the i += 1 does not change the range generator.

– fcracker79
Jan 3 at 13:51













Welcome to SO. In Python, indentation is significative (it's part of the languages syntax), so no one can help with your snippet's broken indentation. Please edit your post to fix the indentation so it exactly matches your own code.

– bruno desthuilliers
Jan 3 at 13:52





Welcome to SO. In Python, indentation is significative (it's part of the languages syntax), so no one can help with your snippet's broken indentation. Please edit your post to fix the indentation so it exactly matches your own code.

– bruno desthuilliers
Jan 3 at 13:52













Yes, Thanks @brunodesthuilliers. when you mentioned about indentation, i tried again by indenting the cursor.execute statement. It was outside the loop before. I corrected it now and working perfectly fine.

– Maria
Jan 4 at 4:12





Yes, Thanks @brunodesthuilliers. when you mentioned about indentation, i tried again by indenting the cursor.execute statement. It was outside the loop before. I corrected it now and working perfectly fine.

– Maria
Jan 4 at 4:12












1 Answer
1






active

oldest

votes


















0














the issue had been resolved by correctly indenting the cursor.execute statement in the script as below. In my original script, there was no indentation done for this line.so it was called outside the loop



import urllib, json
import pyodbc


#read data from API
url = "http://nagiosdatagateway.vestas.net/esq/ITE1452552/logstash-2018.12.16/2/desc"
response = urllib.urlopen(url)
data = json.loads(response.read())

#define db connection
cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
"Server=DKCDCVDCP42DPA;"
"Database=VPDC;"
"Trusted_Connection=yes;")
cursor = cnxn.cursor()
i = 0
j = len(data)
print j
for i in range(0,2):
#print data[1]["_source"]["utc_timestamp"]
print data[i]["_source"]["nagios_comment"]
print data[i]["_source"]["nagios_author"]
cursor.execute("insert into vpdc.pa.Pythontable(nagios_comment,nagios_author)
values (?,?)",(data[i]["_source"]["nagios_comment"],data[i]["_source"]
["nagios_author"] ))
cnxn.commit()





share|improve this answer
























    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


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54023400%2funable-to-insert-all-rows-into-sql-table-using-python-script%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














    the issue had been resolved by correctly indenting the cursor.execute statement in the script as below. In my original script, there was no indentation done for this line.so it was called outside the loop



    import urllib, json
    import pyodbc


    #read data from API
    url = "http://nagiosdatagateway.vestas.net/esq/ITE1452552/logstash-2018.12.16/2/desc"
    response = urllib.urlopen(url)
    data = json.loads(response.read())

    #define db connection
    cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
    "Server=DKCDCVDCP42DPA;"
    "Database=VPDC;"
    "Trusted_Connection=yes;")
    cursor = cnxn.cursor()
    i = 0
    j = len(data)
    print j
    for i in range(0,2):
    #print data[1]["_source"]["utc_timestamp"]
    print data[i]["_source"]["nagios_comment"]
    print data[i]["_source"]["nagios_author"]
    cursor.execute("insert into vpdc.pa.Pythontable(nagios_comment,nagios_author)
    values (?,?)",(data[i]["_source"]["nagios_comment"],data[i]["_source"]
    ["nagios_author"] ))
    cnxn.commit()





    share|improve this answer




























      0














      the issue had been resolved by correctly indenting the cursor.execute statement in the script as below. In my original script, there was no indentation done for this line.so it was called outside the loop



      import urllib, json
      import pyodbc


      #read data from API
      url = "http://nagiosdatagateway.vestas.net/esq/ITE1452552/logstash-2018.12.16/2/desc"
      response = urllib.urlopen(url)
      data = json.loads(response.read())

      #define db connection
      cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
      "Server=DKCDCVDCP42DPA;"
      "Database=VPDC;"
      "Trusted_Connection=yes;")
      cursor = cnxn.cursor()
      i = 0
      j = len(data)
      print j
      for i in range(0,2):
      #print data[1]["_source"]["utc_timestamp"]
      print data[i]["_source"]["nagios_comment"]
      print data[i]["_source"]["nagios_author"]
      cursor.execute("insert into vpdc.pa.Pythontable(nagios_comment,nagios_author)
      values (?,?)",(data[i]["_source"]["nagios_comment"],data[i]["_source"]
      ["nagios_author"] ))
      cnxn.commit()





      share|improve this answer


























        0












        0








        0







        the issue had been resolved by correctly indenting the cursor.execute statement in the script as below. In my original script, there was no indentation done for this line.so it was called outside the loop



        import urllib, json
        import pyodbc


        #read data from API
        url = "http://nagiosdatagateway.vestas.net/esq/ITE1452552/logstash-2018.12.16/2/desc"
        response = urllib.urlopen(url)
        data = json.loads(response.read())

        #define db connection
        cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
        "Server=DKCDCVDCP42DPA;"
        "Database=VPDC;"
        "Trusted_Connection=yes;")
        cursor = cnxn.cursor()
        i = 0
        j = len(data)
        print j
        for i in range(0,2):
        #print data[1]["_source"]["utc_timestamp"]
        print data[i]["_source"]["nagios_comment"]
        print data[i]["_source"]["nagios_author"]
        cursor.execute("insert into vpdc.pa.Pythontable(nagios_comment,nagios_author)
        values (?,?)",(data[i]["_source"]["nagios_comment"],data[i]["_source"]
        ["nagios_author"] ))
        cnxn.commit()





        share|improve this answer













        the issue had been resolved by correctly indenting the cursor.execute statement in the script as below. In my original script, there was no indentation done for this line.so it was called outside the loop



        import urllib, json
        import pyodbc


        #read data from API
        url = "http://nagiosdatagateway.vestas.net/esq/ITE1452552/logstash-2018.12.16/2/desc"
        response = urllib.urlopen(url)
        data = json.loads(response.read())

        #define db connection
        cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
        "Server=DKCDCVDCP42DPA;"
        "Database=VPDC;"
        "Trusted_Connection=yes;")
        cursor = cnxn.cursor()
        i = 0
        j = len(data)
        print j
        for i in range(0,2):
        #print data[1]["_source"]["utc_timestamp"]
        print data[i]["_source"]["nagios_comment"]
        print data[i]["_source"]["nagios_author"]
        cursor.execute("insert into vpdc.pa.Pythontable(nagios_comment,nagios_author)
        values (?,?)",(data[i]["_source"]["nagios_comment"],data[i]["_source"]
        ["nagios_author"] ))
        cnxn.commit()






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Jan 4 at 4:38









        MariaMaria

        14




        14
































            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














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54023400%2funable-to-insert-all-rows-into-sql-table-using-python-script%23new-answer', 'question_page');
            }
            );

            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







            Popular posts from this blog

            android studio warns about leanback feature tag usage required on manifest while using Unity exported app?

            SQL update select statement

            'app-layout' is not a known element: how to share Component with different Modules