I'm trying to save an object into a mysql table. I created a database with a table, in this table there's a text column.
my actual code is
conn = MySQLdb.connect(host='localhost', user='root', passwd='password',db='database')
x = conn.cursor()
x.execute("""INSERT INTO table (title) VALUES (%s)""", (test,))
where test is the object I created parsing from json. After entering this command python shows 1L but when in sql i do
select * from table;
nothing appears, what is wrong?
You need to commit the changes you make to the data base. Use:
x.execute(...)
conn.commit()
I'd try one of two things. If I have to go with a full script like that, I don't bother using the conn... I'll do a subprocess call.
# farm_out_to_os.py
cmd = """INSERT INTO table (title) VALUES ({})""".format(test))
subprocess.call("mysql -u{} -p{} < {}".format(uname, pwd, cmd), shell=True)
But if you want to do it more programmatically, maybe consider using a full ORM like SQLAlchemy
# models.py
import sqlalchemy as db
from sqlalchemy import declarative_base
Base = declarative_base()
class MyTable(Base):
__tablename__ = 'mytable'
id = db.Column(db.Integer, primary_key=True)
val = db.Column(db.Integer)
def __init__(self, val):
self.val = val
And the code:
# code.py
from sqlalchemy import create_engine, sessionmaker
engine = create_engine(config.SQLALCHEMY_URL)
session = sqlalchemy.sessionmaker(bind=engine)
newval = models.MyTable(val=5)
session.add(newval)
session.commit()
session.close()
Depends on what you're trying to do :)
conn.commit()to the end of your inputs. This tells the connection to push the previous commands to the db.x.execute(...)followed byconn.commit().conn.commit()