Python 2.7 MySQLdb to save last row read from database into text file
<p>So bare with me, I have been doing python for a little over a week now. I have been tasked with getting a MySQL database to send data into Salesforce and create a ticket. I currently have python downloading the required tables from the database and saving that information into a .xlsx sheet. Then I have another python script uploading that data to a Google Doc sheet and then a Javascript trigger that will submit the data into salesforce and all works as I expected. </p>

<p>Issue I am having is that when it pulls the data from MySQL, it pulls the same info again and again, I need it to remember the last row or customer ID that it grabbed the last time around, save that ID or Row into a .txt file then pull that same data the next time the script runs so it starts from that row or ID instead of starting from the top again.</p>

<p>Any help would be greatly appreciated.</p>

<pre><code>#!/usr/bin/python
import smtplib
import base64
import os
import sys
import csv
from xlsxwriter.workbook import Workbook
import xlwt
import MySQLdb
def sql_run():
ur = 'user' # your username
pd = 'password' # your password
ht = '192.168.1.10' # your host
pt = 3306
db = 'database' # database where your table is stored
#table = 'rma_customer' # table you want to save

con = MySQLdb.connect(user=ur, passwd=pd, host=ht, port=pt, db=db)
cursor = con.cursor()

query = "SELECT cus_id, cus_username FROM database.rma_customer"
query0 = "SELECT cus_firstname, cus_lastname, cus_cell_phone FROM database.rma_customer_details"
query1 = "SELECT cus_address_1 FROM database.rma_customer_address"
query2 = "SELECT cus_city, cus_postcode FROM database.rma_customer_address"
query3 = "SELECT regpro_model_type, regpro_buy_location, regpro_sn FROM database.rma_reg_product"
cursor.execute(query)
workbook = Workbook('outfile.xlsx')
sheet = workbook.add_worksheet("Warranty Info")
headers = ['ID', 'Email', 'FirstName', 'LastName', 'Phone', 'Street', 'City', 'Postal', 'Product', 'Store', 'SN']
for i, header in enumerate(headers):
    sheet.write(0, i, header)
for r, row in enumerate(cursor.fetchall()):
    r = r + 1
    for c, col in enumerate(row):

        sheet.write(r, c, col)

cursor.execute(query0)  
for r, row in enumerate(cursor.fetchall()):
    r = r + 1
    for c, col in enumerate(row):

        sheet.write(r, c+2, col)        

cursor.execute(query1)  
for r, row in enumerate(cursor.fetchall()):
    r = r + 1
    for c, col in enumerate(row):

        sheet.write(r, c+5, col)

cursor.execute(query2)  
for r, row in enumerate(cursor.fetchall()):
    r = r + 1
    for c, col in enumerate(row):

        sheet.write(r, c+6, col)

cursor.execute(query3)  
for r, row in enumerate(cursor.fetchall()):
    r = r + 1
    for c, col in enumerate(row):

        sheet.write(r, c+8, col)

cursor.close ()
workbook.close()
con.close ()
sys.exit()
# exit the program



sql_run()
</code></pre>

<ol>
<li>Add a <code>row</code> in the <code>table</code>you pull your information from.  </li>
<li>Pull the <code>id</code> (0th's row) as unique identifier</li>
<li>Update <code>row</code> which has <code>id</code> with something like <code>True</code> or <code>False</code>.</li>
<li>Give your query a <code>condition</code> that said row must be <code>True</code>/ <code>False</code></li>
</ol>

<p>This way you make sure it stops iterating over the same info.</p>

<p>Programatically:</p>

<pre><code>query = "SELECT cus_id, cus_username FROM database.rma_customer WHERE row = 'True'"
...

data = cursor.execute(query)
for col in data:
    id = col[0]
...
cursor.execute("""
    UPDATE table
    SET row= %s
    WHERE id= %s
""", (row, id))
</code></pre>

<p>The <code>...</code> stand for your code. I cannot guess where you want to place the code snippets.</p>

python mysql excel python-2.7