\n
What is SQLite?<\/h3>\n
SQLite is a server-less, lightweight, disk-based relational database management system that does not require any installation. The absence of any intermediate server allows it to be embedded in any application or, in our case, a test. The different data type values stored in the database can be accessed through a variant of the SQL query language. Click here<\/a> to know more about SQL data types <\/p>\n Getting setup with SQLite is easy. Python versions 2.5 and above ship a module called sqlite3<\/strong> by default. So you need to do nothing extra. To test if you have the sqlite3 module, try this statement in a Python interpreter Any module that lets you connect to a database exposes a few standard operations like creating and connecting to a database, adding a table, inserting rows, updating rows, fetching\/selecting data, closing a connection, etc. In the remainder of this post, I’ll show you how to perform these operations with sqlite3.<\/p>\n To use the sqlite3 module a connection object has to be created. You can do that like this:<\/p>\n db_name<\/strong> can be any name you like. In case there is no database, one is created. <\/p>\n Once we have the connection object, a cursor object has to be created. The cursor object is the one that executes the SQL commands.<\/p>\n Now that we have a cursor object, we can begin executing SQL commands. Let’s try creating a table in a SQLite database, insert values from the Tutorial Page<\/a> and later fetch a value from the table. The Insert values into the table:<\/b><\/p>\n The The execute statement Putting it all together:<\/b><\/p>\n NOTE:<\/strong> To make this a class and reduce the repeated code, you can take a look at the example we have in our MongoDB tutorial<\/a><\/p>\n When you run this file your output would look like this, There you have it, a getting started guide to the sqlite3 module in Python and a detailed example on how to implement it with tests.<\/p>\n If you liked this article, learn more about Qxf2’s<\/a> testing services for startups.<\/strong><\/p>\n 1. SQLite3- Official documentation : SQLite3<\/a>
\nSetup<\/h3>\n
import sqlite3<\/code>. If you get no import errors, you are all set.<\/p>\n
\nWorking with SQLite<\/h3>\n
\r\n connection_obj = sqlite3.connect(db_name)\r\n<\/pre>\n
\r\n cursor_obj = connection_obj.cursor()\r\n<\/pre>\n
\nCreate a table:<\/b><\/p>\n\r\nimport sqlite3\r\ndef create_table(db_name,table_name,table_header):\r\n\t\"Create a table\"\r\n\ttry:\r\n\t\tconnection_obj = sqlite3.connect(db_name)\r\n\t\tcursor_obj = connection_obj.cursor()\r\n\t\tcursor_obj.execute(\"CREATE TABLE %s%s\"%(table_name,tuple(table_header)))\r\n\t\tconnection_obj.commit()\r\n\t\tconnection_obj.close()\r\n\t\tprint \"-Created Table %s\"%table_name\r\n\texcept Exception,e:\r\n\t\tprint \"Python says:\",str(e)\r\n<\/pre>\n
cursor_obj.execute(\"CREATE TABLE %s%s\"%(table_name,tuple(table_header)))<\/code> command creates a new table. The connection_obj.commit()<\/code> and connection_obj.close()<\/code>saves and closes the database.<\/p>\n\r\ndef insert_values(db_name,table_name,values):\r\n\t\"Insert values into the table\"\r\n\ttry:\r\n\t\tconnection_obj = sqlite3.connect(db_name)\r\n\t\tcursor_obj = connection_obj.cursor()\r\n\t\tcursor_obj.execute(\"INSERT INTO %s VALUES (?,?,?,?)\"%table_name,tuple(values))\r\n\t\tconnection_obj.commit()\r\n\t\tconnection_obj.close()\r\n\texcept Exception,e:\r\n\t\tprint \"Python says:\",str(e)\r\n<\/pre>\n
cursor_obj.execute(\"INSERT INTO %s VALUES (?,?,?,?)\"%table_name,tuple(values))<\/code> is used to insert values into the table. A placeholder(?)<\/code> is used in the execute statements.Use ?<\/code> as a placeholder wherever you want to use a value, and then provide a tuple of values.It is preffered over the usual python string formatters. It helps you avoid SQL injection attacks<\/a>. Once the table is created and the values are inserted into the table the connection_obj.commit()<\/code> command is used to save the changes and connection_obj.close()<\/code> is used to close the connection.
\nFetch value form the table:<\/b><\/p>\n\r\ndef fetch_value(db_name,table_name,required_key,key,value):\r\n\t\"Fetch a value from the table\"\r\n\ttry:\r\n\t\tconnection_obj = sqlite3.connect(db_name)\r\n\t\tcursor_obj = connection_obj.cursor()\r\n\t\tcursor_obj.execute(\"SELECT %s FROM %s WHERE %s=? \"%(required_key,table_name,key),(value,)) #Note: The string formatting is done differently(
(value,)<\/code>) to prevent SQL injection attack.\r\n\t\tname = cursor_obj.fetchone()[0] #To get the first element in the resulting list\r\n\t\tprint \"-The %s from the Table:'%s' where %s='%s' is: %s\"%(required_key,table_name,key,value,name)\r\n\texcept Exception,e:\r\n\t\tprint \"Python says:\",str(e) \r\n\t\r\n<\/pre>\ncursor_obj.execute(\"SELECT %s FROM %s WHERE %s=? \"%(required_key,table_name,key),(value,))<\/code> searches through the table to find a value corresponding to the condition. The fetchone()<\/code> command is used to fetch a single value from the table whereas the fetchall()<\/code> can be used to fetch all the values from the table.<\/p>\n\r\n\"\"\"\r\nQxf2 Services: Utility script to create a SQLite table\r\nNOTE: This was written up for a blog post\/tutorial \r\nWe use a cleaned up and more robust version at clients\r\n\"\"\"\r\n\r\nfrom selenium import webdriver\r\nimport sqlite3\r\n\r\n\r\ndef test_create_table():\r\n\t\"Create a table, insertable and fetch values from the table\"\r\n\tdriver = webdriver.Firefox()\r\n\tdriver.get(\"https:\/\/qxf2.com\/selenium-tutorial-main\")\r\n\tdriver.maximize_window()\r\n\tdb_name = \"example.db\"\r\n\ttable_name = \"test_table\"\r\n\ttable_header = ['Name','Email','Phone','Gender']\r\n\r\n\t#Create a table\r\n\tcreate_table(db_name=db_name,table_name=table_name,table_header=table_header) \r\n\tfor i in range(1,4):\r\n\t\trow = driver.find_elements_by_xpath(\"\/\/tbody\/descendant::tr[%d]\/td\"%i)\r\n\t\ttable_values = []\r\n\t\tfor data in row:\r\n\t\t\tvalue = data.text #To get the text property of the element\r\n\t\t\ttable_values.append(value.encode('utf-8'))\r\n\t\t\r\n\t\t#Insert values into the table\t\r\n\t\tinsert_values(db_name=db_name,table_name=table_name,values=table_values)\r\n\t\r\n\t#Fetch value from the table\t\r\n\tfetch_value(db_name=db_name,table_name=table_name,required_key='Name',key='Email',value='williams@example.com') \r\n\tdriver.quit()\r\n\r\n\r\ndef create_table(db_name,table_name,table_header):\r\n\t\"Create a table\"\r\n\ttry:\r\n\t\tconnection_obj = sqlite3.connect(db_name)\r\n\t\tcursor_obj = connection_obj.cursor()\r\n\t\tcursor_obj.execute(\"CREATE TABLE %s%s\"%(table_name,tuple(table_header)))\r\n\t\tconnection_obj.commit()\r\n\t\tconnection_obj.close()\r\n\t\tprint \"-Created Table %s\"%table_name\r\n\texcept Exception,e:\r\n\t\tprint \"Python says:\",str(e)\r\n\r\n\r\ndef insert_values(db_name,table_name,values):\r\n\t\"Insert values into the table\"\r\n\ttry:\r\n\t\tconnection_obj = sqlite3.connect(db_name)\r\n\t\tcursor_obj = connection_obj.cursor()\r\n\t\tcursor_obj.execute(\"INSERT INTO %s VALUES (?,?,?,?)\"%table_name,tuple(values))\r\n\t\tconnection_obj.commit()\r\n\t\tconnection_obj.close()\r\n\texcept Exception,e:\r\n\t\tprint \"Python says:\",str(e)\r\n\r\n\r\ndef fetch_value(db_name,table_name,required_key,key,value):\r\n\t\"Fetch a value from the table\"\r\n\ttry:\r\n\t\tconnection_obj = sqlite3.connect(db_name)\r\n\t\tcursor_obj = connection_obj.cursor()\r\n\t\tcursor_obj.execute(\"SELECT %s FROM %s WHERE %s=? \"%(required_key,table_name,key),(value,))\r\n\t\tname = cursor_obj.fetchone()[0]\r\n\t\tprint \"-The %s from the Table:'%s' where %s='%s' is:\\n %s\"%(required_key,table_name,key,value,name)\r\n\texcept Exception,e:\r\n\t\tprint \"Python says:\",str(e) \r\n\r\n\r\nif __name__ == '__main__':\r\n\ttest_create_table()\r\n<\/pre>\n
\n![\"test_run\"](\"https:\/\/qxf2.com\/blog\/wp-content\/uploads\/2016\/07\/test_run2.png\")
<\/a><\/p>\n
\n
\nReferences:<\/h3>\n
\n2. TutorialsPoint : SQLite Python Tutorial<\/a>
\n3. Python Central: introduction-to-sqlite-in-python<\/a><\/p>\n
\n