How to correctly insert integers sqlite3 python

Question

I need to insert rows from a excel file into a sqlite3 database i created ; so far I managed i convert the excel into a dataframe I create the database , the table i wanted with the fields , i used a for loop to get my rows in the table through a "insert into tablename values (?..,?)" , (value1,...valuen) however only the date who got the text type is is clearly visible into the database , all the integers are passed into the database as bytes and even an int.from_bytes() don't get me my integers under the right form.. so if anyone can help

devices = df['id_device']
time = df['utc_datetime']
vote_yes = df['yes']
vote_neutre = df['neutre']
vote_no = df['no']

questions = ['question']*len(df)
kpi = ['KPI']*len(df)
id_status = [None]*len(df)
indexing = [index  for index in range(len(df))]
base = list(map(lambda l,t,x,y,z,k,status , quest , index : [l,t.to_datetime(),x,y,z , k , status , quest , index] , devices , time , vote_yes , vote_neutre , vote_no , kpi , id_status , questions , indexing ))

base = [[507, datetime.datetime(2016, 8, 1, 11, 10, 30), 1, 0, 0, 'KPI', None, 'question', 0],
[507, datetime.datetime(2016, 8, 1, 11, 40, 33), 2, 0, 0, 'KPI', None, 'question', 1], 
[507, datetime.datetime(2016, 8, 1, 12, 10, 39), 5, 3, 1, 'KPI', None, 'question', 2], 
[507, datetime.datetime(2016, 8, 1, 13, 10, 43), 1, 0, 0, 'KPI', None, 'question', 3], 
[507, datetime.datetime(2016, 8, 1, 14, 40, 43), 2, 1, 0, 'KPI', None, 'question', 4], 
[507, datetime.datetime(2016, 8, 1, 15, 10, 47), 2, 0, 0, 'KPI', None, 'question', 5], 
 [507, datetime.datetime(2016, 8, 1, 16, 10, 47), 2, 0, 0, 'KPI', None, 'question', 6],
[507, datetime.datetime(2016, 8, 1, 16, 40, 51), 2, 1, 0, 'KPI', None, 'question', 7], 
[507, datetime.datetime(2016, 8, 1, 17, 10, 56), 1, 2, 0, 'KPI', None, 'question', 8], 
[507, datetime.datetime(2016, 8, 1, 17, 40, 57), 1, 0, 0, 'KPI', None, 'question', 9]]


cur = conn.cursor()


cur.execute('''create table if not exists coord4 (device int , time text)''')

for line in base:

    cur.execute('''insert into coord4 values (?,?)''', (line[0], line[1]))
conn.commit()


res = cur.execute('select * from coord4')
print(res.fetchone())
 #output
(b'\xfb\x01\x00\x00\x00\x00\x00\x00', '2016-08-01 11:10:30')

this is my code if you need..

Works for me. Check that you do not have an older table definition in the database, and show how you configured the connection. — CL.
– CL., Commented Jul 3, 2017 at 7:04
I got my answer , the solution was : for line in base: cur.execute('''insert into coord4 values (?,?)''', (line[0], line[1])) conn.commit() — yikkin ying yang
– yikkin ying yang, Commented Jul 3, 2017 at 7:54
That looks exactly the same as the code in the question. Anyway, if you have an answer, please write is as an answer below. — CL.
– CL., Commented Jul 3, 2017 at 9:27

yikkin ying yang · Accepted Answer · 2017-07-03 09:41:19Z

2

The solution I was looking for was :

for line in base:

    cur.execute('''insert into coord4 values (?,?)''', (int(line[0]), line[1]))

conn.commit()

answered Jul 3, 2017 at 9:41

yikkin ying yang

811 silver badge5 bronze badges

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

1 Comment

manoelpqueiroz Over a year ago

I had the same problem, now curious to why Python has to convert something that's technically already an integer.

Collectives™ on Stack Overflow

How to correctly insert integers sqlite3 python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related