CODEWITHSUNDEEP
pythonsqlite
James Green
James Green

Reputation: 125

Creating an sql table based off of a variable

I am creating a database that includes 3 primary tables:

Users, Assignments, Groups

With other potential tables that relate to the table 'Groups'. These potential tables are meant to be tables which are named based on the key variable in the "Groups" table. eg if the Groups table has an entry with groupName = "Group_One", I want to create a table called "Group_One" and then store the usernames of other users into that table. As don't see a practical way to store multiple usernames in one row of the 'Groups' table.

Here is the code I am testing to try and implement this:

import sqlite3

def Database_Setup():
    Cur.executescript(
                        """
                        CREATE TABLE IF NOT EXISTS USERS
                        (
                        username text,
                        password text,
                        clearance int,
                        classes int
                        );
                        CREATE TABLE IF NOT EXISTS GROUPS 
                        (
                        groupName text
                        teacher text,
                        teachingAssistant text
                        users
                        );
                        CREATE TABLE IF NOT EXISTS ASSIGNMENTS
                        (
                        assignmentID int,
                        assignmentName text,
                        assignmentInfo text,
                        dueDate date,
                        setDate date,
                        completedAmount int
                        )
                        """
                        )
def Potential_Solution():
    Group_Name = "Group1"
    List_Of_Users = ["User1","User2","User3"]
    Cur.execute("""
                      CREATE TABLE IF NOT EXISTS {}
                      (
                      username text,
                      randomVar text
                      )
                      """.format(Group_Name))
                      # This part works fine ^^
    for User in List_Of_Users:
        Cur.execute("INSERT INTO TABLE ? values (?,'Some_Var')",(Group_Name,User))

def Main():
    Database_Setup()
    Potential_Solution()
    Cur.execute("SELECT * FROM Group1")
    print(Cur.fetchall())

if __name__ == "__main__":
    Conn = sqlite3.connect("FOO_DB.db")
    Cur = Conn.cursor()
    Main()

However when I execute this, I run into this error:

Traceback (most recent call last):
  File "E:/Python/Py_Proj/DB LIST vs new db example.py", line 53, in <module>
    Main()
  File "E:/Python/Py_Proj/DB LIST vs new db example.py", line 46, in Main
    Potential_Solution()
  File "E:/Python/Py_Proj/DB LIST vs new db example.py", line 42, in Potential_Solution
    Cur.execute("INSERT INTO TABLE ? values (?,Some_Var)",(Group_Name,User))
sqlite3.OperationalError: near "TABLE": syntax error

is there a practical way to do what I am trying to achieve? Or should I resort to another method?

Upvotes: 0

Views: 38

Answers (1)

user_D_A__
user_D_A__

Reputation: 460

I tried the following , 
you have to remove TABLE keyword use positional formatting 
Cur.execute("INSERT INTO {0} VALUES('{1}', 'SomeVar')".format(Group_Name,User )
                )

import sqlite3

def Database_Setup():
    Cur.executescript(
                        """
                        CREATE TABLE IF NOT EXISTS USERS
                        (
                        username text,
                        password text,
                        clearance int,
                        classes int
                        );
                        CREATE TABLE IF NOT EXISTS GROUPS 
                        (
                        groupName text
                        teacher text,
                        teachingAssistant text
                        users
                        );
                        CREATE TABLE IF NOT EXISTS ASSIGNMENTS
                        (
                        assignmentID int,
                        assignmentName text,
                        assignmentInfo text,
                        dueDate date,
                        setDate date,
                        completedAmount int
                        )
                        """
                        )
def Potential_Solution():
    Group_Name = "Group1"
    List_Of_Users = ["User1","User2","User3"]
    Cur.execute("""
                      CREATE TABLE IF NOT EXISTS {}
                      (
                      username text,
                      randomVar text
                      )
                      """.format(Group_Name))
    for User in List_Of_Users:
        Cur.execute("INSERT INTO {0} VALUES('{1}', 'SomeVar')".format(Group_Name,User )
                    )





def Main():
    Database_Setup()
    Potential_Solution()
    Cur.execute("SELECT * FROM Group1")
    print(Cur.fetchall())

if __name__ == "__main__":
    Conn = sqlite3.connect("FOO_DB.db")
    Cur = Conn.cursor()
    Main()

Upvotes: 2

Related Questions