not able to get SQLite database in python

Question

1) I am new to python and I am using SQLITE database. While setting up the connection I am not able to get the database(Which I already made in SQLite and tables as well)

2) While executing the insert query I am getting the following error : "sqlite3.OperationalError: no such table: MemberDetails"

HERE IS THE CODE:

import sqlite3
my_cursor = ""
conn = ""
# setting the connection with database
# def con_database_connection():
# try:
globals()
conn = sqlite3.connect('CareForNation.db')
# new_con = sqlite3.connect('CareForNation.db')
print("connection established successfully")
my_cursor = conn.cursor()
# except sqlite3 as error
# print("Connection failed")
# Login Admin Control
user_name = input("Enter user name")
user_password = input("Enter Password")
if user_name == "123" and user_password == "123":
 print("Login successful")
else:
 print("Invalid user name or password")
# Inserting member information
# con_database_connection()
mid = input("Enter Member ID")
member_name = input("Enter member name")
member_phone_number = input("enter phone number")
member_address = input("Enter address")
insert_data = my_cursor.execute("insert into MemberDetails(Member_ID, Member_Name, Phone_Number, address) "
 "values('mid', 'member_name', 'member_phone_number', member_address)")
# abc = conn.executescript("select * from MemberDetails")
print("New data entered to Member details module")
conn.close()

• Klaus D.: Try to use the full path to the database!
• Jona: Are you sure that there is a table "MemberDetails" in your database ?
• Exelian: The error seems pretty clear. What steps have you taken te verify that the database does indeed contain the specified table? Have you tried accessing the DBwith something like: sqlitebrowser.org?
• Ashwani Pandita: Yes, I created the table MemberDetails in the database
• Exelian: Also note that the sqlite3 module for python does NOT auto-commit. You'll have to commit your changes each time you close the file

Answer 1

First create the table:

my_cursor.execute("""CREATE TABLE MemberDetails(
 mid text,
 member_name text,
 member_phone_number text,
 member_address text
 )""")

Then, replace

insert_data = my_cursor.execute("insert into MemberDetails(Member_ID, Member_Name, Phone_Number, address)"
 "values('mid', 'member_name', 'member_phone_number', member_address)"
 )

With:

with conn:
 my_cursor.execute("INSERT INTO MemberDetails VALUES (:mid, :member_name, :member_phone_number, :member_address)",
 {'mid': mid, 'member_name': member_name, 'member_phone_number': member_phone_number, 'member_address': member_address})

• Ashwani Pandita: I used the above query format but still getting the same error of "sqlite3.OperationalError: no such table: MemberDetails"
• Gustav Rasmussen: Ah, you need to create the table first. Give me a minute and I will update the answer ..

Answer 2

[Updated]

I've made some change in your code it works perfectly now!

import sqlite3
my_cursor = ""
conn = ""
# setting the connection with database
# def con_database_connection():
# try:
globals()
conn = sqlite3.connect('CareForNation.db')
# new_con = sqlite3.connect('CareForNation.db')
print("connection established successfully")
my_cursor = conn.cursor()
# except sqlite3 as error
# print("Connection failed")
# Login Admin Control
user_name = input("Enter user name")
user_password = input("Enter Password")
if user_name == "123" and user_password == "123":
 print("Login successful")
else:
 print("Invalid user name or password")
# Inserting member information
# con_database_connection()
mid = input("Enter Member ID")
member_name = input("Enter member name")
member_phone_number = input("enter phone number")
member_address = input("Enter address")
my_cursor.execute("create table if not exists MemberDetails(Member_ID, Member_Name, Phone_Number, address)")
insert_data = my_cursor.execute("insert into MemberDetails (Member_ID, Member_Name, Phone_Number, address) values (?, ?, ?, ?)", (mid, member_name, member_phone_number, member_address))
# abc = conn.executescript("select * from MemberDetails")
print("New data entered to Member details module")
conn.close()
~ 

• Ashwani Pandita: Even If I run select * query, I do get the same error