Insert & Where mysql statement in Python

Question

I've got a big database I am trying to split it, this big database has a column where each row is either bid or buy it now. I want to split up this table into two tables, I was thinking of doing a SQL statement such as INSERT INTO newtable WHERE row[5] = 'bid' for example. But after some research I found out there is not where statement in an insert statement. Is that true? IF not how can I split up this database? (Using python2 btw)

score 1 · Accepted Answer · edited May 23 '17 at 12:20

1

Why don't you SELECT each group first and then loop through the results and INSERT them?

As per our conversation in chat you could do something like this:

all = "SELECT * FROM listings1 WHERE auctionType IN ('Bid', 'BuyNow')" 
cursor.execute(all)
results = cursor.fetchall()
for row in results:
    if row['auctionType'] == 'Bid':
      #do your insert here
      insert = "INSERT INTO listings1 () VALUES (domainName, itemID)" % (row['domainName'], row['itemID'] etc etc)
      cursor.execute(insert)
    elif row['auctionType'] == 'BuyNow':
      #other insert here

Also note that your mysql library matters. You want the results returned as a dict. See this answer for more details: https://stackoverflow.com/a/8623947/1086938

edited May 23 '17 at 12:20

Community

1
1

answered May 20 '14 at 15:45

hanleyhansen

6,304
8
37
73

That seems like it would work, BUT this row is from the "bigtable" So I need to define row beforehand and have no idea how. – Coder77 May 20 '14 at 16:20
@Coder77 See edit. Something like that should work. – hanleyhansen May 20 '14 at 16:23
What is row[5] supposed to be? – Coder77 May 20 '14 at 16:26
It's your variable from your question. I don't understand what the problem is. Please rephrase. – hanleyhansen May 20 '14 at 16:27
I get this error when using your edited code `IndexError: string index out of range` – Coder77 May 20 '14 at 16:30
My code wasn't meant to be copied and pasted. It's intent is to give you an idea of how to accomplish what you're trying to do. – hanleyhansen May 20 '14 at 16:31
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/54057/discussion-between-coder77-and-hanleyhansen). – Coder77 May 20 '14 at 16:33

score 1 · Answer 2 · answered May 20 '14 at 15:45

1

Insert statement has now where clause, but there is INSERT INTO SELECT statement: INSERT INTO newtable (...) SELECT ... FROM oldtable WHERE column = 'bid'

answered May 20 '14 at 15:45

Alex Shkop

1,992
12
12

Unsure what I'd put in the '..' bits. – Coder77 May 20 '14 at 16:23
List of column names: `INSERT INTO newtable (column1, column2, column3) SELECT column1, column2, column3 FROM oldtable WHERE column = 'bid';`. Note that the number of columns in `INSERT` and `SELECT` should be equal. – Alex Shkop May 21 '14 at 06:07

score 0 · Answer 3 · answered May 20 '14 at 15:51

Not sure if maybe I am misunderstanding the question... but you cannot use WHERE with an INSERT. WHERE is just used to help specify which rows you want to grab or alter in a query.

If what you want to do is grab all the entries in the table that have X value for a particular column (like 'bid' for row[5]) to then insert into their own table elsewhere, you can do a loop on a SELECT statement to grab all of those values with a WHERE row[5]='bid'. (SELECT * FROM currenttable WHERE row[5]='bid').

In a while loop, you can then INSERT INTO newtable (column,column,column) VALUES (value,value,value) - replacing the column/value for all the columns/values for that table.

Once finished, and confirmed that the new table has the appropriate values, you can remove/delete all the unnecessary entries from your original table (DELETE FROM currenttable WHERE row[5]='bid') and thus you have split your table.

Insert & Where mysql statement in Python

3 Answers3