Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
2.41/5 (3 votes)
Hi these are my tables

music_name = char(username),userId(int)

meaning = char(meaning),userid(int)

and i want to store the result on third table=music_result(username,meaning)

i have used the join to merge this


when i tried to do this i got an error like

sqlite3.IntegrityError: datatype mismatch

please do help me to solve this error

What I have tried:

db = sqlite3.connect('db.sqlite3')

cursor = db.cursor()


cursor.execute('\n'

                       'INSERT INTO  music_result SELECT username, meaning ,NULL\n'

                       'FROM music_name N JOIN music_sample1 T ON N.userid = T.userid \n')
        db.commit() 
Posted
Updated 23-Aug-18 0:12am
Comments
Herman<T>.Instance 23-Aug-18 3:04am    
If a table has 2 fields ('and i want to store the result on third table=music_result(username,meaning)') what is the purpose of the NULL field (3rd field) in your query?
Ruban Bharath 23-Aug-18 4:44am    
Sir if i remove the NULL
i have this error what can i do for it
sorry i have userId field too in result table
"table music_result has 3 columns but 2 values were supplied"
Herman<T>.Instance 23-Aug-18 4:52am    
and what is the field definition of field 3? Can the field hold NULL values?
What are the field definitions for field 1 and 2. Does that match?
Ruban Bharath 23-Aug-18 4:54am    
sir sorry what is meant by field definition
are you asking about table creation query
Herman<T>.Instance 23-Aug-18 5:45am    
the definition will be in the create table script where you state:
field [datatype] [not] null ..
Which datatypes are fields 1 and 2 and is field 3 nullable?

1 solution

ahhh now it is clear:
PHP
class result(models.Model):
userid = models.IntegerField(primary_key=True)

a PrimaryKey field cannot be NULL.
Can you set it up as Identity field. A number for ID is than generated and is not needed in the INSERT query.
 
Share this answer
 
Comments
Ruban Bharath 23-Aug-18 7:20am    
NO sir i could not resolved it
i will show my contents to you
Ruban Bharath 23-Aug-18 7:23am    
views.py

def olddata(request):
username = request.POST.get("username", False)
if is_valid(username):
data = result.objects.filter(username=username)
return render(request, "result/index.html", {'data': data})
else:
name = Name(username=username)
name.save()
db = sqlite3.connect('db.sqlite3')
cursor = db.cursor()
cursor.execute('''SELECT meaning from music_sample''')
rand = cursor.fetchall()
rand_item = rand[random.randrange(len(rand))]
cursor.execute('INSERT INTO music_sample1(userid,meaning) values(NULL,?)', rand_item)
cursor.execute('''INSERT INTO music_result SELECT username,meaning, NULL
FROM music_name N JOIN music_sample1 T ON N.userid = T.userid ''')
db.commit()
for row in result.objects.all():
if result.objects.filter(username=row.username).count() > 1:
row.delete()
data = result.objects.filter(username=username)
return render(request, "result/index.html", {'data': data})


def is_valid(username):
con = sqlite3.connect('db.sqlite3')
cur = con.cursor()
cur.execute('SELECT username FROM music_output')
data = cur.fetchall()
for row in data:
if row[0] == username:
return True
return False


then my models are:

class sample(models.Model):
userid = models.IntegerField(primary_key=True)
meaning = models.CharField(max_length=300)


class sample1(models.Model):
userid = models.IntegerField(primary_key=True)
meaning = models.CharField(max_length=300)


class Name(models.Model):
userid = models.IntegerField(primary_key=True)
username = models.CharField(max_length=300,)


class result(models.Model):

userid = models.IntegerField()
meaning = models.CharField(max_length=300)
username = models.CharField(max_length=300)

what's wrong with this code i am getting error now like

"NOT NULL constraint failed: music_sample1.userid"
Herman<T>.Instance 23-Aug-18 7:53am    
the table is in your SqlLite3 db. There you have to make the field PK and AutoIncrement/Identity not in your source code
Ruban Bharath 23-Aug-18 8:10am    
sorry i am not clear with it can you tell me broadly please sir
Herman<T>.Instance 23-Aug-18 9:53am    
read this.
It is about the definiton for PRIMARY KEY.

2.0 The INTEGER PRIMARY KEY
One exception to the typelessness of SQLite is a column whose type is INTEGER PRIMARY KEY. (And you must use "INTEGER" not "INT". A column of type INT PRIMARY KEY is typeless just like any other.) INTEGER PRIMARY KEY columns must contain a 32-bit signed integer. Any attempt to insert non-integer data will result in an error.

INTEGER PRIMARY KEY columns can be used to implement the equivalent of AUTOINCREMENT. If you try to insert a NULL into an INTEGER PRIMARY KEY column, the column will actually be filled with an integer that is one greater than the largest key already in the table. Or if the largest key is 2147483647, then the column will be filled with a random integer. Either way, the INTEGER PRIMARY KEY column will be assigned a unique integer. You can retrieve this integer using the sqlite_last_insert_rowid() API function or using the last_insert_rowid() SQL function in a subsequent SELECT statement.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900