How can I do 'Create Table' and 'Input Values' with Arrays?

Question

I'm looking for an answer to the following question:
Is it possible to 'Create a Table' and to 'Input Values' with various Arrays?

My Problem is, that I have a large amount of values which I'd like to insert into the table. Because of that it's not really practical for me, to do the standard 'CreateTable' stuff like c.execute('''CREATE TABLE calc_matrix (date text, trans text, symbol text, qty real, price real)''')

I have the following Arrays which I like to insert:
1. Array: column_name [200] = [ColName1 string, ColName2 string, ...., ColName200 string]
2. Array: Result_1 [200] = [ Value1 int, Value2 double, Date date, Value3 float, ...., Value185 int, Value 186 float]
3. Array: Result_2 [200] = [ Value1 int, Value2 double, Date date, Value3 float, ...., Value185 int, Value 186 float]
4. Array: Result_3 [200] = [ Value1 int, Value2 double, Date date, Value3 float, ...., Value185 int, Value 186 float]

Therefore I don't know how to accomplish that.

I've tried things like:
c.execute('''CREATE TABLE calc_matrix %s''', column_name)
Or in my loop:
c.execute('''CREATE TABLE calc_matrix ?''', column_name[i])

but nothing worked ......

You shouldn't. The best solution is to normalize your schema by an index column and have one row per array element. — Colonel Thirty Two
– Colonel Thirty Two, Commented Sep 9, 2015 at 16:43

David Rajcok · Accepted Answer · 2015-09-09 17:06:45Z

0

Try to create the full SQL command in a string first and then pass the string into c.execute

string = "CREATE TABLE calc_matrix ( "
your for loop
    string += column_name[i] + ", "

string += ")"
c.execute(string)

Your for loop wasn't working before because your program was trying to execute many CREATE TABLE commands that all had the same table name calc_matrix. You need the entire command in one c.execute statement.

The same pattern can be used for inserting many rows into a table.

string = "INSERT INTO calc_matrix VALUES ("
your for loop
    string += result_1[i] + ", "

string += ")"
c.execute(string)

answered Sep 9, 2015 at 17:06

David Rajcok

1961 silver badge5 bronze badges

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

2 Comments

DieStra Over a year ago

Many Thanx !!! That worked ! But u also have to do string += str(result_1[i]) + ", " and u also have consider some other minorities like add Datatype in the String or no , before ) ! But that's awesome, Thanx !

David Rajcok Over a year ago

Right, converting to string is needed first, and then some sort of flag is needed to add in the comma for all but the last element in the array. I'm glad you got it to work.

Collectives™ on Stack Overflow

How can I do 'Create Table' and 'Input Values' with Arrays?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related