CODEWITHSUNDEEP
sqlitepython-2.7
Common People
Common People

Reputation: 43

Sqlite database does not store previous data inserted

Hi i'm new to python and sqlite and i was just testing a simple coding as below import sqlite3

os.chdir("C:\Backup")
conn = sqlite3.connect('FBG.db')
c=conn.cursor()
c.execute('''create table if not exists A (time INTEGER PRIMARY KEY, data REAL)''')
data=(1,2)
c.execute("insert into A (time, data) values (?,?)", data)

c.execute('SELECT * FROM A ORDER BY time').fetchall()

c.close()

So the problem is every time, i run the code, i was assuming it will store the pervious data, the list of database will become bigger, but what happened is that no matter how many times i have run it, all the data in this table is still (1,2) which i though should be many (1,2). So i was wondering what's the reason for that? because to me it does't make any difference from "insert" or "insert or replace".

EDIT:

with open(file, 'rb') as f:
        entries = csv.reader(f,delimiter='\t')
        rows= list(entries)
    ###    
    # For first point (A) three strain values and temperature
    ###

    ###use numpy to turn the csv files into a matrix  
        matrix= numpy.array([])
        for i in range (1,5) :   #in the setted files using len(list(rows)) to make it read all the coloumn and create a new table for each different coloumn
            a=list(rows[3])
            result = [0,0]
    # the program will detect the number and name for each coloumn then show add new table untill there's no more coloumn left 
            for r in range(5,len(rows)):
                data=list(rows[r])
                c.execute("create table if not exists " + str(a[i]) + file[0:4] + file[4:6] + " (time real, data real)") # NEED TO CHANGE TABLE NAME FROM A TO B OR C OR ETC
                matrix=numpy.append(matrix,float(data[0]))
                matrix=numpy.append(matrix,float(data[i]))
                result= numpy.vstack((result,matrix))
                matrix=[]
    # create a sqlite table named by its point in this case is A with different name to the coloumn
                result = numpy.delete(result, (0), axis=0)
                for item in result:
                    c.execute("insert into " + str(a[i]) + file[0:4] + file[4:6] +  " values (?,?)", item)

Upvotes: 2

Views: 219

Answers (1)

Malachi
Malachi

Reputation: 3221

Like @CL was saying you can't have 2 records with the same PRIMARY KEY

this Table should be built with 3 columns.

  1. A_ID PRIMARY KEY AUTO INCREMENT NOT NULL
  2. time DATETIME (probably NOT NULL as well
  3. data REAL

in other words, Never set the Primary key in your code, let the database auto increment it for you, or use a GUID.

this line 

data=(1,2)

is the only thing that is being inserted into your database. so the only record in the database will be the one you insert, which is 1,2

Upvotes: 1

Related Questions