Category: sqlite

SQLite – ALIAS Syntax ”; Previous Next You can rename a table or a column temporarily by giving another name, which is known as ALIAS. The use of table aliases means to rename a table in a particular SQLite statement. Renaming is a temporary change and the actual table name does not change in the database. The column aliases are used to rename a table”s columns for the purpose of a particular SQLite query. Syntax Following is the basic syntax of table alias. SELECT column1, column2…. FROM table_name AS alias_name WHERE [condition]; Following is the basic syntax of column alias. SELECT column_name AS alias_name FROM table_name WHERE [condition]; Example Consider the following two tables, (a) COMPANY table is as follows − sqlite> select * from COMPANY; ID NAME AGE ADDRESS SALARY ———- ——————– ———- ———- ———- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 (b) Another table is DEPARTMENT as follows − ID DEPT EMP_ID ———- ——————– ———- 1 IT Billing 1 2 Engineering 2 3 Finance 7 4 Engineering 3 5 Finance 4 6 Engineering 5 7 Finance 6 Now, following is the usage of TABLE ALIAS where we use C and D as aliases for COMPANY and DEPARTMENT tables respectively − sqlite> SELECT C.ID, C.NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE C.ID = D.EMP_ID; The above SQLite statement will produce the following result − ID NAME AGE DEPT ———- ———- ———- ———- 1 Paul 32 IT Billing 2 Allen 25 Engineering 3 Teddy 23 Engineering 4 Mark 25 Finance 5 David 27 Engineering 6 Kim 22 Finance 7 James 24 Finance Consider an example for the usage of COLUMN ALIAS where COMPANY_ID is an alias of ID column and COMPANY_NAME is an alias of name column. sqlite> SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE C.ID = D.EMP_ID; The above SQLite statement will produce the following result − COMPANY_ID COMPANY_NAME AGE DEPT ———- ———— ———- ———- 1 Paul 32 IT Billing 2 Allen 25 Engineering 3 Teddy 23 Engineering 4 Mark 25 Finance 5 David 27 Engineering 6 Kim 22 Finance 7 James 24 Finance Print Page Previous Next Advertisements ”;

SQLite – VACUUM ”; Previous Next VACUUM command cleans the main database by copying its contents to a temporary database file and reloading the original database file from the copy. This eliminates free pages, aligns table data to be contiguous, and otherwise cleans up the database file structure. VACUUM command may change the ROWID of entries in tables that do not have an explicit INTEGER PRIMARY KEY. The VACUUM command only works on the main database. It is not possible to VACUUM an attached database file. VACUUM command will fail if there is an active transaction. VACUUM command is a no-op for in-memory databases. As the VACUUM command rebuilds the database file from scratch, VACUUM can also be used to modify many database-specific configuration parameters. Manual VACUUM Following is a simple syntax to issue a VACUUM command for the whole database from command prompt − $sqlite3 database_name “VACUUM;” You can run VACUUM from SQLite prompt as well as follows − sqlite> VACUUM; You can also run VACUUM on a particular table as follows − sqlite> VACUUM table_name; Auto-VACCUM SQLite Auto-VACUUM does not do the same as VACUUM rather it only moves free pages to the end of the database thereby reducing the database size. By doing so it can significantly fragment the database while VACUUM ensures defragmentation. Hence, Auto-VACUUM just keeps the database small. You can enable/disable SQLite auto-vacuuming by the following pragmas running at SQLite prompt − sqlite> PRAGMA auto_vacuum = NONE; — 0 means disable auto vacuum sqlite> PRAGMA auto_vacuum = FULL; — 1 means enable full auto vacuum sqlite> PRAGMA auto_vacuum = INCREMENTAL; — 2 means enable incremental vacuum You can run the following command from the command prompt to check the auto-vacuum setting − $sqlite3 database_name “PRAGMA auto_vacuum;” Print Page Previous Next Advertisements ”;

