使用Python | Set 3的SQL（处理大数据）

建议通过 使用Python的SQL |集1 和 使用Python和SQLite的SQL |集2

在之前的文章中，数据库的记录仅限于小尺寸和单元组。本文将解释如何使用包含所有异常的模块SQLite3从数据库中写入和获取大型数据。 一种简单的方法是执行查询并使用fetchall（）。这已经在第1集中讨论过了。

• executescript（） 这是一种同时执行多个SQL语句的方便方法。它执行作为参数获取的SQL脚本。

  Syntax:sqlite3.connect.executescript(script)

  import sqlite3 # Connection with the DataBase # 'library.db' connection = sqlite3.connect( "library.db" ) cursor = connection.cursor() # SQL piece of code Executed # SQL piece of code Executed cursor.executescript( """ CREATE TABLE people( firstname, lastname, age ); CREATE TABLE book( title, author, published ); INSERT INTO book(title, author, published) VALUES ( 'Dan Clarke''s GFG Detective Agency', 'Sean Simpsons', 1987 ); """ ) sql = """ SELECT COUNT(*) FROM book;""" cursor.execute(sql) # The output in fetched and returned # as a List by fetchall() result = cursor.fetchall() print (result) sql = """ SELECT * FROM book;""" cursor.execute(sql) result = cursor.fetchall() print (result) # Changes saved into database connection.commit() # Connection closed(broken) # with DataBase connection.close()

  输出：

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

  注意：由于具有创建/写入数据库的权限，这段代码可能不适用于在线解释器。

• 执行官 通常情况下，必须从数据文件（对于更简单的事例列表、数组）将大量数据插入数据库。与每次写入数据库中的每一行代码相比，多次迭代代码是很简单的。但在这种情况下，使用loop并不合适，下面的示例说明了原因。下面解释ExecuteMay（）的语法和用法，以及如何像循环一样使用它。

  import sqlite3 # Connection with the DataBase # 'library.db' connection = sqlite3.connect( "library.db" ) cursor = connection.cursor() # SQL piece of code Executed cursor.execute( """ CREATE TABLE book( title, author, published);""" ) List = [( 'A' , 'B' , 2008 ), ( 'C' , 'D' , 2008 ), ( 'E' , 'F' , 2010 )] connection. executemany( """ INSERT INTO book(title, author, published) VALUES (?, ?, ?)""" , List ) sql = """ SELECT * FROM book;""" cursor.execute(sql) result = cursor.fetchall() for x in result: print (x) # Changes saved into database connection.commit() # Connection closed(broken) # with DataBase connection.close()

  输出：

  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
  

  使用executemany（）可以使代码正常工作。

  import sqlite3 # Connection with the DataBase # 'library.db' connection = sqlite3.connect( "library.db" ) cursor = connection.cursor() # SQL piece of code Executed cursor.execute( """ CREATE TABLE book( title, author, published);""" ) List = [( 'A' , 'B' , 2008 ), ( 'C' , 'D' , 2008 ), ( 'E' , 'F' , 2010 )] connection. executemany( """ INSERT INTO book(title, author, published) VALUES (?, ?, ?)""" , List ) sql = """ SELECT * FROM book;""" cursor.execute(sql) result = cursor.fetchall() for x in result: print (x) # Changes saved into database connection.commit() # Connection closed(broken) # with DataBase connection.close()

  输出：

  ('A', 'B', 2008)
  ('C', 'D', 2008)
  ('E', 'F', 2010)
  

• 获取大数据

  import sqlite3 # Connection created with the # database using sqlite3.connect() connection = sqlite3.connect( "company.db" ) cursor = connection.cursor() # Create Table command executed sql = """ CREATE TABLE employee ( ID INTEGER PRIMARY KEY, fname VARCHAR(20), lname VARCHAR(30), gender CHAR(1), dob DATE);""" cursor.execute(sql) # Single Tuple inserted sql = """ INSERT INTO employee VALUES (1007, "Will", "Olsen", "M", "24-SEP-1865");""" cursor.execute(sql) # Multiple Rows 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 Rows fetched from # the Database for row in connection.execute( 'SELECT * FROM employee ORDER BY ID' ): print (row) print ( "Method-2" ) # Method-2 to fetch multiple # rows sql = """ SELECT * FROM employee ORDER BY ID;""" cursor.execute(sql) result = cursor.fetchall() for x in result: print (x) connection.commit() connection.close()

  输出：

  Method-1
  
  (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')
  
  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')
  

  注意：由于具有创建/写入数据库的权限，这段代码可能不适用于在线解释器。