Help with script placeholders.

[anaxcontrol]

I would like to replace the %s to the new format {}. I read quite a bit about it but not getting anywhere. I would also like to use a data placeholder as well. This scripts takes Arduino serial data and inserts to MySQL. Note that this is an example but my script is the same but with populated ip, username, data fields etc.

I'm finding that the data inserts to the wrong column at times so changing from strings to data may solve this but hopefully someone with much more python experience can provide me some guidance.

import time
import serial
import smtplib
import datetime
import MySQLdb


db = MySQLdb.connect(host="xxx.xx.xx.xx", 
                     port=3306,
                     user="xxx", # username
                      passwd="xxx", # password
                      db="xxxx") # name of the database

cur = db.cursor()


while True:
        ser = serial.Serial('/dev/ttyACM0', 115200)
        windspeed = ser.readline()
        winddirection = ser.readline()
        temperature = ser.readline()
        pressure = ser.readline()
        humidity = ser.readline()
        rainfall = ser.readline()
        current_time = datetime.datetime.now()
        unixtime = long(time.time())
        #print(unixtime)
        #print datetime.datetime.now()
        #print(windspeed)
        #print(winddirection)
        #print(temperature)
        #print(pressure)
        #print(humidity)
        #print(rainfall)

        sql = "INSERT INTO `xxx`.`xxxx` (`ID`, `tdate`, `ttime`, `WindSpeed`,`WindDirection`, `Temperature`, `Pressure`, `Humidity`, `Rainfall`, `UNIXTIME`) VALUES (NULL, CURDATE(), %s, %s, %s, %s, %s, %s, %s, %s)"
        data = (current_time , windspeed, winddirection, temperature, pressure, humidity, rainfall, unixtime)
        cur.execute(sql,(data))
        db.commit()

db.close()
print('end')

[sparkz_alot]

The new method of formatting, format() (note the parenthesis, not braces) is much more powerful and easier to use (IMHO) than the previous method. Actually, the Python doc's are pretty descriptive.  Basically, you can put just about anything in the format section as well as within the braces of the print statement. Some examples:

>>> x = 1
>>> y = 2
>>> print("{}, {}".format(x, y))
1, 2

>>> print("{1}, {0}".format(x, y))
2, 1
>>>

>>> print("{} / {} = {}".format(x, y, x / y))
1 / 2 = 0.5
>>>

>>> z = 1.333333333
>>> print("{:.2f}".format(z))
1.33
>>>

As you can see, you can do just about anything you want, well, within reason. If you are unsure, try it and see, the worst that will happen is you'll get an error :-)

[anaxcontrol]

Thank you for that response sparkz_alot. As I'm sure you surmised, I know virtually nothing about Python but learning. I did read over the Old format vs. new format and it seemed straight forward but all the variations I tried just state some error (like "should be data, not a string" or something along those lines). The problem I have is how I take the code from above:

[inline] sql = "INSERT INTO xxx.xxxx (ID, tdate, ttime, WindSpeed,WindDirection, Temperature, Pressure, Humidity, Rainfall, UNIXTIME) VALUES (NULL, CURDATE(), %s, %s, %s, %s, %s, %s, %s, %s)"
data = (current_time , windspeed, winddirection, temperature, pressure, humidity, rainfall, unixtime)
"[/inline]

And convert to new format. The SQL insert section is clear to me (db, table and column names that they go into). But the next part (VALUES; which are the place holders that need the new format) and data=(which are my data field names). I'm not drawing a correlation on how to change this. Note that my current script works with the old format but I'm getting data populated in the wrong columns so I'm assuming its because these are all strings instead of data but not sure. In either case, I want to scratch this possibility off then work from there. It could also be a timing issue. So, should the new format be something like:

VALUES(NULL,CURDATE(),"{:.2f},{},{:.2f},{:.2f},{:.2f},{:.2f},{:.2f},{}".format(current_time, windspeed, winddirection, temperature, pressure, humidity, rainfall, unixtime))"