SQLite – Injection ”; Previous Next If you take user input through a webpage and insert it into a SQLite database there”s a chance that you have left yourself wide open for a security issue known as SQL Injection. In this chapter, you will learn how to help prevent this from happening and help you secure your scripts and SQLite statements. Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a SQLite statement that you will unknowingly run on your database. Never trust user provided data, process this data only after validation; as a rule, this is done by pattern matching. In the following example, the username is restricted to alphanumerical chars plus underscore and to a length between 8 and 20 chars – modify these rules as needed. if (preg_match(“/^w{8,20}$/”, $_GET[”username”], $matches)){ $db = new SQLiteDatabase(”filename”); $result = @$db->query(“SELECT * FROM users WHERE username = $matches[0]”); } else { echo “username not accepted”; } To demonstrate the problem, consider this excerpt − $name = “Qadir”; DELETE FROM users;”; @$db->query(“SELECT * FROM users WHERE username = ”{$name}””); The function call is supposed to retrieve a record from the users table where the name column matches the name specified by the user. Under normal circumstances, $name would only contain alphanumeric characters and perhaps spaces, such as the string ilia. However in this case, by appending an entirely new query to $name, the call to the database turns into a disaster: the injected DELETE query removes all records from users. There are databases interfaces which do not permit query stacking or executing multiple queries in a single function call. If you try to stack queries, the call fails but SQLite and PostgreSQL, happily perform stacked queries, executing all of the queries provided in one string and creating a serious security problem. Preventing SQL Injection You can handle all escape characters smartly in scripting languages like PERL and PHP. Programming language PHP provides the function string sqlite_escape_string() to escape input characters that are special to SQLite. if (get_magic_quotes_gpc()) { $name = sqlite_escape_string($name); } $result = @$db->query(“SELECT * FROM users WHERE username = ”{$name}””); Although the encoding makes it safe to insert the data, it will render simple text comparisons and LIKE clauses in your queries unusable for the columns that contain the binary data. Note − addslashes() should NOT be used to quote your strings for SQLite queries; it will lead to strange results when retrieving your data. Print Page Previous Next Advertisements ”;

SQLite – LIMIT Clause ”; Previous Next SQLite LIMIT clause is used to limit the data amount returned by the SELECT statement. Syntax Following is the basic syntax of SELECT statement with LIMIT clause. SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] Following is the syntax of LIMIT clause when it is used along with OFFSET clause. SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] OFFSET [row num] SQLite engine will return rows starting from the next row to the given OFFSET as shown below in the last example. Example Consider COMPANY table with the following records − ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Following is an example, which limits the row in the table according to the number of rows you want to fetch from table. sqlite> SELECT * FROM COMPANY LIMIT 6; This will produce the following result. ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 However in certain situations, you may need to pick up a set of records from a particular offset. Here is an example, which picks up 3 records starting from the 3rd position. sqlite> SELECT * FROM COMPANY LIMIT 3 OFFSET 2; This will produce the following result. ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 Print Page Previous Next Advertisements ”;

