CODEWITHSUNDEEP
pythonsqlpython-3.xsqlite
Arslan
Arslan

Reputation: 23

SQLite3 help, saving variables into a databse

I have 2 things I needed help with:

1) I am unsure as to how I can check if a table exists in python using the sqlite3 library.

2) I am unsure as to how I can save variables from the program to a database. I want to be able to check if UserDetails exists before making the database.

I've been reading around and everyone is doing stuff differently,

Here is the section of my code that is responsible for saving the variables:

    connection = sqlite3.connect("UserDetails.db") 
    crsr = connection.cursor() 

    #create table
    sql_command = table_creator
    crsr.execute(sql_command)

    #insert values into table
    data_to_insert = (username, first_name, surname, age, user_salt, password_hash, date_today)
    sql_command = """INSERT INTO UserDetails VALUES ((?, ?, ?, ?, ?, ?, ?), data_to_insert);"""
    crsr.execute(sql_command)

    connection.commit() #save changes
    connection.close()  #terminate connection

and in case you want to see table_creator it looks like this:

table_creator = '''CREATE TABLE `UserDetails` ( `Username` VARCHAR(8) NOT NULL, `Firstname` VARCHAR(10) NOT NULL, `Surname` VARCHAR(20) NOT NULL, `Age` INT(2) NOT NULL, `Salt` VARCHAR(10) NOT NULL, `Hash` VARCHAR(64) NOT NULL, `Date` DATE NOT NULL, PRIMARY KEY (`UserName`) );'''

I will appreciate and feedback or support. I am still learning to code, and my CompSci teacher doesnt teach us Python specifically, so what I know is self taught.

Oh and this is the error message I get:

Traceback (most recent call)
  File "c:/Users/Arslan/Project A2/login.py", line 99, in <module>
    save_details()
  File "c:/Users/Arslan/Project A2/login.py", line 93, in save_details
    crsr.execute(sql_command)
sqlite3.OperationalError: no such column: data_to_insert

Upvotes: 0

Views: 67

Answers (2)

Mahrez BenHamad
Mahrez BenHamad

Reputation: 2068

How to check if a table exists or no :

  • The first way :

Use this query:

SELECT name FROM sqlite_master WHERE type='table' AND name='{table_name}';

Modify {table_name} with your table to check There are two cases :

. If the cursor equal to 0 ==> the table does not exist Else, the table exists

  • The second way:

Use :

 PRAGMA table_info(table_name)

example:

enter image description here

  • The third way :

Use this query :

select 1 from table

It will return the constant 1 for every row of the table if the table exists, or nothing if not.

  • There are many other ways, but I listed the best in my opinion.

How to save variables from the program to a database:

To insert data into sqlite3, you can use :

cursor.execute("insert into UserDetails values (?, ?, ?, ?, ?, ?, ?)", (username, firstname, surname, age, salt, hash, date))

DON'T USE (SQL injection):

cursor.execute("insert into UserDetails values ('{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}')".format(username, firstname, surname, age, salt, hash, date))

Don't forget :

conn.commit()

Or you can use instead of it the connection as a context manager:

with conn:
   # then cursor.execute..

Upvotes: 1

GMB
GMB

Reputation: 222422

1) I am unsure as to how I can check if a table exists in python using the sqlite3 library.

Use CREATE TABLE IF NOT EXISTS:

table_creator = '''CREATE TABLE IF NOT EXISTS `UserDetails` ( 
    `Username` VARCHAR(8) NOT NULL,
    `Firstname` VARCHAR(10) NOT NULL,
...
);'''

2) I am unsure as to how I can save variables from the program to a database.

You can pass variables for insert with the following syntax:

 data_to_insert = (username, first_name, surname, age, user_salt, password_hash, date_today)
sql_command = '''INSERT INTO UserDetails VALUES (?, ?, ?, ?, ?, ?, ?)''';
crsr.execute(sql_command, data_to_insert )

Upvotes: 0

Related Questions