Hi everybody!
I'd like to announce the first release (1.0.0) of Lispvirt: Common Lisp bindings for Libvirt.
You can download the source code from GitHub at:
https://github.com/jcfaracco/lispvirt
There you can find any instrunctions to install, test and use Lispvirt. There are some examples and tests to teach how to use the API. As this release is the first one, there is nothing to announce as a new feature.
This version is based on Libvirt (>= 1.2). So, if you are using an old version of libvirt, I cannot guarantee that Lispvirt will work.
Please, clone the code and enjoy!
If you want to contribute, please, send me a message, email, commits or anything you want.
We still need many tests to check if the API is correctly implemented, examples and some missing structures.
Any contribution is welcome and I would be glad if people help me. =D
Tuesday, March 17, 2015
Monday, December 17, 2012
SQLite3: An Overview with Code Snippets in C and Python.
SQLite Introduction
SQLite is a light Database library written in C. You can use this library for database operations instead of using a DBMS process running. If your application needs a database but the operations of it are so simple and the access flow is not so high, it's recommended that you use this database approach. The current version of SQLite is 3.x.
In this post we will introduce some basics functions of the library and they will be showed in two programming language: C and Python. The intention is show you how easily the SQLite is.
The database schema used is the example below.
This is a Company schema and it has your employees, the dependets of the employees and some management of Company's projects.
We going to create the Database tables and do some basic operations. First, we create the tables, your primary keys and foreing keys. You can create a function to do it.
Here we create a function called
C:
In Python, the constructor __init__ of the class create the tables
Obviously, don't forget to include the libraries
C:
You have to pass the sqlite3 handle as an argument to execute the statements. Each SQL statemens was defined in string constant variables. After the statements will be executed. The body of the function can be viewed below.
C:
Python:
Before populate the tables, let's going to see the main() fucntion. There, we can verify how the database was initiated. The code is so simple.
C:
Python:
C:
Python:
The sqlite3_prepare_v2() function prepare the output of the SQL execution to be accessed with a pointer. On the other hand, the output data can accessed in sequence with the sqlite3_step() function. The columns can be accessed with the sqlite3_column_text() function, passing the statement pointer and the index of the column.
The other example function shows a INNER JOIN statement.
C:
Python:
The process is exactly the same, but observe the difference between strings.
Finally, the sample has a function who creates a trigger. The trigger records changes in project's names in a table called "log"and, when it happens, the time and date is recorded too.
C:
Python:
As you can see, this function create the table 'log', create the trigger, update a specific project's name and execute a SELECT statement in the table 'log'. The output of SELECT statement is printed in the output stream.
Conclusion
If you don't want to use a DBMS, SQLite is a good alternative for small databases. Remember that SQLite is only a library who manipulates a file. So, a large amount of data can cause latency when you recover a data.
In Python, we have some differences from C and they will show in a next post.
Observation: The database figure will be changed for a better figure.
In this post we will introduce some basics functions of the library and they will be showed in two programming language: C and Python. The intention is show you how easily the SQLite is.
The database schema used is the example below.
This is a Company schema and it has your employees, the dependets of the employees and some management of Company's projects.
We going to create the Database tables and do some basic operations. First, we create the tables, your primary keys and foreing keys. You can create a function to do it.
Here we create a function called
C:
int create_database_tables(sqlite3 *handle)
In Python, the constructor __init__ of the class create the tables
Obviously, don't forget to include the libraries
C:
#include <sqlite3.h>
Python:
import sqlite3
You have to pass the sqlite3 handle as an argument to execute the statements. Each SQL statemens was defined in string constant variables. After the statements will be executed. The body of the function can be viewed below.
C:
int create_database_tables(sqlite3 *handle) {
/** ERROR message. **/
char *errmsg;
/** String who create the table 'employee'. **/
const char * employee = "CREATE TABLE IF NOT EXISTS employee \
(cpf_emp TEXT PRIMARY KEY, \
name TEXT NOT NULL, code TEXT, \
dpt_acro TEXT, begin_date TEXT, \
FOREIGN KEY(code) REFERENCES employee(cpf_emp), \
FOREIGN KEY(dpt_acro) \
REFERENCES departament(acro_dept))";
/** String who create the table 'relative'. **/
const char * relative = "CREATE TABLE IF NOT EXISTS relative \
(cpf_emp TEXT, name TEXT, \
gender TEXT, PRIMARY KEY (cpf_emp, name), \
FOREIGN KEY(cpf_emp) \
REFERENCES employee(cpf_emp))";
/** String who create the table 'department'. **/
const char * department = "CREATE TABLE IF NOT EXISTS department \
(acro_dpt TEXT PRIMARY KEY, \
name_dpt TEXT NOT NULL, \
cpf_emp TEXT NOT NULL, \
FOREIGN KEY(cpf_emp) \
REFERENCES employee(cpf_emp))";
/** String who create the table 'project'. **/
const char * project = "CREATE TABLE IF NOT EXISTS project \
(number_project INTEGER PRIMARY KEY, \
name_project TEXT NOT NULL)";
/** String who create the table 'control'. **/
const char * control = "CREATE TABLE IF NOT EXISTS control \
(acro_dpt TEXT, number_project TEXT, \
PRIMARY KEY(acro_dpt, number_project), \
FOREIGN KEY(acro_dpt) \
REFERENCES department(acro_dept), \
FOREIGN KEY(number_project) \
REFERENCES project(number_project))";
/** String who create the table 'develop'. **/
const char * develop = "CREATE TABLE IF NOT EXISTS develop \
(cpf_emp TEXT, number_project TEXT, \
time_worked FLOAT, \
PRIMARY KEY(cpf_emp, number_project), \
FOREIGN KEY(cpf_emp) \
REFERENCES employee(cpf_emp), \
FOREIGN KEY(number_project) \
REFERENCES project(number_project))";
/** Execute all the SQL create tables statements. **/
if(sqlite3_exec(handle,employee,0,0,&errmsg) != SQLITE_OK) {
printf("ERROR: %s\n", errmsg);
return -1;
}
if(sqlite3_exec(handle,relative,0,0,&errmsg) != SQLITE_OK) {
printf("ERROR: %s\n", errmsg);
return -1;
}
if(sqlite3_exec(handle,department,0,0,&errmsg) != SQLITE_OK) {
printf("ERROR: %s\n", errmsg);
return -1;
}
if(sqlite3_exec(handle,project,0,0,&errmsg) != SQLITE_OK) {
printf("ERROR: %s\n", errmsg);
return -1;
}
if(sqlite3_exec(handle,control,0,0,&errmsg) != SQLITE_OK) {
printf("ERROR: %s\n", errmsg);
return -1;
}
if(sqlite3_exec(handle,develop,0,0,&errmsg) != SQLITE_OK) {
printf("ERROR: %s\n", errmsg);
return -1;
}
return 1;
}
/** ERROR message. **/
char *errmsg;
/** String who create the table 'employee'. **/
const char * employee = "CREATE TABLE IF NOT EXISTS employee \
(cpf_emp TEXT PRIMARY KEY, \
name TEXT NOT NULL, code TEXT, \
dpt_acro TEXT, begin_date TEXT, \
FOREIGN KEY(code) REFERENCES employee(cpf_emp), \
FOREIGN KEY(dpt_acro) \
REFERENCES departament(acro_dept))";
/** String who create the table 'relative'. **/
const char * relative = "CREATE TABLE IF NOT EXISTS relative \
(cpf_emp TEXT, name TEXT, \
gender TEXT, PRIMARY KEY (cpf_emp, name), \
FOREIGN KEY(cpf_emp) \
REFERENCES employee(cpf_emp))";
/** String who create the table 'department'. **/
const char * department = "CREATE TABLE IF NOT EXISTS department \
(acro_dpt TEXT PRIMARY KEY, \
name_dpt TEXT NOT NULL, \
cpf_emp TEXT NOT NULL, \
FOREIGN KEY(cpf_emp) \
REFERENCES employee(cpf_emp))";
/** String who create the table 'project'. **/
const char * project = "CREATE TABLE IF NOT EXISTS project \
(number_project INTEGER PRIMARY KEY, \
name_project TEXT NOT NULL)";
/** String who create the table 'control'. **/
const char * control = "CREATE TABLE IF NOT EXISTS control \
(acro_dpt TEXT, number_project TEXT, \
PRIMARY KEY(acro_dpt, number_project), \
FOREIGN KEY(acro_dpt) \
REFERENCES department(acro_dept), \
FOREIGN KEY(number_project) \
REFERENCES project(number_project))";
/** String who create the table 'develop'. **/
const char * develop = "CREATE TABLE IF NOT EXISTS develop \
(cpf_emp TEXT, number_project TEXT, \
time_worked FLOAT, \
PRIMARY KEY(cpf_emp, number_project), \
FOREIGN KEY(cpf_emp) \
REFERENCES employee(cpf_emp), \
FOREIGN KEY(number_project) \
REFERENCES project(number_project))";
/** Execute all the SQL create tables statements. **/
if(sqlite3_exec(handle,employee,0,0,&errmsg) != SQLITE_OK) {
printf("ERROR: %s\n", errmsg);
return -1;
}
if(sqlite3_exec(handle,relative,0,0,&errmsg) != SQLITE_OK) {
printf("ERROR: %s\n", errmsg);
return -1;
}
if(sqlite3_exec(handle,department,0,0,&errmsg) != SQLITE_OK) {
printf("ERROR: %s\n", errmsg);
return -1;
}
if(sqlite3_exec(handle,project,0,0,&errmsg) != SQLITE_OK) {
printf("ERROR: %s\n", errmsg);
return -1;
}
if(sqlite3_exec(handle,control,0,0,&errmsg) != SQLITE_OK) {
printf("ERROR: %s\n", errmsg);
return -1;
}
if(sqlite3_exec(handle,develop,0,0,&errmsg) != SQLITE_OK) {
printf("ERROR: %s\n", errmsg);
return -1;
}
return 1;
}
Python:
class Database:
def __init__(self, cur):
# String who create the table 'employee'.
employee = 'CREATE TABLE IF NOT EXISTS employee \
(cpf_emp TEXT PRIMARY KEY, \
name TEXT NOT NULL, code TEXT, \
dpt_acro TEXT, begin_date TEXT, \
FOREIGN KEY(code) REFERENCES employee(cpf_emp), \
FOREIGN KEY(dpt_acro) \
REFERENCES departament(acro_dept))'
# String who create the table 'relative'.
relative = 'CREATE TABLE IF NOT EXISTS relative \
(cpf_emp TEXT, name TEXT, \
gender TEXT, PRIMARY KEY (cpf_emp, name), \
FOREIGN KEY(cpf_emp) \
REFERENCES employee(cpf_emp))'
# String who create the table 'department'.
department = 'CREATE TABLE IF NOT EXISTS department \
(acro_dpt TEXT PRIMARY KEY, \
name_dpt TEXT NOT NULL, \
cpf_emp TEXT NOT NULL, \
FOREIGN KEY(cpf_emp) \
REFERENCES employee(cpf_emp))'
# String who create the table 'project'.
project = 'CREATE TABLE IF NOT EXISTS project \
(number_project INTEGER PRIMARY KEY, \
name_project TEXT NOT NULL)'
# String who create the table 'control'.
control = 'CREATE TABLE IF NOT EXISTS control \
(acro_dpt TEXT, number_project TEXT, \
PRIMARY KEY(acro_dpt, number_project), \
FOREIGN KEY(acro_dpt) \
REFERENCES department(acro_dept), \
FOREIGN KEY(number_project) \
REFERENCES project(number_project))'
# String who create the table 'develop'.
develop = 'CREATE TABLE IF NOT EXISTS develop \
(cpf_emp TEXT, number_project TEXT, \
time_worked FLOAT, \
PRIMARY KEY(cpf_emp, number_project), \
FOREIGN KEY(cpf_emp) \
REFERENCES employee(cpf_emp), \
FOREIGN KEY(number_project) \
REFERENCES project(number_project))'
try:
# Execute all the SQL create tables statements.
cur.execute(employee)
cur.execute(relative)
cur.execute(departament)
cur.execute(project)
cur.execute(control)
cur.execute(develop)
except sqlite3.Error, e:
# Throw an error if it occurs.
print 'ERROR: ' % e.args[0]
The char pointer errmsg in C shows an error string if the SQL statement doesn't execute properly. However, in Python, we use try and except to catch an error of the SQL statement execution.
def __init__(self, cur):
# String who create the table 'employee'.
employee = 'CREATE TABLE IF NOT EXISTS employee \
(cpf_emp TEXT PRIMARY KEY, \
name TEXT NOT NULL, code TEXT, \
dpt_acro TEXT, begin_date TEXT, \
FOREIGN KEY(code) REFERENCES employee(cpf_emp), \
FOREIGN KEY(dpt_acro) \
REFERENCES departament(acro_dept))'
# String who create the table 'relative'.
relative = 'CREATE TABLE IF NOT EXISTS relative \
(cpf_emp TEXT, name TEXT, \
gender TEXT, PRIMARY KEY (cpf_emp, name), \
FOREIGN KEY(cpf_emp) \
REFERENCES employee(cpf_emp))'
# String who create the table 'department'.
department = 'CREATE TABLE IF NOT EXISTS department \
(acro_dpt TEXT PRIMARY KEY, \
name_dpt TEXT NOT NULL, \
cpf_emp TEXT NOT NULL, \
FOREIGN KEY(cpf_emp) \
REFERENCES employee(cpf_emp))'
# String who create the table 'project'.
project = 'CREATE TABLE IF NOT EXISTS project \
(number_project INTEGER PRIMARY KEY, \
name_project TEXT NOT NULL)'
# String who create the table 'control'.
control = 'CREATE TABLE IF NOT EXISTS control \
(acro_dpt TEXT, number_project TEXT, \
PRIMARY KEY(acro_dpt, number_project), \
FOREIGN KEY(acro_dpt) \
REFERENCES department(acro_dept), \
FOREIGN KEY(number_project) \
REFERENCES project(number_project))'
# String who create the table 'develop'.
develop = 'CREATE TABLE IF NOT EXISTS develop \
(cpf_emp TEXT, number_project TEXT, \
time_worked FLOAT, \
PRIMARY KEY(cpf_emp, number_project), \
FOREIGN KEY(cpf_emp) \
REFERENCES employee(cpf_emp), \
FOREIGN KEY(number_project) \
REFERENCES project(number_project))'
try:
# Execute all the SQL create tables statements.
cur.execute(employee)
cur.execute(relative)
cur.execute(departament)
cur.execute(project)
cur.execute(control)
cur.execute(develop)
except sqlite3.Error, e:
# Throw an error if it occurs.
print 'ERROR: ' % e.args[0]
The char pointer errmsg in C shows an error string if the SQL statement doesn't execute properly. However, in Python, we use try and except to catch an error of the SQL statement execution.
Before populate the tables, let's going to see the main() fucntion. There, we can verify how the database was initiated. The code is so simple.
C:
int main(int argc, char *args[]) {
sqlite3_stmt * stmt;
sqlite3 * handle;
/** Open connection with the database. **/
/** If the file does not exists, create it. **/
if (sqlite3_open("sample.db",&handle)) {
printf("Connection falied!\n");
return -1;
}
/** Create the tables of our database. **/
create_database_tables(handle);
/** Populate the tables with data. **/
insert_data_to_database(handle);
/** Execute two queries: **/
/** A normal SELECT and a SELECT with INNER JOIN. **/
select_data_of_database(handle, stmt);
select_inner_join_data_of_database(handle, stmt);
/** Create a Trigger that verifies alterations **/
/** at the name column of the project's table. **/
log_trigger_database(handle, stmt);
/** Close connection. **/
sqlite3_close(handle);
return 0;
}
sqlite3_stmt * stmt;
sqlite3 * handle;
/** Open connection with the database. **/
/** If the file does not exists, create it. **/
if (sqlite3_open("sample.db",&handle)) {
printf("Connection falied!\n");
return -1;
}
/** Create the tables of our database. **/
create_database_tables(handle);
/** Populate the tables with data. **/
insert_data_to_database(handle);
/** Execute two queries: **/
/** A normal SELECT and a SELECT with INNER JOIN. **/
select_data_of_database(handle, stmt);
select_inner_join_data_of_database(handle, stmt);
/** Create a Trigger that verifies alterations **/
/** at the name column of the project's table. **/
log_trigger_database(handle, stmt);
/** Close connection. **/
sqlite3_close(handle);
return 0;
}
Python:
if __name__ == "__main__":
con = None
try:
# Open connection with the database.
# If the file does not exists, create it.
con = sqlite3.connect('sample.db')
# Get the cursor of connection.
cur = con.cursor()
# Init the database with the constructor.
db = Database(cur)
con.commit()
# Insert data into the tables.
db.insert_data_into_database(cur)
# Commit the modifications.
con.commit()
# Execute the samples of SELECT.
db.select_data_of_database(cur)
db.select_inner_join_data_of_database(cur)
# Create the Trigger example.
db.log_trigger_database(cur)
except sqlite3.Error, e:
# Throw an error if it occurs.
print "Error %s:" % e.args[0]
sys.exit(1)
finally:
# Close the connection
if con:
con.close()
con = None
try:
# Open connection with the database.
# If the file does not exists, create it.
con = sqlite3.connect('sample.db')
# Get the cursor of connection.
cur = con.cursor()
# Init the database with the constructor.
db = Database(cur)
con.commit()
# Insert data into the tables.
db.insert_data_into_database(cur)
# Commit the modifications.
con.commit()
# Execute the samples of SELECT.
db.select_data_of_database(cur)
db.select_inner_join_data_of_database(cur)
# Create the Trigger example.
db.log_trigger_database(cur)
except sqlite3.Error, e:
# Throw an error if it occurs.
print "Error %s:" % e.args[0]
sys.exit(1)
finally:
# Close the connection
if con:
con.close()
You can easily see the functions being called. In main() we simply execute the sample functions. In Python, if we don't commit the modifications, the next functions doesn't work. If we create the tables and, after, insert the data, the SELECT doesn't return values.
The next step is populate the tables with data. You can easily do it executing the strings with INSERT statements. The code seems like the create tables function. But, instead of strings with "CREATE TABLE..." we can use "INSERT..." and any other SQL word like "COMMIT", "ROLLBACK", "SELECT...", etc.
C:
Python:
C:
int insert_data_into_database(sqlite3 *handle) {
/** ERROR message. **/
char *errmsg;
/** Insert data into 'employee'. **/
sqlite3_exec(handle,"INSERT INTO employee VALUES \
('123456789','John Smith','333445555','5','09-JAN-55')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO employee VALUES \
('333445555','Franklin Wong','888665555','5','08-DEZ-45')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO employee VALUES \
('999887777','Alicia Zelaya','987654321','4','19-JUL-58')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO employee VALUES \
('987654321','Jeniffer Wallace','888665555','4','20-JUN-31')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO employee VALUES \
('666884444','Ramesh Narayan','333445555','5','15-SET-52')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO employee VALUES \
('453453453','Joyce English','333445555','5','31-JUL-62')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO employee VALUES \
('987987987','Ahmad Jabbar','987654321','4','29-MAR-59')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO employee VALUES \
('888665555','James Borg',null,'1','10-NOV-27')",
0,0,&errmsg);
/** Insert data into 'relative'. **/
sqlite3_exec(handle,"INSERT INTO relative VALUES ('333445555','Alice','F')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO relative VALUES ('333445555','Theodore','M')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO relative VALUES ('333445555','Joy','F')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO relative VALUES ('987654321','Abner','M')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO relative VALUES ('123456789','Michael','M')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO relative VALUES ('123456789','Alice','F')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO relative VALUES ('123456789','Elizabeth','F')",
0,0,&errmsg);
/** Insert data into 'department'. **/
sqlite3_exec(handle,"INSERT INTO departament VALUES \
('res','Research','333445555')",0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO departament VALUES \
('adm','Administration','987654321')",0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO departament VALUES \
('man','Management','888665555')",0,0,&errmsg);
/** Insert data into 'project'. **/
sqlite3_exec(handle,"INSERT INTO project VALUES ('1','Product X')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO project VALUES ('2','Product Y')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO project VALUES ('3','Product Z')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO project VALUES ('10','Automation')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO project VALUES ('20','Reorganization')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO project VALUES ('30','Optimization')",
0,0,&errmsg);
/** Insert data into 'control'. **/
sqlite3_exec(handle,"INSERT INTO control VALUES ('res','Product X')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO control VALUES ('res','Product Y')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO control VALUES ('res','Product Z')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO control VALUES ('man','Automation')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO control VALUES ('adm','Reorganization')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO control VALUES ('adm','Optimization')",
0,0,&errmsg);
/** Insert data into 'develop'. **/
sqlite3_exec(handle,"INSERT INTO develop VALUES ('123456789','1',32.5)",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO develop VALUES ('123456789','2',7.5)",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO develop VALUES ('666884444','3',40.0)",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO develop VALUES ('453453453','1',20.0)",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO develop VALUES ('453453453','2',20.0)",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO develop VALUES ('333445555','2',10.0)",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO develop VALUES ('333445555','3',10.0)",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO develop VALUES ('333445555','10',10.0)",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO develop VALUES ('333445555','20',10.0)",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO develop VALUES ('999887777','30',30.0)",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO develop VALUES ('999887777','10',10.0)",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO develop VALUES ('987987987','10',35.0)",
0,0,&errmsg);
}
/** ERROR message. **/
char *errmsg;
/** Insert data into 'employee'. **/
sqlite3_exec(handle,"INSERT INTO employee VALUES \
('123456789','John Smith','333445555','5','09-JAN-55')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO employee VALUES \
('333445555','Franklin Wong','888665555','5','08-DEZ-45')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO employee VALUES \
('999887777','Alicia Zelaya','987654321','4','19-JUL-58')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO employee VALUES \
('987654321','Jeniffer Wallace','888665555','4','20-JUN-31')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO employee VALUES \
('666884444','Ramesh Narayan','333445555','5','15-SET-52')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO employee VALUES \
('453453453','Joyce English','333445555','5','31-JUL-62')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO employee VALUES \
('987987987','Ahmad Jabbar','987654321','4','29-MAR-59')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO employee VALUES \
('888665555','James Borg',null,'1','10-NOV-27')",
0,0,&errmsg);
/** Insert data into 'relative'. **/
sqlite3_exec(handle,"INSERT INTO relative VALUES ('333445555','Alice','F')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO relative VALUES ('333445555','Theodore','M')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO relative VALUES ('333445555','Joy','F')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO relative VALUES ('987654321','Abner','M')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO relative VALUES ('123456789','Michael','M')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO relative VALUES ('123456789','Alice','F')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO relative VALUES ('123456789','Elizabeth','F')",
0,0,&errmsg);
/** Insert data into 'department'. **/
sqlite3_exec(handle,"INSERT INTO departament VALUES \
('res','Research','333445555')",0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO departament VALUES \
('adm','Administration','987654321')",0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO departament VALUES \
('man','Management','888665555')",0,0,&errmsg);
/** Insert data into 'project'. **/
sqlite3_exec(handle,"INSERT INTO project VALUES ('1','Product X')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO project VALUES ('2','Product Y')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO project VALUES ('3','Product Z')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO project VALUES ('10','Automation')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO project VALUES ('20','Reorganization')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO project VALUES ('30','Optimization')",
0,0,&errmsg);
/** Insert data into 'control'. **/
sqlite3_exec(handle,"INSERT INTO control VALUES ('res','Product X')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO control VALUES ('res','Product Y')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO control VALUES ('res','Product Z')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO control VALUES ('man','Automation')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO control VALUES ('adm','Reorganization')",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO control VALUES ('adm','Optimization')",
0,0,&errmsg);
/** Insert data into 'develop'. **/
sqlite3_exec(handle,"INSERT INTO develop VALUES ('123456789','1',32.5)",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO develop VALUES ('123456789','2',7.5)",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO develop VALUES ('666884444','3',40.0)",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO develop VALUES ('453453453','1',20.0)",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO develop VALUES ('453453453','2',20.0)",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO develop VALUES ('333445555','2',10.0)",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO develop VALUES ('333445555','3',10.0)",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO develop VALUES ('333445555','10',10.0)",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO develop VALUES ('333445555','20',10.0)",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO develop VALUES ('999887777','30',30.0)",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO develop VALUES ('999887777','10',10.0)",
0,0,&errmsg);
sqlite3_exec(handle,"INSERT INTO develop VALUES ('987987987','10',35.0)",
0,0,&errmsg);
}
Python:
def insert_data_into_database(self, cur):
try:
# Insert data into 'employee'.
cur.execute("INSERT INTO employee VALUES \
('123456789','John Smith','333445555','5','09-JAN-55')")
cur.execute("INSERT INTO employee VALUES \
('333445555','Franklin Wong','888665555','5','08-DEZ-45')")
cur.execute("INSERT INTO employee VALUES \
('999887777','Alicia Zelaya','987654321','4','19-JUL-58')")
cur.execute("INSERT INTO employee VALUES \
('987654321','Jeniffer Wallace','888665555','4','20-JUN-31')")
cur.execute("INSERT INTO employee VALUES \
('666884444','Ramesh Narayan','333445555','5','15-SET-52')")
cur.execute("INSERT INTO employee VALUES \
('453453453','Joyce English','333445555','5','31-JUL-62')")
cur.execute("INSERT INTO employee VALUES \
('987987987','Ahmad Jabbar','987654321','4','29-MAR-59')")
cur.execute("INSERT INTO employee VALUES \
('888665555','James Borg',null,'1','10-NOV-27')")
# Insert data into 'relative'.
cur.execute("INSERT INTO relative VALUES \
('333445555','Alice','F')")
cur.execute("INSERT INTO relative VALUES \
('333445555','Theodore','M')")
cur.execute("INSERT INTO relative VALUES \
('333445555','Joy','F')")
cur.execute("INSERT INTO relative VALUES \
('987654321','Abner','M')")
cur.execute("INSERT INTO relative VALUES \
('123456789','Michael','M')")
cur.execute("INSERT INTO relative VALUES \
('123456789','Alice','F')")
cur.execute("INSERT INTO relative VALUES \
('123456789','Elizabeth','F')")
# Insert data into 'department'.
cur.execute("INSERT INTO departament VALUES \
('res','Research','333445555')")
cur.execute("INSERT INTO departament VALUES \
('adm','Administration','987654321')")
cur.execute("INSERT INTO departament VALUES \
('man','Management','888665555')")
# Insert data into 'project'.
cur.execute("INSERT INTO project VALUES ('1','Product X')")
cur.execute("INSERT INTO project VALUES ('2','Product Y')")
cur.execute("INSERT INTO project VALUES ('3','Product Z')")
cur.execute("INSERT INTO project VALUES ('10','Automation')")
cur.execute("INSERT INTO project VALUES ('20','Reorganization')")
cur.execute("INSERT INTO project VALUES ('30','Optimization')")
# Insert data into 'control'.
cur.execute("INSERT INTO control VALUES ('res','Product X')")
cur.execute("INSERT INTO control VALUES ('res','Product Y')")
cur.execute("INSERT INTO control VALUES ('res','Product Z')")
cur.execute("INSERT INTO control VALUES ('man','Automation')")
cur.execute("INSERT INTO control VALUES ('adm','Reorganization')")
cur.execute("INSERT INTO control VALUES ('adm','Optimization')")
# Insert data into 'develop'.
cur.execute("INSERT INTO develop VALUES ('123456789','1',32.5)")
cur.execute("INSERT INTO develop VALUES ('123456789','2',7.5)")
cur.execute("INSERT INTO develop VALUES ('666884444','3',40.0)")
cur.execute("INSERT INTO develop VALUES ('453453453','1',20.0)")
cur.execute("INSERT INTO develop VALUES ('453453453','2',20.0)")
cur.execute("INSERT INTO develop VALUES ('333445555','2',10.0)")
cur.execute("INSERT INTO develop VALUES ('333445555','3',10.0)")
cur.execute("INSERT INTO develop VALUES ('333445555','10',10.0)")
cur.execute("INSERT INTO develop VALUES ('333445555','20',10.0)")
cur.execute("INSERT INTO develop VALUES ('999887777','30',30.0)")
cur.execute("INSERT INTO develop VALUES ('999887777','10',10.0)")
cur.execute("INSERT INTO develop VALUES ('987987987','10',35.0)")
except sqlite3.Error, e:
# Throw an error if it occurs.
print 'ERROR: ' % e.args[0]
try:
# Insert data into 'employee'.
cur.execute("INSERT INTO employee VALUES \
('123456789','John Smith','333445555','5','09-JAN-55')")
cur.execute("INSERT INTO employee VALUES \
('333445555','Franklin Wong','888665555','5','08-DEZ-45')")
cur.execute("INSERT INTO employee VALUES \
('999887777','Alicia Zelaya','987654321','4','19-JUL-58')")
cur.execute("INSERT INTO employee VALUES \
('987654321','Jeniffer Wallace','888665555','4','20-JUN-31')")
cur.execute("INSERT INTO employee VALUES \
('666884444','Ramesh Narayan','333445555','5','15-SET-52')")
cur.execute("INSERT INTO employee VALUES \
('453453453','Joyce English','333445555','5','31-JUL-62')")
cur.execute("INSERT INTO employee VALUES \
('987987987','Ahmad Jabbar','987654321','4','29-MAR-59')")
cur.execute("INSERT INTO employee VALUES \
('888665555','James Borg',null,'1','10-NOV-27')")
# Insert data into 'relative'.
cur.execute("INSERT INTO relative VALUES \
('333445555','Alice','F')")
cur.execute("INSERT INTO relative VALUES \
('333445555','Theodore','M')")
cur.execute("INSERT INTO relative VALUES \
('333445555','Joy','F')")
cur.execute("INSERT INTO relative VALUES \
('987654321','Abner','M')")
cur.execute("INSERT INTO relative VALUES \
('123456789','Michael','M')")
cur.execute("INSERT INTO relative VALUES \
('123456789','Alice','F')")
cur.execute("INSERT INTO relative VALUES \
('123456789','Elizabeth','F')")
# Insert data into 'department'.
cur.execute("INSERT INTO departament VALUES \
('res','Research','333445555')")
cur.execute("INSERT INTO departament VALUES \
('adm','Administration','987654321')")
cur.execute("INSERT INTO departament VALUES \
('man','Management','888665555')")
# Insert data into 'project'.
cur.execute("INSERT INTO project VALUES ('1','Product X')")
cur.execute("INSERT INTO project VALUES ('2','Product Y')")
cur.execute("INSERT INTO project VALUES ('3','Product Z')")
cur.execute("INSERT INTO project VALUES ('10','Automation')")
cur.execute("INSERT INTO project VALUES ('20','Reorganization')")
cur.execute("INSERT INTO project VALUES ('30','Optimization')")
# Insert data into 'control'.
cur.execute("INSERT INTO control VALUES ('res','Product X')")
cur.execute("INSERT INTO control VALUES ('res','Product Y')")
cur.execute("INSERT INTO control VALUES ('res','Product Z')")
cur.execute("INSERT INTO control VALUES ('man','Automation')")
cur.execute("INSERT INTO control VALUES ('adm','Reorganization')")
cur.execute("INSERT INTO control VALUES ('adm','Optimization')")
# Insert data into 'develop'.
cur.execute("INSERT INTO develop VALUES ('123456789','1',32.5)")
cur.execute("INSERT INTO develop VALUES ('123456789','2',7.5)")
cur.execute("INSERT INTO develop VALUES ('666884444','3',40.0)")
cur.execute("INSERT INTO develop VALUES ('453453453','1',20.0)")
cur.execute("INSERT INTO develop VALUES ('453453453','2',20.0)")
cur.execute("INSERT INTO develop VALUES ('333445555','2',10.0)")
cur.execute("INSERT INTO develop VALUES ('333445555','3',10.0)")
cur.execute("INSERT INTO develop VALUES ('333445555','10',10.0)")
cur.execute("INSERT INTO develop VALUES ('333445555','20',10.0)")
cur.execute("INSERT INTO develop VALUES ('999887777','30',30.0)")
cur.execute("INSERT INTO develop VALUES ('999887777','10',10.0)")
cur.execute("INSERT INTO develop VALUES ('987987987','10',35.0)")
except sqlite3.Error, e:
# Throw an error if it occurs.
print 'ERROR: ' % e.args[0]
Now, let's see two examples of SELECT statement.
The first function contains the simple SELECT.
C:
int select_data_of_database(sqlite3 * handle, sqlite3_stmt * stmt) {
const char * tail;
/** String with SELECT statement. **/
const char * select_emp = "SELECT * FROM employee";
/** Execute the SQL statement. **/
if(sqlite3_prepare_v2(handle,select_emp,-1,&stmt,&tail) != SQLITE_OK) {
printf("ERROR: Select wasn't executed!");
return -1;
}
/** Output of the execution. **/
printf("%s\n", select_emp);
printf("==========================================================\n");
while (sqlite3_step(stmt) == SQLITE_ROW) {
printf("%s | ", sqlite3_column_text(stmt, 0));
printf("%s | ", sqlite3_column_text(stmt, 1));
printf("%s | ", sqlite3_column_text(stmt, 2));
printf("%s | ", sqlite3_column_text(stmt, 3));
printf("%s\n", sqlite3_column_text(stmt, 4));
}
printf("==========================================================\n\n");
/** Finish the statement. **/
sqlite3_finalize(stmt);
return 1;
}
const char * tail;
/** String with SELECT statement. **/
const char * select_emp = "SELECT * FROM employee";
/** Execute the SQL statement. **/
if(sqlite3_prepare_v2(handle,select_emp,-1,&stmt,&tail) != SQLITE_OK) {
printf("ERROR: Select wasn't executed!");
return -1;
}
/** Output of the execution. **/
printf("%s\n", select_emp);
printf("==========================================================\n");
while (sqlite3_step(stmt) == SQLITE_ROW) {
printf("%s | ", sqlite3_column_text(stmt, 0));
printf("%s | ", sqlite3_column_text(stmt, 1));
printf("%s | ", sqlite3_column_text(stmt, 2));
printf("%s | ", sqlite3_column_text(stmt, 3));
printf("%s\n", sqlite3_column_text(stmt, 4));
}
printf("==========================================================\n\n");
/** Finish the statement. **/
sqlite3_finalize(stmt);
return 1;
}
Python:
def select_data_of_database(self, cur):
try:
# Execute the SQL statement.
cur.execute("SELECT * FROM employee")
# Output of the execution.
print "SELECT * FROM employee"
print "=========================================================="
for row in cur:
print row[0], '|', row[1], '|', row[2], '|', row[3], '|', row[4]
print "==========================================================\n"
except sqlite3.Error, e:
print 'ERROR: ' % e.args[0]
try:
# Execute the SQL statement.
cur.execute("SELECT * FROM employee")
# Output of the execution.
print "SELECT * FROM employee"
print "=========================================================="
for row in cur:
print row[0], '|', row[1], '|', row[2], '|', row[3], '|', row[4]
print "==========================================================\n"
except sqlite3.Error, e:
print 'ERROR: ' % e.args[0]
The sqlite3_prepare_v2() function prepare the output of the SQL execution to be accessed with a pointer. On the other hand, the output data can accessed in sequence with the sqlite3_step() function. The columns can be accessed with the sqlite3_column_text() function, passing the statement pointer and the index of the column.
The other example function shows a INNER JOIN statement.
C:
int select_inner_join_data_of_database(sqlite3 * handle, sqlite3_stmt * stmt) {
const char * tail;
/** String with SELECT statement. **/
const char * select_emp_and_rel = "SELECT * FROM employee \
AS C JOIN relative \
AS R ON C.cpf_emp=R.cpf_emp;";
/** Execute the SQL statement. **/
if(sqlite3_prepare_v2(handle,select_emp_and_rel,-1,&stmt,&tail) != SQLITE_OK) {
printf("ERROR: Select wasn't executed!");
return -1;
}
/** Output of the execution. **/
printf("%s\n", select_emp_and_rel);
printf("==========================================================\n");
while (sqlite3_step(stmt) == SQLITE_ROW) {
printf("%s | ", sqlite3_column_text(stmt, 0));
printf("%s | ", sqlite3_column_text(stmt, 1));
printf("%s | ", sqlite3_column_text(stmt, 2));
printf("%s | ", sqlite3_column_text(stmt, 3));
printf("%s | ", sqlite3_column_text(stmt, 4));
printf("%s | ", sqlite3_column_text(stmt, 5));
printf("%s | ", sqlite3_column_text(stmt, 6));
printf("%s\n", sqlite3_column_text(stmt, 7));
}
printf("==========================================================\n\n");
/** Finish the statement. **/
sqlite3_finalize(stmt);
return 1;
}
const char * tail;
/** String with SELECT statement. **/
const char * select_emp_and_rel = "SELECT * FROM employee \
AS C JOIN relative \
AS R ON C.cpf_emp=R.cpf_emp;";
/** Execute the SQL statement. **/
if(sqlite3_prepare_v2(handle,select_emp_and_rel,-1,&stmt,&tail) != SQLITE_OK) {
printf("ERROR: Select wasn't executed!");
return -1;
}
/** Output of the execution. **/
printf("%s\n", select_emp_and_rel);
printf("==========================================================\n");
while (sqlite3_step(stmt) == SQLITE_ROW) {
printf("%s | ", sqlite3_column_text(stmt, 0));
printf("%s | ", sqlite3_column_text(stmt, 1));
printf("%s | ", sqlite3_column_text(stmt, 2));
printf("%s | ", sqlite3_column_text(stmt, 3));
printf("%s | ", sqlite3_column_text(stmt, 4));
printf("%s | ", sqlite3_column_text(stmt, 5));
printf("%s | ", sqlite3_column_text(stmt, 6));
printf("%s\n", sqlite3_column_text(stmt, 7));
}
printf("==========================================================\n\n");
/** Finish the statement. **/
sqlite3_finalize(stmt);
return 1;
}
Python:
def select_inner_join_data_of_database(self, cur):
try:
# Execute the SQL statement.
cur.execute("SELECT * FROM employee AS C JOIN \
relative AS R ON C.cpf_emp=R.cpf_emp")
# Output of the execution.
print "SELECT * FROM employee AS C JOIN \
relative AS R ON C.cpf_emp=R.cpf_emp"
print "=========================================================="
for row in cur:
print row[0], '|', row[1], '|', row[2], '|', row[3], '|',
row[4], '|', row[5], '|', row[6], '|', row[7]
print "==========================================================\n"
except sqlite3.Error, e:
# Throw an error if it occurs.
print 'ERROR: ' % e.args[0]
try:
# Execute the SQL statement.
cur.execute("SELECT * FROM employee AS C JOIN \
relative AS R ON C.cpf_emp=R.cpf_emp")
# Output of the execution.
print "SELECT * FROM employee AS C JOIN \
relative AS R ON C.cpf_emp=R.cpf_emp"
print "=========================================================="
for row in cur:
print row[0], '|', row[1], '|', row[2], '|', row[3], '|',
row[4], '|', row[5], '|', row[6], '|', row[7]
print "==========================================================\n"
except sqlite3.Error, e:
# Throw an error if it occurs.
print 'ERROR: ' % e.args[0]
The process is exactly the same, but observe the difference between strings.
Finally, the sample has a function who creates a trigger. The trigger records changes in project's names in a table called "log"and, when it happens, the time and date is recorded too.
C:
int log_trigger_database(sqlite3 * handle, sqlite3_stmt * stmt) {
/** String who create the 'log' table used by trigger. **/
const char * log = "CREATE TABLE log(id integer PRIMARY KEY, \
project_old_name TEXT, \
project_new_name TEXT, date TEXT)";
/** String who create the trigger. **/
const char * trigger = "CREATE TRIGGER project_trigger \
UPDATE OF name_project ON project \
BEGIN INSERT INTO Log(project_old_name, \
project_new_name, date) \
VALUES(old.name_project, new.name_project, \
datetime('now')); END;";
/** UPDATE statement to verify the trigger. **/
const char * update_trigger = "UPDATE project SET name_project='Product W' \
WHERE number_project='3'";
/** String with SELECT statement. **/
const char * select_log = "SELECT * FROM log";
const char * tail;
char *errmsg;
/** Execute all the SQL statement. **/
if(sqlite3_exec(handle,log,0,0,&errmsg) != SQLITE_OK) {
printf("ERROR: %s\n", errmsg);
return -1;
}
if(sqlite3_exec(handle,trigger,0,0,&errmsg) != SQLITE_OK) {
printf("ERROR: %s\n", errmsg);
return -1;
}
if(sqlite3_exec(handle,update_trigger,0,0,&errmsg) != SQLITE_OK) {
printf("ERROR: %s\n", errmsg);
return -1;
}
if(sqlite3_prepare_v2(handle,select_log,-1,&stmt,&tail) != SQLITE_OK) {
printf("ERROR: Select wasn't executed!");
return -1;
}
/** Output of the execution. **/
printf("%s\n", select_log);
printf("==========================================================\n");
while (sqlite3_step(stmt) == SQLITE_ROW) {
printf("%d | ", sqlite3_column_int(stmt, 0));
printf("%s | ", sqlite3_column_text(stmt, 1));
printf("%s | ", sqlite3_column_text(stmt, 2));
printf("%s\n", sqlite3_column_text(stmt, 3));
}
printf("==========================================================\n\n");
/** Finish the statement. **/
sqlite3_finalize(stmt);
}
/** String who create the 'log' table used by trigger. **/
const char * log = "CREATE TABLE log(id integer PRIMARY KEY, \
project_old_name TEXT, \
project_new_name TEXT, date TEXT)";
/** String who create the trigger. **/
const char * trigger = "CREATE TRIGGER project_trigger \
UPDATE OF name_project ON project \
BEGIN INSERT INTO Log(project_old_name, \
project_new_name, date) \
VALUES(old.name_project, new.name_project, \
datetime('now')); END;";
/** UPDATE statement to verify the trigger. **/
const char * update_trigger = "UPDATE project SET name_project='Product W' \
WHERE number_project='3'";
/** String with SELECT statement. **/
const char * select_log = "SELECT * FROM log";
const char * tail;
char *errmsg;
/** Execute all the SQL statement. **/
if(sqlite3_exec(handle,log,0,0,&errmsg) != SQLITE_OK) {
printf("ERROR: %s\n", errmsg);
return -1;
}
if(sqlite3_exec(handle,trigger,0,0,&errmsg) != SQLITE_OK) {
printf("ERROR: %s\n", errmsg);
return -1;
}
if(sqlite3_exec(handle,update_trigger,0,0,&errmsg) != SQLITE_OK) {
printf("ERROR: %s\n", errmsg);
return -1;
}
if(sqlite3_prepare_v2(handle,select_log,-1,&stmt,&tail) != SQLITE_OK) {
printf("ERROR: Select wasn't executed!");
return -1;
}
/** Output of the execution. **/
printf("%s\n", select_log);
printf("==========================================================\n");
while (sqlite3_step(stmt) == SQLITE_ROW) {
printf("%d | ", sqlite3_column_int(stmt, 0));
printf("%s | ", sqlite3_column_text(stmt, 1));
printf("%s | ", sqlite3_column_text(stmt, 2));
printf("%s\n", sqlite3_column_text(stmt, 3));
}
printf("==========================================================\n\n");
/** Finish the statement. **/
sqlite3_finalize(stmt);
}
Python:
def log_trigger_database(self, cur):
try:
# Create the table 'log' used by the trigger.
cur.execute("CREATE TABLE log(id integer PRIMARY KEY, \
project_old_name TEXT, project_new_name TEXT, \
date TEXT)")
# Create the trigger.
cur.execute("CREATE TRIGGER project_trigger \
UPDATE OF name_project ON project \
BEGIN \
INSERT INTO Log(project_old_name, project_new_name, date) \
VALUES(old.name_project, new.name_project, \
datetime('now')); \
END;")
# Execute the UPDATE to check the trigger.
cur.execute("UPDATE project SET name_project='Product W' \
WHERE number_project='3'")
# Check if trigger works.
cur.execute("SELECT * FROM log")
# Output of the execution.
print "SELECT * FROM log"
print "=========================================================="
for row in cur:
print row[0], '|', row[1], '|', row[2], '|', row[3]
print "==========================================================\n"
except sqlite3.Error, e:
# Throw an error if it occurs.
print 'ERROR: ' % e.args[0]
try:
# Create the table 'log' used by the trigger.
cur.execute("CREATE TABLE log(id integer PRIMARY KEY, \
project_old_name TEXT, project_new_name TEXT, \
date TEXT)")
# Create the trigger.
cur.execute("CREATE TRIGGER project_trigger \
UPDATE OF name_project ON project \
BEGIN \
INSERT INTO Log(project_old_name, project_new_name, date) \
VALUES(old.name_project, new.name_project, \
datetime('now')); \
END;")
# Execute the UPDATE to check the trigger.
cur.execute("UPDATE project SET name_project='Product W' \
WHERE number_project='3'")
# Check if trigger works.
cur.execute("SELECT * FROM log")
# Output of the execution.
print "SELECT * FROM log"
print "=========================================================="
for row in cur:
print row[0], '|', row[1], '|', row[2], '|', row[3]
print "==========================================================\n"
except sqlite3.Error, e:
# Throw an error if it occurs.
print 'ERROR: ' % e.args[0]
As you can see, this function create the table 'log', create the trigger, update a specific project's name and execute a SELECT statement in the table 'log'. The output of SELECT statement is printed in the output stream.
Conclusion
If you don't want to use a DBMS, SQLite is a good alternative for small databases. Remember that SQLite is only a library who manipulates a file. So, a large amount of data can cause latency when you recover a data.
In Python, we have some differences from C and they will show in a next post.
Observation: The database figure will be changed for a better figure.
Subscribe to:
Posts (Atom)