Posts: 5
Threads: 1
Joined: Feb 2017
I'm trying to use a SQL class that I found and am having issues with getting it to work and am wondering if anyone has suggestions.
Here is the code (also github.com/nestordeharo/mysql-python-class):
#!/usr/bin/env python
# coding=utf-8
import MySQLdb, sys
from collections import OrderedDict
class MysqlPython(object):
"""
Python Class for connecting with MySQL server and accelerate development project using MySQL
Extremely easy to learn and use, friendly construction.
"""
__instance = None
__host = None
__user = None
__password = None
__database = None
__session = None
__connection = None
def __new__(cls, *args, **kwargs):
if not cls.__instance or not cls.__database:
cls.__instance = super(MysqlPython, cls).__new__(cls, *args, **kwargs)
return cls.__instance
## End def __new__
def __init__(self, host='localhost', user='root', password='', database=''):
self.__host = host
self.__user = user
self.__password = password
self.__database = database
## End def __init__
def __open(self):
try:
cnx = mysql.connector.connect(self.__host, self.__user, self.__password, self.__database)
self.__connection = cnx
self.__session = cnx.cursor()
except mysql.connector.errorcode as e:
print("Error %d: %s" % (e.args[0],e.args[1]))
## End def __open
def __close(self):
self.__session.close()
self.__connection.close()
## End def __close
def select(self, table, where=None, *args, **kwargs):
result = None
query = 'SELECT '
keys = args
values = tuple(kwargs.values())
l = len(keys) - 1
for i, key in enumerate(keys):
query += "`"+key+"`"
if i < l:
query += ","
## End for keys
query += 'FROM %s' % table
if where:
query += " WHERE %s" % where
## End if where
self.__open()
self.__session.execute(query, values)
number_rows = self.__session.rowcount
number_columns = len(self.__session.description)
if number_rows >= 1 and number_columns > 1:
result = [item for item in self.__session.fetchall()]
else:
result = [item[0] for item in self.__session.fetchall()]
self.__close()
return result
## End def select
def update(self, table, where=None, *args, **kwargs):
query = "UPDATE %s SET " % table
keys = kwargs.keys()
values = tuple(kwargs.values()) + tuple(args)
l = len(keys) - 1
for i, key in enumerate(keys):
query += "`"+key+"` = %s"
if i < l:
query += ","
## End if i less than 1
## End for keys
query += " WHERE %s" % where
self.__open()
self.__session.execute(query, values)
self.__connection.commit()
# Obtain rows affected
update_rows = self.__session.rowcount
self.__close()
return update_rows
## End function update
def insert(self, table, *args, **kwargs):
values = None
query = "INSERT INTO %s " % table
if kwargs:
keys = kwargs.keys()
values = tuple(kwargs.values())
query += "(" + ",".join(["`%s`"] * len(keys)) % tuple (keys) + ") VALUES (" + ",".join(["%s"]*len(values)) + ")"
elif args:
values = args
query += " VALUES(" + ",".join(["%s"]*len(values)) + ")"
self.__open()
self.__session.execute(query, values)
self.__connection.commit()
self.__close()
return self.__session.lastrowid
## End def insert
def delete(self, table, where=None, *args):
query = "DELETE FROM %s" % table
if where:
query += ' WHERE %s' % where
values = tuple(args)
self.__open()
self.__session.execute(query, values)
self.__connection.commit()
# Obtain rows affected
delete_rows = self.__session.rowcount
self.__close()
return delete_rows
## End def delete
def select_advanced(self, sql, *args):
od = OrderedDict(args)
query = sql
values = tuple(od.values())
self.__open()
self.__session.execute(query, values)
number_rows = self.__session.rowcount
number_columns = len(self.__session.description)
if number_rows >= 1 and number_columns > 1:
result = [item for item in self.__session.fetchall()]
else:
result = [item[0] for item in self.__session.fetchall()]
self.__close()
return result
## End def select_advanced
## End classThe line that I'm currently having issues with is
cls.__instance = super(MysqlPython, cls).__new__(cls, *args, **kwargs) The error that I get is that **kwargs is an unexpected argument. I think I know what the error means, but I don't know why I'm getting it or how to fix it.
Thanks.
Posts: 12,137
Threads: 496
Joined: Sep 2016
Posts: 5
Threads: 1
Joined: Feb 2017
(Feb-20-2017, 06:20 PM)Larz60+ Wrote: Homework?
No, home project. I'm trying to develop an app to run on the Raspberry PI with QML, Python, and MySQL. Beginner Python programmer? - Yes.
Posts: 12,137
Threads: 496
Joined: Sep 2016
Ok, Please show complete traceback
Posts: 5
Threads: 1
Joined: Feb 2017
Feb-20-2017, 06:37 PM
(This post was last modified: Feb-20-2017, 06:38 PM by jstockton.)
Error: /Library/Frameworks/Python.framework/Versions/3.5/bin/python3.5 "/Users/jasonstockton/Qt programs/Demo/DemoQMLProgram/main.py"
getMonitorInterval
Traceback (most recent call last):
File "/Users/jasonstockton/Qt programs/Demo/DemoQMLProgram/main.py", line 287, in <module>
QObject,main(sys.argv)
File "/Users/jasonstockton/Qt programs/Demo/DemoQMLProgram/main.py", line 259, in main
app = Controller(sys.argv)
File "/Users/jasonstockton/Qt programs/Demo/DemoQMLProgram/main.py", line 244, in __init__
environment = Environment()
File "/Users/jasonstockton/Qt programs/Demo/DemoQMLProgram/main.py", line 34, in __init__
self.interval = AppSettings.getmonitorinterval(self)
File "/Users/jasonstockton/Qt programs/Demo/DemoQMLProgram/main.py", line 78, in getmonitorinterval
_connection = MysqlPython('raspi', 'root', 'pwd', 'Demo')
File "/Users/jasonstockton/Qt programs/Demo/DemoQMLProgram/main.py", line 101, in __new__
cls.__instance = super(MysqlPython, cls).__new__(cls, *args, **kwargs)
TypeError: object() takes no parameters
Process finished with exit code 1
Here is the entire program. It's messy and needs to be refactored. I have it all lumped together like this to try and figure out whats going on.
import sys
import random
import mysql
import mysql.connector
from collections import OrderedDict
from PyQt5 import QtCore
from mysql.connector import errorcode
# from .lib.database import Mysql
# from .lib.thread import TemperatureThread
# from .lib.settings import AppSettings
from PyQt5.QtCore import QObject, QUrl, pyqtSlot
from PyQt5.QtGui import QGuiApplication
from PyQt5.QtQml import QQmlApplicationEngine
class TemperatureThread(QtCore.QObject):
@pyqtSlot()
def querytemp(self,result=int):
print("queryTemp")
return random.randint(1, 100)
class Environment:
def __init__(self):
super(Environment, self).__init__()
self._thread = QtCore.QThread()
self._timer = QtCore.QTimer()
self.interval = AppSettings.getmonitorinterval(self)
self.interval = 5000
self.temperaturethread = None
print('interval is ' + str(self.interval))
def starttemperaturemonitoring(self):
print("starttemperaturemonitoring")
self.temperaturethread = TemperatureThread()
self._timer.timeout.connect(self.temperaturethread.querytemp)
self._timer.start(self.interval)
self._timer.moveToThread(self._thread)
self._thread.start()
def stoptemperaturemonitoring(self):
self._timer.stop()
self._thread.quit()
# def gettemperature(self, value):
# print("getTemperature")
# # self.setcurrentenvironmentvalue("temperature", value, QDateTime.currentDateTime().toString("yyyy/MM/dd hh:mm:ss"))
def gethumidity(self):
print(random.randint(100, 200))
def setcurrentenvironmentvalue(self, variable, value, dateTime):
print("setCurrentEnvironmentValue")
# _connection = Mysql(host='raspi', user='root', password='GrowMachine', database='GrowMachine')
# _connection.insert('environmentValues', variable = variable, value = value, dateTime = dateTime)
# @pyqtSlot(str, result='QString')
# def getcurrentenvironmentvalue(self, variable):
# print("getCurrentEnvironmentValue")
# variable = 'temperature'
# cmd = """SELECT value FROM environmentValues"""
# cmd += """ where variable = '""" + variable + """';"""
# self.cursor.execute(cmd)
# value = self.cursor.fetchone()[0]
# return str(value)
class AppSettings:
def getmonitorinterval(self):
print("getMonitorInterval")
_connection = MysqlPython('raspi', 'root', 'pwd', 'Demo')
conditional_query = 'setting = %s '
temp = _connection.select('settings', conditional_query, 'value', setting='monitorInterval')
print(temp)
return temp
class MysqlPython(object):
"""
Python Class for connecting with MySQL server and accelerate development project using MySQL
Extremely easy to learn and use, friendly construction.
"""
__instance = None
__host = None
__user = None
__password = None
__database = None
__session = None
__connection = None
def __new__(cls, *args, **kwargs):
if not cls.__instance or not cls.__database:
cls.__instance = super(MysqlPython, cls).__new__(cls, *args, **kwargs)
return cls.__instance
## End def __new__
def __init__(self, host='localhost', user='root', password='', database=''):
self.__host = host
self.__user = user
self.__password = password
self.__database = database
## End def __init__
def __open(self):
try:
cnx = mysql.connector.connect(self.__host, self.__user, self.__password, self.__database)
self.__connection = cnx
self.__session = cnx.cursor()
except mysql.connector.errorcode as e:
print("Error %d: %s" % (e.args[0],e.args[1]))
## End def __open
def __close(self):
self.__session.close()
self.__connection.close()
## End def __close
def select(self, table, where=None, *args, **kwargs):
result = None
query = 'SELECT '
keys = args
values = tuple(kwargs.values())
l = len(keys) - 1
for i, key in enumerate(keys):
query += "`"+key+"`"
if i < l:
query += ","
## End for keys
query += 'FROM %s' % table
if where:
query += " WHERE %s" % where
## End if where
self.__open()
self.__session.execute(query, values)
number_rows = self.__session.rowcount
number_columns = len(self.__session.description)
if number_rows >= 1 and number_columns > 1:
result = [item for item in self.__session.fetchall()]
else:
result = [item[0] for item in self.__session.fetchall()]
self.__close()
return result
## End def select
def update(self, table, where=None, *args, **kwargs):
query = "UPDATE %s SET " % table
keys = kwargs.keys()
values = tuple(kwargs.values()) + tuple(args)
l = len(keys) - 1
for i, key in enumerate(keys):
query += "`"+key+"` = %s"
if i < l:
query += ","
## End if i less than 1
## End for keys
query += " WHERE %s" % where
self.__open()
self.__session.execute(query, values)
self.__connection.commit()
# Obtain rows affected
update_rows = self.__session.rowcount
self.__close()
return update_rows
## End function update
def insert(self, table, *args, **kwargs):
values = None
query = "INSERT INTO %s " % table
if kwargs:
keys = kwargs.keys()
values = tuple(kwargs.values())
query += "(" + ",".join(["`%s`"] * len(keys)) % tuple (keys) + ") VALUES (" + ",".join(["%s"]*len(values)) + ")"
elif args:
values = args
query += " VALUES(" + ",".join(["%s"]*len(values)) + ")"
self.__open()
self.__session.execute(query, values)
self.__connection.commit()
self.__close()
return self.__session.lastrowid
## End def insert
def delete(self, table, where=None, *args):
query = "DELETE FROM %s" % table
if where:
query += ' WHERE %s' % where
values = tuple(args)
self.__open()
self.__session.execute(query, values)
self.__connection.commit()
# Obtain rows affected
delete_rows = self.__session.rowcount
self.__close()
return delete_rows
## End def delete
def select_advanced(self, sql, *args):
od = OrderedDict(args)
query = sql
values = tuple(od.values())
self.__open()
self.__session.execute(query, values)
number_rows = self.__session.rowcount
number_columns = len(self.__session.description)
if number_rows >= 1 and number_columns > 1:
result = [item for item in self.__session.fetchall()]
else:
result = [item[0] for item in self.__session.fetchall()]
self.__close()
return result
## End def select_advanced
## End class
class Controller(QGuiApplication):
def __init__(self, argv, parent=None):
super(Controller, self).__init__(argv)
global environment
environment = Environment()
def start(self, int):
# Show that the slot has been called.
print("trigger start signal received")
environment.starttemperaturemonitoring()
def stop(self):
# Show that the slot has been called.
print("trigger stop signal received")
environment.stoptemperaturemonitoring()
def main(argv):
app = Controller(sys.argv)
engine = QQmlApplicationEngine()
context = engine.rootContext()
# settings = AppSettings()
# context.setContextProperty("settings", appSettings)
engine.load(QUrl('main.qml'))
mainwindow = engine.rootObjects()[0]
power_button = mainwindow.findChild(QObject, "powerButton")
power_button.clicked.connect(app.quit)
power_switch = mainwindow.findChild(QObject, "powerSwitch")
power_switch.start.connect(app.start)
power_switch.stop.connect(app.stop)
if mainwindow is not None:
mainwindow.show()
else:
for err in mainwindow.errors():
print(err.toString())
app.exec()
if __name__ == '__main__':
QObject,main(sys.argv)
Posts: 7,431
Threads: 125
Joined: Sep 2016
Feb-20-2017, 06:44 PM
(This post was last modified: Feb-20-2017, 06:44 PM by snippsat.)
Use something that is maintained and updated like PyMySQL.
Quote: I'm trying to develop an app to run on the Raspberry PI with QML, Python, and MySQL
For Raspberry PI i would have used something simpler,
like SQLite( build into Python), dataset that i have a tutorial about here.
Posts: 12,137
Threads: 496
Joined: Sep 2016
I can't run this as I don't use MySql, but I think
changing:
cls.__instance = super(MysqlPython, cls).__new__(cls, *args, **kwargs) to:
cls.__instance = super(MysqlPython, self).__new__(cls, *args, **kwargs) may be the issue
Posts: 5
Threads: 1
Joined: Feb 2017
(Feb-20-2017, 06:44 PM)snippsat Wrote: Use something that is maintained and updated like PyMySQL.
Quote: I'm trying to develop an app to run on the Raspberry PI with QML, Python, and MySQL
For Raspberry PI i would have used something simpler,
like SQLite(build into Python), dataset that i have a tutorial about here.
I started out with SQLite, but ran into an issue of not being able to specify a previously created SQL database from QML. I need to be able to update my database in my Python script and read values in QML and vice versa. I posted this question on the Qt forums and was told there isn't a way to do this. https://forum.qt.io/topic/76166/location...e-database SQLite would be much simpler like you said.
I will take a look at PyMySQL. Thanks for the reference. For my own knowledge I would still like to figure out what's wrong with my script in case I run into anything like it again.
Posts: 12,137
Threads: 496
Joined: Sep 2016
It is a straight forward procedure in any relational database management system that I have worked with to unload a complete schema to external files.
I don't know how to do this in MySQL, here's how it's done in sqlite3, MySql will have some sort of dump program
The database is one of mine:
sqlite3 StockMktDb
sqlite>
sqlite> .output StockMktDb.sql
sqlite> .dump
sqlite> .quit partial results:
Output: PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE ASXListed ( CompanyName VARCHAR, AsxSymbol VARCHAR, GicsIndustryGroup VARCHAR);
INSERT INTO "ASXListed" VALUES('Company name','ASX code','GICS industry group');
INSERT INTO "ASXListed" VALUES('MOQ LIMITED','MOQ','Software & Services');
INSERT INTO "ASXListed" VALUES('1-PAGE LIMITED','1PG','Software & Services');
INSERT INTO "ASXListed" VALUES('1300 SMILES LIMITED','ONT','Health Care Equipment & Services');
INSERT INTO "ASXListed" VALUES('1ST AVAILABLE LTD','1ST','Health Care Equipment & Services');
INSERT INTO "ASXListed" VALUES('333D LIMITED','T3D','Food, Beverage & Tobacco');
However, that aside, once a database has been dumped to .sql files, it's
a simple procedure to reload in sqlite3.
sqlite3 newdb
sqlite> .read StockMktDb.sql That's all there is to it
Posts: 5
Threads: 1
Joined: Feb 2017
Feb-21-2017, 01:27 AM
(This post was last modified: Feb-21-2017, 01:38 AM by jstockton.)
(Feb-20-2017, 06:49 PM)Larz60+ Wrote: I can't run this as I don't use MySql, but I think
changing:
cls.__instance = super(MysqlPython, cls).__new__(cls, *args, **kwargs) to:
cls.__instance = super(MysqlPython, self).__new__(cls, *args, **kwargs) may be the issue
I tried this change and now I get
Error: /Library/Frameworks/Python.framework/Versions/3.5/bin/python3.5 "/Users/jasonstockton/Qt programs/Demo/DemoQMLProgram/main.py"
getMonitorInterval
Traceback (most recent call last):
File "/Users/jasonstockton/Qt programs/Demo/DemoQMLProgram/main.py", line 287, in <module>
QObject,main(sys.argv)
File "/Users/jasonstockton/Qt programs/Demo/DemoQMLProgram/main.py", line 259, in main
app = Controller(sys.argv)
File "/Users/jasonstockton/Qt programs/Demo/DemoQMLProgram/main.py", line 244, in __init__
environment = Environment()
File "/Users/jasonstockton/Qt programs/Demo/DemoQMLProgram/main.py", line 34, in __init__
self.interval = AppSettings.getmonitorinterval(self)
File "/Users/jasonstockton/Qt programs/Demo/DemoQMLProgram/main.py", line 78, in getmonitorinterval
_connection = MysqlPython('raspi', 'root', 'pwd', 'Demo')
File "/Users/jasonstockton/Qt programs/Demo/DemoQMLProgram/main.py", line 101, in __new__
cls.__instance = super(MysqlPython, self).__new__(cls, *args, **kwargs)
NameError: name 'self' is not defined
Process finished with exit code 1
Are the errors I am getting occurring because of MySQL or are they straight Python errors? I was thinking the errors are coming from Python and would occur no matter what flavor of SQL I use.
(Feb-20-2017, 08:28 PM)Larz60+ Wrote: It is a straight forward procedure in any relational database management system that I have worked with to unload a complete schema to external files.
I don't know how to do this in MySQL, here's how it's done in sqlite3, MySql will have some sort of dump program
The database is one of mine:
sqlite3 StockMktDb
sqlite>
sqlite> .output StockMktDb.sql
sqlite> .dump
sqlite> .quit partial results:
Output: PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE ASXListed ( CompanyName VARCHAR, AsxSymbol VARCHAR, GicsIndustryGroup VARCHAR);
INSERT INTO "ASXListed" VALUES('Company name','ASX code','GICS industry group');
INSERT INTO "ASXListed" VALUES('MOQ LIMITED','MOQ','Software & Services');
INSERT INTO "ASXListed" VALUES('1-PAGE LIMITED','1PG','Software & Services');
INSERT INTO "ASXListed" VALUES('1300 SMILES LIMITED','ONT','Health Care Equipment & Services');
INSERT INTO "ASXListed" VALUES('1ST AVAILABLE LTD','1ST','Health Care Equipment & Services');
INSERT INTO "ASXListed" VALUES('333D LIMITED','T3D','Food, Beverage & Tobacco');
However, that aside, once a database has been dumped to .sql files, it's
a simple procedure to reload in sqlite3.
sqlite3 newdb
sqlite> .read StockMktDb.sql That's all there is to it
I think I figured out how I can make SQLite work. As I've been trying to develop this app I've run into various issues and have gone down different paths that work or don't work and I had a thought in my head that wasn't accurate. Talking through all of this made me look at it differently and realize this is a derp moment for me. My QML is calling a Python slot to interact with the db which I was going to do with MySQL. I just realized I can use SQLite in the slot instead. I'll set the db name in Python and not worry about having to set it in QML. I would still like to figure out why my SQL class above is throwing that error.
|
