Change language

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.

Shop

Learn programming in R: courses

$

Best Python online courses for 2022

$

Best laptop for Fortnite

$

Best laptop for Excel

$

Best laptop for Solidworks

$

Best laptop for Roblox

$

Best computer for crypto mining

$

Best laptop for Sims 4

$

Latest questions

NUMPYNUMPY

psycopg2: insert multiple rows with one query

12 answers

NUMPYNUMPY

How to convert Nonetype to int or string?

12 answers

NUMPYNUMPY

How to specify multiple return types using type-hints

12 answers

NUMPYNUMPY

Javascript Error: IPython is not defined in JupyterLab

12 answers

News


Wiki

Python OpenCV | cv2.putText () method

numpy.arctan2 () in Python

Python | os.path.realpath () method

Python OpenCV | cv2.circle () method

Python OpenCV cv2.cvtColor () method

Python - Move item to the end of the list

time.perf_counter () function in Python

Check if one list is a subset of another in Python

Python os.path.join () method