SQLite – PHP ”; Previous Next In this chapter, you will learn how to use SQLite in PHP programs. Installation SQLite3 extension is enabled by default as of PHP 5.3.0. It”s possible to disable it by using –without-sqlite3 at compile time. Windows users must enable php_sqlite3.dll in order to use this extension. This DLL is included with Windows distributions of PHP as of PHP 5.3.0. For detailed installation instructions, kindly check our PHP tutorial and its official website. PHP Interface APIs Following are important PHP routines which can suffice your requirement to work with SQLite database from your PHP program. If you are looking for a more sophisticated application, then you can look into PHP official documentation. Sr.No. API & Description 1 public void SQLite3::open ( filename, flags, encryption_key ) Opens SQLite 3 Database. If the build includes encryption, then it will attempt to use the key. If the filename is given as ”:memory:”, SQLite3::open() will create an in-memory database in RAM that lasts only for the duration of the session. If the filename is actual device file name, SQLite3::open() attempts to open the database file by using its value. If no file by that name exists, then a new database file by that name gets created. Optional flags used to determine how to open the SQLite database. By default, open uses SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE. 2 public bool SQLite3::exec ( string $query ) This routine provides a quick, easy way to execute SQL commands provided by sql argument, which can consist of more than one SQL command. This routine is used to execute a result-less query against a given database. 3 public SQLite3Result SQLite3::query ( string $query ) This routine executes an SQL query, returning an SQLite3Result object if the query returns results. 4 public int SQLite3::lastErrorCode ( void ) This routine returns the numeric result code of the most recent failed SQLite request. 5 public string SQLite3::lastErrorMsg ( void ) This routine returns English text describing the most recent failed SQLite request. 6 public int SQLite3::changes ( void ) This routine returns the number of database rows that were updated, inserted, or deleted by the most recent SQL statement. 7 public bool SQLite3::close ( void ) This routine closes a database connection previously opened by a call to SQLite3::open(). 8 public string SQLite3::escapeString ( string $value ) This routine returns a string that has been properly escaped for safe inclusion in an SQL statement. Connect to Database Following PHP code shows how to connect to an existing database. If database does not exist, then it will be created and finally a database object will be returned. <?php class MyDB extends SQLite3 { function __construct() { $this->open(”test.db”); } } $db = new MyDB(); if(!$db) { echo $db->lastErrorMsg(); } else { echo “Opened database successfullyn”; } ?> Now, let”s run the above program to create our database test.db in the current directory. You can change your path as per your requirement. If the database is successfully created, then it will display the following message − Open database successfully Create a Table Following PHP program will be used to create a table in the previously created database. <?php class MyDB extends SQLite3 { function __construct() { $this->open(”test.db”); } } $db = new MyDB(); if(!$db) { echo $db->lastErrorMsg(); } else { echo “Opened database successfullyn”; } $sql =<<<EOF CREATE TABLE COMPANY (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL); EOF; $ret = $db->exec($sql); if(!$ret){ echo $db->lastErrorMsg(); } else { echo “Table created successfullyn”; } $db->close(); ?> When the above program is executed, it will create the COMPANY table in your test.db and it will display the following messages − Opened database successfully Table created successfully INSERT Operation Following PHP program shows how to create records in the COMPANY table created in the above example. <?php class MyDB extends SQLite3 { function __construct() { $this->open(”test.db”); } } $db = new MyDB(); if(!$db){ echo $db->lastErrorMsg(); } else { echo “Opened database successfullyn”; } $sql =<<<EOF INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Paul”, 32, ”California”, 20000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, ”Allen”, 25, ”Texas”, 15000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, ”Teddy”, 23, ”Norway”, 20000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, ”Mark”, 25, ”Rich-Mond ”, 65000.00 ); EOF; $ret = $db->exec($sql); if(!$ret) { echo $db->lastErrorMsg(); } else { echo “Records created successfullyn”; } $db->close(); ?> When the above program is executed, it will create the given records in the COMPANY table and will display the following two lines. Opened database successfully Records created successfully SELECT Operation Following PHP program shows how to fetch and display records from the COMPANY table created in the above example − <?php class MyDB extends SQLite3 { function __construct() { $this->open(”test.db”); } } $db = new MyDB(); if(!$db) { echo $db->lastErrorMsg(); } else { echo “Opened database successfullyn”; } $sql =<<<EOF SELECT * from COMPANY; EOF; $ret = $db->query($sql); while($row = $ret->fetchArray(SQLITE3_ASSOC) ) { echo “ID = “. $row[”ID”] . “n”; echo “NAME = “. $row[”NAME”] .”n”; echo “ADDRESS = “. $row[”ADDRESS”] .”n”; echo “SALARY = “.$row[”SALARY”] .”nn”; } echo “Operation done successfullyn”; $db->close(); ?> When the above program is executed, it will produce the following result. Opened database successfully ID = 1 NAME = Paul ADDRESS = California SALARY = 20000 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000 Operation done successfully UPDATE Operation Following PHP code shows how to use UPDATE statement to update any record and then fetch and display the updated records from the COMPANY table. <?php class MyDB extends SQLite3 { function __construct() { $this->open(”test.db”); } } $db = new MyDB(); if(!$db) { echo $db->lastErrorMsg(); } else { echo “Opened database successfullyn”; } $sql =<<<EOF UPDATE COMPANY set SALARY = 25000.00 where ID=1; EOF; $ret = $db->exec($sql); if(!$ret) { echo

SQLite – EXPLAIN ”; Previous Next SQLite statement can be preceded by the keyword “EXPLAIN” or by the phrase “EXPLAIN QUERY PLAN” used for describing the details of a table. Either modification causes the SQLite statement to behave as a query and to return information about how the SQLite statement would have operated if the EXPLAIN keyword or phrase had been omitted. The output from EXPLAIN and EXPLAIN QUERY PLAN is intended for interactive analysis and troubleshooting only. The details of the output format are subject to change from one release of SQLite to the next. Applications should not use EXPLAIN or EXPLAIN QUERY PLAN since their exact behavior is variable and only partially documented. Syntax syntax for EXPLAIN is as follows − EXPLAIN [SQLite Query] syntax for EXPLAIN QUERY PLAN is as follows − EXPLAIN QUERY PLAN [SQLite Query] Example Consider COMPANY table with the following records − ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Now, let us check the following sub-query with SELECT statement − sqlite> EXPLAIN SELECT * FROM COMPANY WHERE Salary &gt= 20000; This will produce the following result. addr opcode p1 p2 p3 ———- ———- ———- ———- ———- 0 Goto 0 19 1 Integer 0 0 2 OpenRead 0 8 3 SetNumColu 0 5 4 Rewind 0 17 5 Column 0 4 6 RealAffini 0 0 7 Integer 20000 0 8 Lt 357 16 collseq(BI 9 Rowid 0 0 10 Column 0 1 11 Column 0 2 12 Column 0 3 13 Column 0 4 14 RealAffini 0 0 15 Callback 5 0 16 Next 0 5 17 Close 0 0 18 Halt 0 0 19 Transactio 0 0 20 VerifyCook 0 38 21 Goto 0 1 22 Noop 0 0 Now, let us check the following Explain Query Plan with SELECT statement − SQLite> EXPLAIN QUERY PLAN SELECT * FROM COMPANY WHERE Salary &gt= 20000; order from detail ———- ———- ————- 0 0 TABLE COMPANY Print Page Previous Next Advertisements ”;

SQLite – Transactions ”; Previous Next A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program. A transaction is the propagation of one or more changes to the database. For example, if you are creating, updating, or deleting a record from the table, then you are performing transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors. Practically, you will club many SQLite queries into a group and you will execute all of them together as part of a transaction. Properties of Transactions Transactions have the following four standard properties, usually referred to by the acronym ACID. Atomicity − Ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure and previous operations are rolled back to their former state. Consistency − Ensures that the database properly changes states upon a successfully committed transaction. Isolation − Enables transactions to operate independently of and transparent to each other. Durability − Ensures that the result or effect of a committed transaction persists in case of a system failure. Transaction Control Following are the following commands used to control transactions: BEGIN TRANSACTION − To start a transaction. COMMIT − To save the changes, alternatively you can use END TRANSACTION command. ROLLBACK − To rollback the changes. Transactional control commands are only used with DML commands INSERT, UPDATE, and DELETE. They cannot be used while creating tables or dropping them because these operations are automatically committed in the database. BEGIN TRANSACTION Command Transactions can be started using BEGIN TRANSACTION or simply BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command is encountered. However, a transaction will also ROLLBACK if the database is closed or if an error occurs. Following is the simple syntax to start a transaction. BEGIN; or BEGIN TRANSACTION; COMMIT Command COMMIT command is the transactional command used to save changes invoked by a transaction to the database. COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command. Following is the syntax for COMMIT command. COMMIT; or END TRANSACTION; ROLLBACK Command ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database. ROLLBACK command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued. Following is the syntax for ROLLBACK command. ROLLBACK; Example Consider COMPANY table with the following records. ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Now, let”s start a transaction and delete records from the table having age = 25. Then, use ROLLBACK command to undo all the changes. sqlite> BEGIN; sqlite> DELETE FROM COMPANY WHERE AGE = 25; sqlite> ROLLBACK; Now, if you check COMPANY table, it still has the following records − ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Let”s start another transaction and delete records from the table having age = 25 and finally we use COMMIT command to commit all the changes. sqlite> BEGIN; sqlite> DELETE FROM COMPANY WHERE AGE = 25; sqlite> COMMIT; If you now check COMPANY table is still has the following records − ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Print Page Previous Next Advertisements ”;

SQLite – Python ”; Previous Next In this chapter, you will learn how to use SQLite in Python programs. Installation SQLite3 can be integrated with Python using sqlite3 module, which was written by Gerhard Haring. It provides an SQL interface compliant with the DB-API 2.0 specification described by PEP 249. You do not need to install this module separately because it is shipped by default along with Python version 2.5.x onwards. To use sqlite3 module, you must first create a connection object that represents the database and then optionally you can create a cursor object, which will help you in executing all the SQL statements. Python sqlite3 module APIs Following are important sqlite3 module routines, which can suffice your requirement to work with SQLite database from your Python program. If you are looking for a more sophisticated application, then you can look into Python sqlite3 module”s official documentation. Sr.No. API & Description 1 sqlite3.connect(database [,timeout ,other optional arguments]) This API opens a connection to the SQLite database file. You can use “:memory:” to open a database connection to a database that resides in RAM instead of on disk. If database is opened successfully, it returns a connection object. When a database is accessed by multiple connections, and one of the processes modifies the database, the SQLite database is locked until that transaction is committed. The timeout parameter specifies how long the connection should wait for the lock to go away until raising an exception. The default for the timeout parameter is 5.0 (five seconds). If the given database name does not exist then this call will create the database. You can specify filename with the required path as well if you want to create a database anywhere else except in the current directory. 2 connection.cursor([cursorClass]) This routine creates a cursor which will be used throughout of your database programming with Python. This method accepts a single optional parameter cursorClass. If supplied, this must be a custom cursor class that extends sqlite3.Cursor. 3 cursor.execute(sql [, optional parameters]) This routine executes an SQL statement. The SQL statement may be parameterized (i. e. placeholders instead of SQL literals). The sqlite3 module supports two kinds of placeholders: question marks and named placeholders (named style). For example − cursor.execute(“insert into people values (?, ?)”, (who, age)) 4 connection.execute(sql [, optional parameters]) This routine is a shortcut of the above execute method provided by the cursor object and it creates an intermediate cursor object by calling the cursor method, then calls the cursor”s execute method with the parameters given. 5 cursor.executemany(sql, seq_of_parameters) This routine executes an SQL command against all parameter sequences or mappings found in the sequence sql. 6 connection.executemany(sql[, parameters]) This routine is a shortcut that creates an intermediate cursor object by calling the cursor method, then calls the cursor.s executemany method with the parameters given. 7 cursor.executescript(sql_script) This routine executes multiple SQL statements at once provided in the form of script. It issues a COMMIT statement first, then executes the SQL script it gets as a parameter. All the SQL statements should be separated by a semi colon (;). 8 connection.executescript(sql_script) This routine is a shortcut that creates an intermediate cursor object by calling the cursor method, then calls the cursor”s executescript method with the parameters given. 9 connection.total_changes() This routine returns the total number of database rows that have been modified, inserted, or deleted since the database connection was opened. 10 connection.commit() This method commits the current transaction. If you don”t call this method, anything you did since the last call to commit() is not visible from other database connections. 11 connection.rollback() This method rolls back any changes to the database since the last call to commit(). 12 connection.close() This method closes the database connection. Note that this does not automatically call commit(). If you just close your database connection without calling commit() first, your changes will be lost! 13 cursor.fetchone() This method fetches the next row of a query result set, returning a single sequence, or None when no more data is available. 14 cursor.fetchmany([size = cursor.arraysize]) This routine fetches the next set of rows of a query result, returning a list. An empty list is returned when no more rows are available. The method tries to fetch as many rows as indicated by the size parameter. 15 cursor.fetchall() This routine fetches all (remaining) rows of a query result, returning a list. An empty list is returned when no rows are available. Connect To Database Following Python code shows how to connect to an existing database. If the database does not exist, then it will be created and finally a database object will be returned. #!/usr/bin/python import sqlite3 conn = sqlite3.connect(”test.db”) print “Opened database successfully”; Here, you can also supply database name as the special name :memory: to create a database in RAM. Now, let”s run the above program to create our database test.db in the current directory. You can change your path as per your requirement. Keep the above code in sqlite.py file and execute it as shown below. If the database is successfully created, then it will display the following message. $chmod +x sqlite.py $./sqlite.py Open database successfully Create a Table Following Python program will be used to create a table in the previously created database. #!/usr/bin/python import sqlite3 conn = sqlite3.connect(”test.db”) print “Opened database successfully”; conn.execute(”””CREATE TABLE COMPANY (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL);”””) print “Table created successfully”; conn.close() When the above program is executed, it will create the COMPANY table in your test.db and it will display the following messages − Opened database successfully Table created successfully INSERT Operation Following Python program shows how to create records in the COMPANY table created in the above example. #!/usr/bin/python import sqlite3 conn = sqlite3.connect(”test.db”) print “Opened database successfully”; conn.execute(“INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Paul”, 32, ”California”, 20000.00 )”); conn.execute(“INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, ”Allen”, 25, ”Texas”, 15000.00 )”); conn.execute(“INSERT INTO COMPANY

Discuss SQLite ”; Previous Next SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain. This tutorial will give you a quick start with SQLite and make you comfortable with SQLite programming. Print Page Previous Next Advertisements ”;

SQLite – AUTOINCREMENT ”; Previous Next SQLite AUTOINCREMENT is a keyword used for auto incrementing a value of a field in the table. We can auto increment a field value by using AUTOINCREMENT keyword when creating a table with specific column name to auto increment. The keyword AUTOINCREMENT can be used with INTEGER field only. Syntax The basic usage of AUTOINCREMENT keyword is as follows − CREATE TABLE table_name( column1 INTEGER AUTOINCREMENT, column2 datatype, column3 datatype, ….. columnN datatype, ); Example Consider COMPANY table to be created as follows − sqlite> CREATE TABLE COMPANY( ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); Now, insert the following records into table COMPANY − INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( ”Paul”, 32, ”California”, 20000.00 ); INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES (”Allen”, 25, ”Texas”, 15000.00 ); INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES (”Teddy”, 23, ”Norway”, 20000.00 ); INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( ”Mark”, 25, ”Rich-Mond ”, 65000.00 ); INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( ”David”, 27, ”Texas”, 85000.00 ); INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( ”Kim”, 22, ”South-Hall”, 45000.00 ); INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( ”James”, 24, ”Houston”, 10000.00 ); This will insert 7 tuples into the table COMPANY and COMPANY will have the following records − ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Print Page Previous Next Advertisements ”;