Python Forum
Basic SQL query using Py: Inserting or querying sqlite3 database not returning data
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Basic SQL query using Py: Inserting or querying sqlite3 database not returning data
#1
Hello, can someone assist here please:
When I run the program below, I expect table to be created and record inserted into the database.
However when I query database, it displays no records.

Please let me know if you can point out what is missing here from either query or insert operation.

Thank you,
Marlon

=====
import sqlite3
def insert():


	connection_obj = sqlite3.connect('apix.db')
	cursor_obj = connection_obj.cursor()
	cursor_obj.execute("DROP TABLE IF EXISTS APIX")
	table = """ CREATE TABLE APIX (
				IP VARCHAR(255) NOT NULL,
				DATE CHAR(8) NOT NULL
			); """
	
	cursor_obj.execute(table)
	print("Table is Ready. Now I will insert...")
	connection_obj.execute ("""INSERT INTO APIX (IP,Date) VALUES ("1.1.1.1","03/03/01")""")
	connection_obj.close()


def query():

	connection_obj = sqlite3.connect('apix.db')
	cursor_obj = connection_obj.cursor()
	statement = '''SELECT * FROM APIX'''
	cursor_obj.execute(statement)
	output = cursor_obj.fetchall()
	for row in output:
  		print(row)
  
	connection_obj.commit()
	connection_obj.close()
	return output

insert()
print(query())
(venv) [email protected] venv % python delmain.py
Table is Ready. Now I will insert...
[]
(venv) [email protected] venv %
Reply
#2
Please use proper bbcode when posing to keep the format.
I welcome all feedback.
The only dumb question, is one that doesn't get asked.

My Scripts
CookBook - Shmup - PyQt5 Music Player


Reply
#3
You need to commit in the insert() (i.e. after changing the DB - CREATE, INSERT, etc.) , not in query() (i.e. SELECT - what do you commit after selecting?)
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#4
My bad, once I posted, I verified the commit in the wrong code block. Thanks all for the replies.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Same Data Showing Several Times With Beautifulsoup Query eddywinch82 2 620 May-29-2022, 11:46 PM
Last Post: eddywinch82
  Query in sqlite database frewil 2 801 Feb-06-2022, 05:35 PM
Last Post: frewil
  I need help parsing through data and creating a database using beautiful soup username369 1 1,110 Sep-22-2021, 08:45 PM
Last Post: Larz60+
  SaltStack: MySQL returner save less data into Database table columns xtc14 2 1,482 Jul-02-2021, 02:19 PM
Last Post: xtc14
  Python Variables and Sqlite3 Database Staples200 1 1,589 May-25-2021, 02:40 AM
Last Post: Staples200
  sqlite3 database problem Maryan 2 1,812 Oct-05-2020, 05:21 AM
Last Post: buran
  Looping to read data in database CEC68 1 1,214 Sep-24-2020, 08:54 PM
Last Post: scidam
  Adding data to a table in SQLite3 djwilson0495 2 2,140 Aug-15-2020, 02:48 PM
Last Post: djwilson0495
  Get database used data space from pyodbc susja 1 1,493 Aug-14-2020, 02:01 PM
Last Post: susja
  Fastest Method for Querying SQL Server with Python Pandas BuJayBelvin 7 4,340 Aug-02-2020, 06:21 PM
Last Post: jefsummers

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020