Hi,
I append rows to a table in MySQL with sqlSave from RODBC package. The data
frame that is saved is ordered by date ascending (first column) as is the
table in the DB. When I send the whole data frame at once, the data frame is
appended in reverse order. That means the row with the newest date in the
data frame is the first row appended to the table, while the row with oldest
date will be last row in the table. OK, I thought that "append" just orders
by date descending and that is why the reverse ordering happens. But when I
send each row of the data frame using a for-loop the result is the same: The
first row send is the last row in the table, the second row send, is the
second last row in the table.
My solution is to run the for loop from last to first row of the data frame
but I am looking for an explanation why append works like this and if it's
possible to change this in some setting.
#Send data frame completely (6 rows, 12 cols)
sqlSave(con, df, tablename = table, rownames=FALSE, addPK=TRUE, append=TRUE,
verbose=TRUE)
# Send data frame row by row
for (a in 1:6) {
dfsend <- df[a,]
sqlSave(con, dfsend, tablename = table, rownames=FALSE, addPK=TRUE,
append=TRUE, verbose=TRUE)
}
# "solution": Send data frame row by row, starting from last row of data
frame
for (a in 6:1) {
dfsend <- df[a,]
sqlSave(con, dfsend, tablename = table, rownames=FALSE, addPK=TRUE,
append=TRUE, verbose=TRUE)
}
Thank you
Chris
RODBC sqlSave appends rows from data frame in reverse order
4 messages · Enrico Schumann, Brian Ripley, Chris
On Wed, 20 Nov 2013, "Chris" <cs at incentive-investing.com> writes:
Hi, I append rows to a table in MySQL with sqlSave from RODBC package. The data frame that is saved is ordered by date ascending (first column) as is the table in the DB. When I send the whole data frame at once, the data frame is appended in reverse order. That means the row with the newest date in the data frame is the first row appended to the table, while the row with oldest date will be last row in the table. OK, I thought that "append" just orders by date descending and that is why the reverse ordering happens. But when I send each row of the data frame using a for-loop the result is the same: The first row send is the last row in the table, the second row send, is the second last row in the table. My solution is to run the for loop from last to first row of the data frame but I am looking for an explanation why append works like this and if it's possible to change this in some setting.
In MySQL, you never have a guarantee in which order the rows will be retrieved unless you specify it (eg, "SELECT * FROM table ORDER BY column;"). [...]
Thank you Chris
Enrico Schumann Lucerne, Switzerland http://enricoschumann.net
On 20/11/2013 16:34, Enrico Schumann wrote:
On Wed, 20 Nov 2013, "Chris" <cs at incentive-investing.com> writes:
Hi, I append rows to a table in MySQL with sqlSave from RODBC package. The data frame that is saved is ordered by date ascending (first column) as is the table in the DB. When I send the whole data frame at once, the data frame is appended in reverse order. That means the row with the newest date in the data frame is the first row appended to the table, while the row with oldest date will be last row in the table. OK, I thought that "append" just orders by date descending and that is why the reverse ordering happens. But when I send each row of the data frame using a for-loop the result is the same: The first row send is the last row in the table, the second row send, is the second last row in the table. My solution is to run the for loop from last to first row of the data frame but I am looking for an explanation why append works like this and if it's possible to change this in some setting.
In MySQL, you never have a guarantee in which order the rows will be retrieved unless you specify it (eg, "SELECT * FROM table ORDER BY column;").
Nor any other RDBMS: as the RODBC manual does say (on p.6).
[...]
Thank you Chris
Brian D. Ripley, ripley at stats.ox.ac.uk Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ University of Oxford, Tel: +44 1865 272861 (self) 1 South Parks Road, +44 1865 272866 (PA) Oxford OX1 3TG, UK Fax: +44 1865 272595
Great, thank you all. I will always add "order by" when fetching data into da data frame and assume the rows in MySQL might not always be in right order -----Urspr?ngliche Nachricht----- Von: Prof Brian Ripley [mailto:ripley at stats.ox.ac.uk] Gesendet: Mittwoch, 20. November 2013 18:19 An: Enrico Schumann; Chris Cc: r-sig-db at r-project.org Betreff: Re: [R-sig-DB] RODBC sqlSave appends rows from data frame in reverse order
On 20/11/2013 16:34, Enrico Schumann wrote:
On Wed, 20 Nov 2013, "Chris" <cs at incentive-investing.com> writes:
Hi, I append rows to a table in MySQL with sqlSave from RODBC package. The data frame that is saved is ordered by date ascending (first column) as is the table in the DB. When I send the whole data frame at once, the data frame is appended in reverse order. That means the row with the newest date in the data frame is the first row appended to the table, while the row with oldest date will be last row in the table. OK, I thought that "append" just orders by date descending and that is why the reverse ordering happens. But when I send each row of the data frame using a for-loop the result is the same: The first row send is the last row in the table, the second row send, is the second
last row in the table.
My solution is to run the for loop from last to first row of the data frame but I am looking for an explanation why append works like this and if it's possible to change this in some setting.
In MySQL, you never have a guarantee in which order the rows will be retrieved unless you specify it (eg, "SELECT * FROM table ORDER BY column;").
Nor any other RDBMS: as the RODBC manual does say (on p.6).
[...]
Thank you Chris
Brian D. Ripley, ripley at stats.ox.ac.uk Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ University of Oxford, Tel: +44 1865 272861 (self) 1 South Parks Road, +44 1865 272866 (PA) Oxford OX1 3TG, UK Fax: +44 1865 272595