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

Common xlabel/ylabel for matplotlib subplots

12 answers

NUMPYNUMPY

How to specify multiple return types using type-hints

12 answers

NUMPYNUMPY

Why do I get "Pickle - EOFError: Ran out of input" reading an empty file?

12 answers

NUMPYNUMPY

Flake8: Ignore specific warning for entire file

12 answers

NUMPYNUMPY

glob exclude pattern

12 answers

NUMPYNUMPY

How to avoid HTTP error 429 (Too Many Requests) python

12 answers

NUMPYNUMPY

Python CSV error: line contains NULL byte

12 answers

NUMPYNUMPY

csv.Error: iterator should return strings, not bytes

12 answers

News


Wiki

Python | How to copy data from one Excel sheet to another

Common xlabel/ylabel for matplotlib subplots

Check if one list is a subset of another in Python

sin

How to specify multiple return types using type-hints

exp

Printing words vertically in Python

exp

Python Extract words from a given string

Cyclic redundancy check in Python

Finding mean, median, mode in Python without libraries

cos

Python add suffix / add prefix to strings in a list

Why do I get "Pickle - EOFError: Ran out of input" reading an empty file?

Python - Move item to the end of the list

Python - Print list vertically