How do I execute multiple SQL statements at once - Python

Question

How do I execute multiple SQL statements at once using python?

Comments

Hi, it is looks grate,

here you are defined sql statement directly in python file, but in case if we want to execute multiple .sql scripts(SCR(structure changes request and DCR(data change request ) and pl/sql scripts) files in sequence order how can we do. (for automating DB deployment process). if you help on this really appreciate.

---

Use a loop and change script each time or maybe you could combine it into one script even

Hope it helps!!

Answer 1

You can use sqlite3 module. Have a look at this example:

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() 

Hope this helps!

If you need to know more about Python, join Python online course certification today.

Thanks!