2

I have an online MySQL (presto) database which keeps on auto-updating (i.e., new rows keep getting added to the tables(views, to be exact) every day). I want to fetch the data from it every day but don't want to refetch the data that I have already fetched before.

I am using python3 for extracting the data from the online host database as:

from pyhive import presto
import numpy as np
cursor=presto.connect().cursor()
sql = 'select * from schema.table'
cursor.execute(sql)
rows = np.array(cursor.fetchall())
cursor.close()

Doing this takes time and fetches all the data (new data as well as already fetched data). I am storing the data in rows variable in a csv file afterward.

Is there any way to get only the new data (id is the primary key in the table)?

Thanks in advance.

  • There is no any mechanism stored the info about the record was fetched. You are to create it. You'll need to alter Your table structure and add any field which allows You to determine have You fetched any record or not. It can be the timestamp field stored the timepoint of create/update current record (those update can be performed automatically while set proper field attributes). It can be simple flag field which is (directly, by separate update query) set to some value when You fetch the record and set to another value when the record is created or altered. – Akina Jun 5 '18 at 6:06
  • @Akina I only have viewing (and extracting/downloading) rights for the database, I cannot alter the tables(or views) once they are created. They can auto-update if they are views but manually updating them to create a new column is not possible. Is there any other way? – Devanshu Somani Jun 5 '18 at 6:09
  • The only solution I see is to create addtitonal table stored the info (record IDs for example) about the records You have already fetched. If so I recommend to create stored procedure which selects the records from main table not listed in 'already viewed' table into temporary table, adds their IDs to the fetched records list, and outputs them to You. If the ID in main table is autoincrement You can store not the list of records fetched but only the greatest ID - it will simplify the process. – Akina Jun 5 '18 at 6:21
  • The table does not keep track of what was "already fetched"; you must have a column (auto_increment, datetime, has_been_seen, etc) that, with help from your code, provides that info. – Rick James Jun 8 '18 at 1:26
1

Assuming id is an incrementing number or something else which grows every time you insert a new row (such as a timestamp), this should be quite straightforward:

First read the last id value in the csv file from the previous run, and then use this in your query line:

sql = 'SELECT * FROM schema.table WHERE id > %d ORDER BY id ASC' % id

UPDATE:

If id is not an incrementing key, e.g. it's a UUID, then this becomes much harder. Other DBMSes (e.g. Oracle) have rowid which could probably have been used here, though that is not a MySQL feature. MariaDB has rowid in its CONNECT storage engine, so you could set up a local MariaDB instance, install the CONNECT engine, create a CONNECT table connected to your remote MySQL table schema.table with the special rowid column using a statement such as:

CREATE TABLE connect_table (
 col1 float,
 col2 datetime,
 col3 varchar(20),
 row_id int special=rowid
)
ENGINE=CONNECT TABLE_TYPE=MYSQL DBNAME=mytest TABNAME=table
CONNECTION='mysql://root:password@localhost'; 
  • I am sorry but that is a specific solution. In my case, the id isn't an incrementing number. What I am asking is that if there is a function or a method of doing it in a general case? – Devanshu Somani Jun 6 '18 at 4:51
  • @DevanshuSomani I've updated my answer. I think most often tables will have a primary key that is incrementing, but occasionally this is not the case, such as UUID keys. – dbdemon Jun 7 '18 at 9:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.