CODEWITHSUNDEEP
pythonmysqlpython-3.x
Jonathan Cheng
Jonathan Cheng

Reputation: 479

Pymysql insert foreign key error

My table weather

create table weather (
            time timestamp default current_timestamp on update current_timestamp,
            id smallint,
            tpr float(3,1),
            wet float(3,1),
            uv tinyint(2),
            foreign key (id) references database.station(pk));

Table station

CREATE TABLE station(
pk SMALLINT(5) NOT NULL AUTO_INCREMENT,
name VARCHAR(5),
lng DOUBLE(10,6),
lat DOUBLE(10,6),
PRIMARY KEY(pk));

When I use the pymysql to insert the id to the weather.

I made the two functions below:

conn = pymysql.connect()#ignore the details
def get_id_from_station():
    sql = """SELECT pk FROM  station """
    conn.cursor().execute(sql)
    id = conn.cursor().fetchone()[0]
    weather_saved(id)

def weather_saved(id):
    #get the time,tpr,wet,uv value
    sql = """INSERT INTO weather (time,id,tpr,wet,uv) VALUES (%s,%s,%s,%s,%s)"""
    db.execute(sql, (time, id,tpr, wet, uv))

But the weather table didn't update.

What's the wrong?

Upvotes: 0

Views: 979

Answers (1)

Niklas Rosencrantz
Niklas Rosencrantz

Reputation: 26643

Here is code that updates the weather table. I used python 2.7 but it should be the same. I think your problem might be that you didn't commit the insert. 

import pymysql

conn = pymysql.connect(...)

def get_id_from_station():
    sql = """SELECT pk FROM  station """

    cursor = conn.cursor()
    cursor.execute(sql)
    row = cursor.fetchone()
    weather_saved(row)

import time
import datetime

def weather_saved(id):
    #get the time,tpr,wet,uv value
    sql = """INSERT INTO weather (time,id,tpr,wet,uv) VALUES (%s,%s,%s,%s,%s)"""
    cursor = conn.cursor()
    ts = time.time()
    timestamp = datetime.datetime.fromtimestamp(ts).strftime('%Y-%m-%d %H:%M:%S')
    cursor.execute(sql, (timestamp, id,7, 7, 7))
    conn.commit()

get_id_from_station()

Upvotes: 1

Related Questions