python sqlite3, inserting row into table using data from another table

Question

There are many eaxmples of this, but in all cases that I saw they knew the names of the fields(columns). The two tables have exactly the same column/fields.

My solution has solved my current problem but as you can see from the code, it might qualify for the 'most ridiculous code of the year' award.

# Copy data from one table to another in the same database
print '-' * 70
print 'Copy data from one table to another in the same database\n'
print '  Usefull for creating test data.'
print '-' * 70

import sqlite3

connection = sqlite3.connect("table.sqlite")
cursor = connection.cursor()

source_table = 'table1'
target_table = 'test_table1'

stmt = "SELECT * FROM %s" % source_table

cursor.execute(stmt)
data = cursor.fetchall()

for row in data:
    stmt = "insert into %s values " % target_table + str(row)
    stmt = stmt.replace("u'", '"')
    stmt = stmt.replace("'", '"')
    stmt = stmt.replace(' None', ' Null')
    cursor.execute(stmt)
    connection.commit()

connection.close()

There must be a better (more reliable) way to do this.

falsetru · Accepted Answer · 2013-09-29 08:18:51Z

1

Use cursor.executemany:

import sqlite3

connection = sqlite3.connect("table.sqlite")
cursor = connection.cursor()

source_table = 'table1'
target_table = 'test_table1'

stmt = "SELECT * FROM %s" % source_table

cursor.execute(stmt)
data = cursor.fetchall()

fields = ','.join('?' for desc in cursor.description)
stmt = "insert into {} values ({})".format(target_table, fields)
cursor.executemany(stmt, data)
connection.commit()
connection.close()

Used cursor.description to get column data.

NOTE

parameter marks vary according to database module. sqlite3 module use qmark(?). You should check it if you use another database module.

answered Sep 29, 2013 at 8:18

falsetru

371k69 gold badges769 silver badges659 bronze badges

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

Collectives™ on Stack Overflow

python sqlite3, inserting row into table using data from another table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related