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 : https://dynamic-models.readthedocs.io/en/latest/index.html — May.D, Sep 03 '21 at 09: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, Sep 03 '21 at 13:20
I need the tables as each name that is created have more stuff to store using formset. The one who task me to do it doesnt want me to centralized all the data in a table with a foreign key as it will be alot of data to go thru for editing and deletion. @Lewis — L.Calvin, Sep 06 '21 at 01:07
Does this answer your question? [Using dynamic models in Django framework](https://stackoverflow.com/questions/31835362/using-dynamic-models-in-django-framework) — Abdul Aziz Barkat, Sep 06 '21 at 09:00
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](https://django-tenant-schemas.readthedocs.io/en/latest/) if multi-tenancy is your goal. — Abdul Aziz Barkat, Sep 06 '21 at 09:02
I understand it is a bad idea. But the one who handed me the task wants this to be done and use the existing db which is SQL. Thanks for the link. I will take a look at it. @AbdulAzizBarkat — L.Calvin, Sep 06 '21 at 09:11

score 1 · Accepted Answer · edited Sep 07 '21 at 09:21

1

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 07 '21 at 09:21

Peter Mortensen

30,738
21
105
131

answered Sep 06 '21 at 05:49

berkeebal

463
4
8

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 – L.Calvin Sep 06 '21 at 06:21
No problem. If you an answer is useful or right solution for you read [this](https://stackoverflow.com/help/someone-answers) 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,)` – berkeebal Sep 06 '21 at 07:04
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 – L.Calvin Sep 06 '21 at 07:40
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. – berkeebal Sep 06 '21 at 07:55
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``` – L.Calvin Sep 06 '21 at 09:39
`{string1}` and `{string2}` should be surrounded with quotes. Like `".... VALUES ('{string1}','{string2}')" `. – berkeebal Sep 06 '21 at 10:05
Thank you very much! Really sorry but one more question. How do i reference to this table. Because in django, u reference to the declared table to let the view know of such table by importing from .models and .forms. In this case if i want to call the newly created table, what do i import. My purpose currently is to make a new table, use it as modelfactoryset and by the end, i delete it away. So a temp table for me to do some work – L.Calvin Sep 07 '21 at 04:01
As far as I know it is not possible to import that table like regular django models. Because django `django.models.Model` class helps you create tables and manage records with regular python class. But in this case you did not create a python class instead directly created table with raw sql. Now to manipulate those record you have to use raw sql . – berkeebal Sep 07 '21 at 06:31
Wait what? But wont using raw sql becomes vulnerable in my future codes for this raw sql generated table? For my other sql statements right now, it is being checked by django by is_valid() as the tables i created are referring to data in a existing table. So i assume its safe. – L.Calvin Sep 07 '21 at 06:45
I assume you mean `form.is_valid()`, the django forms main purpose is validating html forms. The SQL operations is not about django forms. So using raw SQL for projection you have to be careful to SQL injections. Which you can find examples in everywhere. This comment thread has became quite long. So for further questions create a chat. – berkeebal Sep 07 '21 at 06:56
So that means due to this```'{string1}'```, the whole web is vulnerable to the injections? Is there some other platforms we can chat? My reputation is not enough for the chat feature – L.Calvin Sep 07 '21 at 07:11
Sorry about this but is there a way for me to display it in a html page after extracting the data out? – L.Calvin Sep 08 '21 at 07:24
Of course, like other python data types you can render those datas in html. Checkout django`django.shortcuts.render` function and django template language. – berkeebal Sep 08 '21 at 07:47
Thank you. May I ask if i want to use Django Rest Framework on this newly created table, how do i do the following? In ```serializers.py``` , there has to be some model. How do i link this model to the newly created table? – L.Calvin Oct 04 '21 at 04:09
So think about this way, Django model classes are python classes that help you to interact and do operations on your database. And model serializers actually validates and normalizes your input helps to interact with django models. So how can you create a model serializer without having a model? :). I think you should go with `rest_framework.serializers.Serializer` object. – berkeebal Oct 04 '21 at 07:21
Thanks but is it possible for you to check what is wrong with my code? My data has a list of items. I used for loop to get it but it is only printing the last item in the list – L.Calvin Oct 04 '21 at 09:50
That question is beyond this scope also this thread is quite long. Please open another question and let me also the community to work on it. – berkeebal Oct 04 '21 at 10:21
I have posted at new question about it at https://stackoverflow.com/questions/69444088/printing-multiple-items-using-serializer. Would be very grateful for your help ! – L.Calvin Oct 05 '21 at 01:58

Dynamically creating tables using SQLite and Django

1 Answers1