SQL Using Python | Set 3 (Big Data Processing)



In previous articles, database records were limited to a small size and one tuple. This article explains how to write and retrieve big data from a database using the SQLite3 module covering all exceptions.
The easy way — execute the request and use fetchall (). This was discussed in SET 1.

  • executescript ()
    This is a convenient method for executing multiple SQL statements at the same time. It executes the SQL script that it receives as a parameter.
    Syntax :
    sqlite3.connect.executescript(script)

    import sqlite3

     
    # Database connection
    # & # 39; library.db & # 39;

    connection = sqlite3.connect ( "library.db" )

    cursor = connection. cursor ()

     
    # SQL part of the code executed
    # SQL part of the code executed

    cursor.executescript ( " ""

    CREATE TABLE of people (

    First name,

    Last name,

    age

    );

     

    CREATE TABLE book (

      title,

    author,

    published

    );

      

      INSERT INTO

    book (title, author, published)

    VALUES (

    Dana Clarke Detective Agency GFG,

      " Sean Simpsons ",

      1987

    );

    "" " )

      

    sql = "" "

    SELECT ACCOUNT (*) FROM book; & quot; & quot; & quot;

     
    cursor.execute (sql)

     
    # Selected and returned
    # as a list from fetchall ()

    result = cursor.fetchall ()

    print (result)

     

    sql = "" "

    SELECT * FROM book; & quot; & quot; & quot;

     
    cursor.execute (sql)

     

    result = cursor.fetchall ()

    print (result)

     
    # Changes saved in the database
    connection.commit ()

     
    # Connection closed (severed)
    # with database
    connection.close ()

    Output:

     [(1,)] [( "Dan Clarke`s GFG Detective Agency", `Sean Simpsons`, 1987)] 

    Note. This piece of code may not work with online translators due to database create / write access rights.

  • executemany ()
    This is often the case that a large amount of data must be inserted into the database from data files (for a simpler case, take lists, arrays). It would be easier to repeat the code over and over again than to write each row to the database every time. But using a loop is not appropriate in this case, the example below shows why. The syntax and usage of executemany () is explained below and how it can be used as a loop.

    import sqlite3

     
    # Database connection
    # & # 39; library.db & # 39;

    connection = sqlite3.connect ( "library.db" )

    cursor = connection.cursor ()

     
    # SQL part of the code executed

    cursor.execute ( " ""

     

      CREATE TABLE book (

    title,

    author,

    published); & quot; & quot; " )

      

    List = [( `A` , `B` , 2008 ), ( ` C` , `D` , 2008 ),

    ( `E` , `F` , 2010 )]

      

    connection. executemany ( "" "

      

    INSERT INTO

    book (title, author, published)

      VALUES (?,?,? ) & quot; & quot; & quot; , List )

     

    sql = "" "

      SELECT * FROM book ; & quot; & quot; & quot;

    cursor.execute (sql)

    result = cursor.fetchall ()

    for x in result:

    print (x)

     
    # Changes saved in the database
    connection.commit ()

     
    # Connection closed (disconnected)
    # with database
    connection.close ()

    Output:

     Traceback (most recent call last): File "C: /Users/GFG/Desktop/SQLITE3.py ", line 16, in List [2] [3] = [[` A`, `B `, 2008], [` C`, `D`, 2008], [` E`, `F`, 2010]] NameError: name` List` is not defined 

    Using executemany () can make some of the code functional.

    Output:

     (`A`,` B`, 2008) (`C`,` D`, 2008) (`E `,` F`, 2010) 
  • Get big data

  • import sqlite3

     
    # Database connection
    # & # 39; library .db & # 39;

    connection = sqlite3.connect ( "library.db" )

    cursor = connection.cursor ()

      
    # SQL part of the code executed

    cursor.execute ( " ""

    CREATE TABLE book (

    title,

    author,

    published); & quot; & quot; " )

      

    List = [( `A` , `B` , 2008 ), ( ` C` , `D` , 2008 ), 

    ( `E` , `F` , 2010 )]

      

    connection. executemany ( "" "

      INSERT INTO

    book (title, author, published)

    VALUES (?,?,?) & quot; & quot; & quot; , List )

     

    sql = "" "

    SELECT * FROM book; & quot; & quot; & quot;
    cursor.execute (sql)

    result = cursor.fetchall ()

    for x in result:

      print (x)

      
    # Changes saved in the database
    connection.commit ()

     
    # Connection closed (disconnected)
    # with database
    connection.close ()

    import sqlite3

      
    # Connection created with
    # database using sqlite3.connect ()

    connection = sqlite3.connect ( "company.db" )

    cursor = connection.cursor ()

     
    # Create Table completed

    sql = "" "

      CREATE TABLE employee (

    ID INTEGER PRIMARY KEY,

    name VARCHAR (20),

    NAME VARCHAR (30) ,

    gender CHAR (1),

    dob DATE); & quot; & quot; & quot;

    cursor.execute (sql)

      
    # Single tuple inserted

    sql = "" "

      INSERT INTO employee

    VALUES (1007, "Will", "Olsen", "M" , & quot; 24-SEPTEMBER-1865 & quot;); & quot; & quot; & quot;

    cursor.execute (sql)

      
    # Multiple lines inserted

    List = [( 1008 , ` Rkb` , `Boss` , `M` , " 27-NOV-1864 " ),

    ( 1098 , ` Sak` , `Rose` , ` F` , "27-DEC-1864" ),

    ( 1908 , `Royal` , ` Bassen` , "F" , "17-NOV-1894" )]

     
    connection ... executemany (

    "INSERT INTO employee VALUES (?,?,?,?,?) " , List )

     

    print ( "Method-1" )

     
    # Multiple lines extracted from
    # Database

    for row in connection.execute ( `SELECT * FROM employee ORDER BY ID` ):

      print (row)

     

    print ( "Method-2" )

     
    # Method 2 to get multiple
    # lines

    sql = "" "

      CHOOSE * FROM EMPLOYEE ORDER BY ID;

     
    cursor.execute (sql)

    result = cursor.fetchall ()

     

    for x in result:

    print (x)

      
    connection.commit ()
    connection.close ()

    Output:

     Method-1 (100 7, `Will`,` Olsen`, `M`,` 24-SEP-1865`) (1008, `Rkb`,` Boss`, `M`,` 27-NOV-1864`) (1098, `Sak `,` Rose`, `F`,` 27-DEC-1864`) (1908, `Royal`,` Bassen`, `F`,` 17-NOV-1894`) Method-2 (1007, `Will` , `Olsen`,` M`, `24-SEP-1865`) (1008,` Rkb`, `Boss`,` M`, `27-NOV-1864`) (1098,` Sak`, `Rose` , `F`,` 27-DEC-1864`) (1908, `Royal`,` Bassen`, `F`,` 17-NOV-1894`) 

    Note. This code snippet may not work with online translators due to database create / write permissions.