CODEWITHSUNDEEP
pythonsql-serverdjango
WakeskaterX
WakeskaterX

Reputation: 1428

Error Manually Creating Table in Django

So just a little background:

What I'm doing is creating an internal app that allows users to upload CSV files and then stores each file in a new table that certain queries will be run against to do data calculations. So, I'm building a system where tables are created dynamically as files are uploaded and then can be referenced with a key for the next 60 days to avoid having to re-upload files. Rather than create dynamic models, which I did look into, I figured the easiest way would be to create a class that just executed the exact SQL I needed for the specific queries and returned the information.

The Problem:

My issue at the moment is that I can't get the create_table syntax to work correctly and it keeps throwing an error. I think I'm just having an issue understanding how it's setting the parameters correctly. I'm also connecting to Microsoft SQL Server. So here's my exception page:

Django Error Message

And here's the couple snippets of script:

table.py:

from django.db import connection
import sys

def create_table(table_name):
    print("Table name is: "+str(table_name))
    t_name = table_name
    if not table_exists(table_name):
        with connection.cursor() as c:
            c.execute("CREATE TABLE %s (RecordID int PRIMARY KEY NOT NULL IDENTITY(1,1), Email varchar(255), Mailbox varchar(255), Domain varchar(255), Match_Type varchar(50), Correction varchar(255))",[t_name])
            return True
    else:
        print("Table Already Exists: "+table_name)
        return False

processor.py:

from DataStorage.models import RecordList, RecordData
from DataStorage.table import table_exists, create_table
import string, random

def handle_uploaded_file(f,cust_name=None,):
    #Check if our file exists already and is complete
    if RecordList.objects.filter(file_name=f.name).exists():
        item = RecordList.objects.get(file_name=f.name)
        if table_exists(item.table_name):
            return None
        else:
            create_table(item.table_name)
    else:
        with open('files/'+f.name, 'wb') as destination:
            for chunk in f.chunks():
                destination.write(chunk);

        guid = generate_guid(12)
        record = RecordList(record_ID=guid,customer_name=cust_name,file_name=f.name,table_name=get_tablename(f.name))
        record.save()
        create_table(record.table_name)
        return guid

I've looked all over Stack Overflow and read the documentation here: Django Executing Custom SQL Directly, but couldn't figure out why I'm getting this error.

Upvotes: 0

Views: 1004

Answers (1)

Thomas Orozco
Thomas Orozco

Reputation: 55197

The table name in CREATE TABLE should not be quoted. This also means it can't be passed as a parameter (in a prepared statement in your case).

You'll need to use regular string interpolation for it:

  c.execute("CREATE TABLE %s (<snip>)" % t_name)

You'll have to be very careful not to introduce a SQL injection here. t_name must be properly sanitized (and that doesn't mean quoted).

Upvotes: 1

Related Questions