Or am I off by a mile?

[nilamo]

Does the database driver support that method of formatting strings? That's really the only important question, because if it doesn't, you won't use it. You definitely want to use whatever works with the db driver, since it handles things like escaping your data and sanitizing it for you.

[anaxcontrol]

nilamo-Im not sure if the db supports it or not. The db is MySQL. The original code I posted works though so I just need the python method of formatting. I'm not versed in python at all. in fact, I just need it to do this one function. The rest is C++ which I'm ok with.

[nilamo]

String constant stating the type of parameter marker formatting expected by the interface. Set to 'format' = ANSI C printf format codes, e.g. '...WHERE name=%s'. If a mapping object is used for conn.execute(), then the interface actually uses 'pyformat' = Python extended format codes, e.g. '...WHERE name=%(name)s'. However, the API does not presently allow the specification of more than one style in paramstyle.

Which matches MySQL's docs:

add_employee = ("INSERT INTO employees "
              "(first_name, last_name, hire_date, gender, birth_date) "
              "VALUES (%s, %s, %s, %s, %s)")
add_salary = ("INSERT INTO salaries "
             "(emp_no, salary, from_date, to_date) "
             "VALUES (%(emp_no)s, %(salary)s, %(from_date)s, %(to_date)s)")

data_employee = ('Geert', 'Vanderkelen', tomorrow, 'M', date(1977, 6, 14))

# Insert new employee
cursor.execute(add_employee, data_employee)

Which is to say, unless you try it and it works, then you probably can't use the newer format strings, but you can use a data dict and format your values that way.

[sparkz_alot]

I agree with @nilamo, if what you currently have works, stick with it for now. Btw, either method is going to give you strings. I'm not a database guy, but I would imagine as long as the cells are set for 'strings' you should be o.k. as far as that goes.

One thing I noticed is this unixtime = long(time.time()) , this will get you:

Error:
>>> unixtime = long(time.time))
 File "<stdin>", line 1
   unixtime = long(time.time))
                             ^
SyntaxError: invalid syntax
>>> unixtime = long(time.time())
Traceback (most recent call last):
 File "<stdin>", line 1, in <module>
NameError: name 'long' is not defined

Did you just mean:

>>> unixtime = time.time()
>>> unixtime
1499978309.6422074
>>> type(unixtime)
<class 'float'>
>>>

Yes, 115200 bps is extremely fast. Possibly lower to maybe 9600 bps for testing? Not sure how you're handling lost data or buffer overruns, etc.

[anaxcontrol]

Keep in mind that my script is different- the uploaded was an example. I dropped mine down to 9600 from 57600 and im not having any more issues. I'll leave it in the old format and call it good. Also on my script I don't have unixtime so no issue there. I just didn't upload it because it would need sanitizing and I didn't want to risk a spillage.

Last question, but on the fringe of the original q scope. I do not have any handling of lost data or buffer overruns, etc. if I could get a point in the right direction, I could figure it out. I'll add that then I think I'm good to go.

[sparkz_alot]

Not sure how involved you want to get or what options are available for the Arduino. A good place to look for serial communications information is here: Serial HOWTO. I also found this tutorial(?) on changing the buffer size: Serial buffer size.

If you are getting consistant results at 9600 bps, you could try 'upping' the speed (stick with the standard speeds) until you start getting failures, at which point you could try increasing the buffer size or dropping the speed back down.

Of course the best way would be to find out which UART chip your particular version uses and then try to search for the spec sheets.

[anaxcontrol]

sparkz_alot- this is perfect. I actually found a println statement that was causing all my problems. I removed it and now received 500 rows so far with no errors at 57600. I tried 115 but got some errors so backed it off. I'm going to give the buffer increase mod a shot then I think I'm golden. Thank you all very much for the assistance! I'd say this problem is solved.