pythonmysqlpyqt5qsqltablemodelqsqldatabase

PYQT5 how to change the scheme in mysql odbc connection for QSqlTableModel?


I have a valid ODBC connection to Mysql server with root access Platform Windows 7 64bit, Python 3.6, PyQt5

The connection is to default schema 'accounts'. I want to fetch data from different schema 'acc001'

I tried this but no result returned

from PyQt5.QtSql import *

db = QSqlDatabase.addDatabase('QODBC')
db.setDatabaseName('mysql_db01')

db.setUserName('root')
db.setPassword('#######')
db.open()

test_model = QSqlTableModel()
test_model.setTable('test1')
test_model.setFilter('id >= 35')
test_model.select()
idx = test_model.index(0,1)
print('Data',idx.data(),idx1.data(),test_model.rowCount(), test_model.columnCount())


new_model = QSqlTableModel()
new_model.setTable('acc001.a_acc_mst')
new_model.select()
idx = new_model.index(0,1)
idx1 = new_model.index(0,0)
print('Data',idx.data(),idx1.data())
print(new_model.rowCount(), new_model.columnCount())

The output are

Data RAM 35
3 6
Data None None
0 0

I want to work on two schema simultaneously how to go about it with odbc as the

db = QtSql.QSqlDatabase.addDatabase('QMYSQL') 

does not work


Solution

  • One solution I found was everytime I want to change the schema I need to run this command

    db.exec("use acc001")
    

    acc001 is the schema name but this is cumbersome any better solution will be welcome