Hello,
I'm trying to find the best method to create new tables in SQLite derived
from a select statement on an existing SQLite table. I've currently tried
the two methods, one in Python and one in the SQLite command shell (both
illustrated below). Although I generally prefer to code in Python, I find
it much slower. I also find the SQLite command line much slower when I use
the INSERT INTO ... SELECT query. What are best coding practices in
building derived SQLite tables?
Python example:
conn = sqlite3.connect("example.db')
c = conn.cursor()
query = '''
CREATE TABLE clients (
cust_id int,
year int,
clerk_id int,
sales decimal(30,4)
PRIMARY KEY (cust_id, year)
);'''
c.execute(query)
query = '''
ATTACH '%s' AS panel;''' % ( 'panel.db' )
c.execute(query)
query = '''
INSERT INTO clients (
cust_id,
year,
clerk_id,
sales)
SELECT
cust_id,
year,
clerk_id,
sales
FROM panel.panel
WHERE sales >= 30000;''
c.execute(query)
conn.commit()
SQLite command shell example:
ATTACH 'panel.db' AS panel;
.output ../temp/clients.txt
SELECT
cust_id,
year,
clerk_id,
sales
FROM panel.panel
WHERE sales >= 30000;
CREATE TABLE clients (
cust_id int,
year int,
clerk_id int,
sales decimal(30,4)
PRIMARY KEY (cust_id, year)
);
.import ../temp/clients.txt clients
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
