Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>It looks like the first field in your MySQL table is a <strong>unique</strong> timestamp. It is possible to set up the MySQL table so that the field must be unique, and to ignore <code>INSERT</code>s that would violate that uniqueness property. At a <code>mysql&gt;</code> prompt enter the command:</p> <pre><code>ALTER IGNORE TABLE heating ADD UNIQUE heatingidx (thedate, thetime) </code></pre> <p>(Change <code>thedate</code> and <code>thetime</code> to the names of the columns holding the date and time.)</p> <hr> <p>Once you make this change to your database, you only need to change one line in your program to make MySQL ignore duplicate insertions:</p> <pre><code>cursor.execute('INSERT IGNORE INTO `heating` VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,)', row) </code></pre> <hr> <p>Yes, it is a little wasteful to be running <code>INSERT IGNORE ...</code> on lines that have already been processed, but given the frequency of your data (every 6 minutes?), it is not going to matter much in terms of performance.</p> <p>The advantage to doing it this way is that it is now impossible to accidentally insert duplicates into your table. It also keeps the logic of your program simple and easy to read. </p> <p>It also avoids having two programs write to the same CSV file at the same time. Even if your program <em>usually</em> succeeds without error, every so often -- maybe once in a blue moon -- your program and the other program may try to write to the file at the same time, which could result in an error or mangled data.</p> <hr> <p>You can also make your program a little faster by using <code>cursor.executemany</code> instead of <code>cursor.execute</code>:</p> <pre><code>rows = list(csv_data) cursor.executemany('''INSERT IGNORE INTO `heating` VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,)''', rows) </code></pre> <p>is equivalent to</p> <pre><code>for row in csv_data: cursor.execute('INSERT INTO `heating` VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,)', row) </code></pre> <p>except that it packs all the data into one command.</p>
    singulars
    1. This table or related slice is empty.
    1. POPython Read a CSV and place values in MySQL Database
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USunutbu
    1. USunutbu
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI like that but the date and time are two separate Fields ???
      singulars
      1. PO
      1. USZeroG
    2. CO@ZeroG: That's no problem. Just list all the fields needed to define a unique row. I've edited the post above to show what I mean.
      singulars
      1. PO
      1. USunutbu
    3. COwill this take into account that date and time will need to be different i.e. in 2 days there are 2 14:00's even though dates will be different?
      singulars
      1. PO
      1. USZeroG
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload