[
Execute Values for Batch Inserts¶
In [2]:
%run 02_function_get_database_connection.ipynb
In [3]:
%load_ext sql
In [4]:
%env DATABASE_URL=postgresql://itversity_sms_user:itversity@pg.itversity.com:5432/itversity_sms_db
env: DATABASE_URL=postgresql://itversity_sms_user:itversity@pg.itversity.com:5432/itversity_sms_db
In [5]:
%%sql
DROP TABLE IF EXISTS courses
Done.
Out[5]:
[]
In [6]:
%%sql
CREATE TABLE courses (
course_id SERIAL PRIMARY KEY,
course_name VARCHAR(60),
course_author VARCHAR(40),
course_status VARCHAR(9) CHECK (course_status IN ('published', 'inactive', 'draft')),
course_published_dt DATE
)
* postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db Done.
Out[6]:
[]
In [7]:
courses = [{'course_name': 'Programming using Python',
'course_author': 'Bob Dillon',
'course_status': 'published',
'course_published_dt': '2020-09-30'},
{'course_name': 'Data Engineering using Python',
'course_author': 'Bob Dillon',
'course_status': 'published',
'course_published_dt': '2020-07-15'},
{'course_name': 'Data Engineering using Scala',
'course_author': 'Elvis Presley',
'course_status': 'draft',
'course_published_dt': None},
{'course_name': 'Programming using Scala',
'course_author': 'Elvis Presley',
'course_status': 'published',
'course_published_dt': '2020-05-12'},
{'course_name': 'Programming using Java',
'course_author': 'Mike Jack',
'course_status': 'inactive',
'course_published_dt': '2020-08-10'},
{'course_name': 'Web Applications - Python Flask',
'course_author': 'Bob Dillon',
'course_status': 'inactive',
'course_published_dt': '2020-07-20'},
{'course_name': 'Web Applications - Java Spring',
'course_author': 'Mike Jack',
'course_status': 'draft',
'course_published_dt': None},
{'course_name': 'Pipeline Orchestration - Python',
'course_author': 'Bob Dillon',
'course_status': 'draft',
'course_published_dt': None},
{'course_name': 'Streaming Pipelines - Python',
'course_author': 'Bob Dillon',
'course_status': 'published',
'course_published_dt': '2020-10-05'},
{'course_name': 'Web Applications - Scala Play',
'course_author': 'Elvis Presley',
'course_status': 'inactive',
'course_published_dt': '2020-09-30'},
{'course_name': 'Web Applications - Python Django',
'course_author': 'Bob Dillon',
'course_status': 'published',
'course_published_dt': '2020-06-23'},
{'course_name': 'Server Automation - Ansible',
'course_author': 'Uncle Sam',
'course_status': 'published',
'course_published_dt': '2020-07-05'}]
In [8]:
import psycopg2
from psycopg2.extras import execute_values
In [9]:
columns = courses[0].keys()
query = "INSERT INTO courses ({}) VALUES %s".format(','.join(columns))
In [10]:
query
Out[10]:
'INSERT INTO courses (course_name,course_author,course_status,course_published_dt) VALUES %s'
In [11]:
values = [[value for value in course.values()] for course in courses]
In [12]:
values
Out[12]:
[['Programming using Python', 'Bob Dillon', 'published', '2020-09-30'], ['Data Engineering using Python', 'Bob Dillon', 'published', '2020-07-15'], ['Data Engineering using Scala', 'Elvis Presley', 'draft', None], ['Programming using Scala', 'Elvis Presley', 'published', '2020-05-12'], ['Programming using Java', 'Mike Jack', 'inactive', '2020-08-10'], ['Web Applications - Python Flask', 'Bob Dillon', 'inactive', '2020-07-20'], ['Web Applications - Java Spring', 'Mike Jack', 'draft', None], ['Pipeline Orchestration - Python', 'Bob Dillon', 'draft', None], ['Streaming Pipelines - Python', 'Bob Dillon', 'published', '2020-10-05'], ['Web Applications - Scala Play', 'Elvis Presley', 'inactive', '2020-09-30'], ['Web Applications - Python Django', 'Bob Dillon', 'published', '2020-06-23'], ['Server Automation - Ansible', 'Uncle Sam', 'published', '2020-07-05']]
In [13]:
execute_values(sms_connection.cursor(), query, values)
In [14]:
sms_connection.commit()
In [15]:
%%sql
SELECT * FROM courses
* postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db 12 rows affected.
Out[15]:
| course_id | course_name | course_author | course_status | course_published_dt |
|---|---|---|---|---|
| 1 | Programming using Python | Bob Dillon | published | 2020-09-30 |
| 2 | Data Engineering using Python | Bob Dillon | published | 2020-07-15 |
| 3 | Data Engineering using Scala | Elvis Presley | draft | None |
| 4 | Programming using Scala | Elvis Presley | published | 2020-05-12 |
| 5 | Programming using Java | Mike Jack | inactive | 2020-08-10 |
| 6 | Web Applications – Python Flask | Bob Dillon | inactive | 2020-07-20 |
| 7 | Web Applications – Java Spring | Mike Jack | draft | None |
| 8 | Pipeline Orchestration – Python | Bob Dillon | draft | None |
| 9 | Streaming Pipelines – Python | Bob Dillon | published | 2020-10-05 |
| 10 | Web Applications – Scala Play | Elvis Presley | inactive | 2020-09-30 |
| 11 | Web Applications – Python Django | Bob Dillon | published | 2020-06-23 |
| 12 | Server Automation – Ansible | Uncle Sam | published | 2020-07-05 |
]