Dynamically creating tables using SQLite and Django

Question

I am given a task for a web application I’m developing currently. Currently, my code allow me to do the necessary saving to the existing tables, but I am unsure of how to do the following task. The task is to dynamically create tables as long as the 'save' button is pressed in my web application. I am using SQLite for my database.

Example: I have the field of 'name'. So the user types Test for the name field. Upon saving, this name is stored in an existing table and register under a id of 1. At the same time, I want to be able to create a new table with its own fields. This table will be named example_(id). So in this case it will be example_1.

I’m a beginner in Django and SQL so if anyone can guide/help me in any way, thank you!

Got the error of

views.py

@api_view(['GET'])
def selected_device(request,pk=None):
    if pk != None:
        devices = Device.objects.filter(pk=pk)
        devicedetail = DeviceDetail.objects.filter(DD2DKEY=pk)
        cursor = connection.cursor()
        tablename= "dev_interface_" + str(pk)
        cursor.execute(f"SELECT interface FROM {tablename} ")
        righttable = cursor.fetchall()
        devserializer = DeviceSerializers(devices, many=True)
        devdserializer = DeviceDetailSerializers(devicedetail, many=True)
        interfaces = []
        for i in righttable:
            interfaces.append(i[0])            
        for i in interfaces: 
                data =[{"interface": i}]  
        interserializer = InterfaceSerializers(data, many = True)
        results = {
            "device":devserializer.data,
            "device_details" : devdserializer.data,
            "interface":interserializer.data,
        }
        return Response(results)

In interfaces, I have the following ['G0/1', 'TenGigabitEthernet1/1/3', 'TenGigabitEthernet1/1/5', 'TenGigabitEthernet1/1/20', 'TenGigabitEthernet1/1/21', 'TenGigabitEthernet1/1/22', 'TenGigabitEthernet1/1/23', 'TenGigabitEthernet1/1/24', 'TenGigabitEthernet1/1/25', 'TenGigabitEthernet1/1/26']

I think you can find a solution here : dynamic-models.readthedocs.io/en/latest/index.html — May.D
– May.D, Commented Sep 3, 2021 at 9:51
Like in the regular form views, fetch the user input with django form, create a database connection and create a new table with raw sql in your view. — berkeebal
– berkeebal, Commented Sep 3, 2021 at 13:20
Note: Creating tables dynamically is a bad idea, it increases the number of tables you need to manage, generally you would normalize your schema and add foreign keys to some extra table to maintain such relations. You can also try using PostgreSQL schemas by using django-tenant-schemas if multi-tenancy is your goal. — Abdul Aziz Barkat
– Abdul Aziz Barkat, Commented Sep 6, 2021 at 9:02

Peter Mortensen · Accepted Answer · 2021-09-07 09:21:57Z

2

I have mentioned in the comments that you can use database connection with raw SQL. Here is an example for you:

from django.db import connection

# Create a connection with your database
cursor = connection.cursor()

# Execute your raw SQL
cursor.execute("CREATE TABLE NameTable(name varchar(255));")
# Create database records
cursor.execute("INSERT INTO NameTable VALUES('ExampleName')")
# Fetch records from the database
cursor.execute("SELECT * FROM NameTable")

# Get the data from the database. fetchall() can be used if you would like to get multiple rows
name = cursor.fetchone()

# Manipulate data
# Don't forget the close database connection
cursor.close()

This is just a basic example about database connection in Django. Customize it depending on your needs. Here is the official documentation for raw SQL and database connections. Also keep in mind that what you are trying to do may not be the best practice or recommended.

edited Sep 7, 2021 at 9:21

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Sep 6, 2021 at 5:49

berkeebal

4934 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

18 Comments

L.Calvin Over a year ago

Thanks for the example! May I ask how to change the table name to the scenario (example_(id)) in my post. Because I need the separate tables which is tie to individual IDs stored in existing table. Such as I have 2 name under ID of 1 and 2. So i will have 2 new tables which are example_1 and example_2

berkeebal Over a year ago

No problem. If you an answer is useful or right solution for you read this to say thanks. Secondly just create a string with name input and id with it. instead of using ExampleTable name use the string that you have builted. And be aware of SQL injection since you are using raw input on db operations. Something similiar to this string =instance.name + '_' + str(instance.id); query = "CREATE TABLE (?) (name varchar(255);)",(string,)

L.Calvin Over a year ago

I'm waiting for any other method to compare. I would like to give u a upvote currently but it is not letting me to vote as my reputation is less than 15. Sorry about that. Regards to the proposed solution, what is the query for and the (string,)? Is that some sql syntax?

string =  'dev_interface_' + str(deviceD.id); cursor.execute("CREATE TABLE ('string')(id integer NOT NULL PRIMARY KEY AUTOINCREMENT, mgt_interface varchar(50) NOT NULL, mgt_ip varchar(15) NOT NULL.....)

The string is having a syntax error. I tried string, 'string', "string". All cannot work though

berkeebal Over a year ago

You don't need brackets to name your table. If this looks confusing just create a formatted string and pass it as a parameter to execute function. EX:

query = f"CREATE TABLE {string} ( id integer NOT NULL PRIMARY KEY AUTOINCREMENT, mgt_interface varchar(50) NOT NULL, mgt_ip varchar(15) NOT NULL.....)"

and cursor.execute(query). string is the name string that you have built for the table name.

L.Calvin Over a year ago

Thank you very much! Just one last question. Im trying to update the newly created table but i cant seem to be able to update it. My code is : queryagain = f"INSERT INTO {string}(mgt_interface, mgt_ip) VALUES ({string1}, {string2})" with string1=deviceD.mgt_interface and string2= new_device.ipaddr

|

Collectives™ on Stack Overflow

Dynamically creating tables using SQLite and Django

1 Answer 1

18 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

18 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related