Inserting and Deleting Data

This example shows best practices for inserting and deleting data.

Setup

Create a connection to a Vertica database, and open a cursor:

>>> import vertica_db_client
>>> db = vertica_db_client.connect("host=myhost.example.com database=mydb port=5433 user=dbadmin")
>>> cur = db.cursor()

Insert Data

Create a table, and insert data into the table:

>>> cur.execute("CREATE TABLE foo (numbs int, names varchar(30))")
>>> data = [(313, 'foo slang'), (434, 'loaf table'), (565, 'rogue winger')]
>>> cur.executemany("INSERT INTO foo VALUES(?, ?)", data)

Commit the changes, and query the table to see the newly inserted data:

>>> cur.execute("COMMIT")
>>> cur.execute("SELECT * FROM foo ORDER BY numbs ASC")
>>> rows = cur.fetchall()
>>> for i, row in enumerate(rows):
...     print row
...
(313, 'foo slang')
(434, 'loaf table')
(565, 'rogue winger')

Delete Data

Delete data from the table:

>>> cur.execute("DELETE FROM foo WHERE numbs = 434")

Fetch the results from the cursor to get the number of deleted rows:

>>> row = cur.fetchone()
>>> print row
(1,)