SQL Script to Backup all SQL Server Databases Backup all databases on your server using T-SQL is a much simpler and faster. Below T-SQL script will generate backup of all databases with date stamp in backup file name. Update the backup path in below script. DECLARE @dbname NVARCHAR(256) DECLARE @filepath NVARCHAR(512) DECLARE @fileName NVARCHAR(512) DECLARE @Date NVARCHAR(40) -- Database backup directory SET @filepath = 'D:\backup\' -- filename format SELECT @Date = CONVERT(NVARCHAR(20),GETDATE(),110) DECLARE backup_cursor CURSOR READ_ONLY FOR SELECT name FROM master.sys.databases WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude system databases AND state = 0 -- Online database AND is_in_standby = 0 -- database is not read only for log shipping OPEN backup_cursor FETCH NEXT FROM backup_cursor INTO @dbname WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @filepath + @dbname + '_' + @Date + '.bak' BACKUP DATABASE @dbname TO DISK = @fileName WITH INIT FETCH NEXT FROM backup_cursor INTO @dbname END CLOSE backup_cursor DEALLOCATE backup_cursor

Get non-zero values from a QSqlQueryModel that contains NULL using PyQt5

Fayyazuddin

I am querying from a SQL database and displaying the output using PyQt. I am using UNION ALL to combine two tables that share a column. They are both in the same schema (named schema).

Here are the tables:

table_1:

number id 1 1 3 1 5 1

table_2:

number letter_number 2 2 4 2 6 2

The SQL query is:

SELECT "number", "id", NULL::bigint AS "letter_number" 
FROM schema."table_1" 
UNION ALL
SELECT "number", NULL::bigint AS "id", "letter_number"
FROM schema."table_2";

The resulting table is:

number id letter_number 1 1 2 2 3 1 4 2 5 1 6 2

I then use PyQt5 and Python to get the resulting values. I do not want to edit them. The query text is the same as above.

from PyQt5.QtSql import QSqlQueryModel
model = QSqlQueryModel()
model.setQuery(querytext)
for row in range(model.rowCount()):
    for column in range(model.columnCount()):
        index = model.index(row, column)
        print(model.data(index))

I get this for the output (I have formatted it to better display the problem):

1, 1, 0
2, 0, 2
3, 1, 0
4, 0 ,2
5, 1, 0
6, 0, 2

The NULL values come out as 0 (zero), how do I stop this and get something that is not a number (empty string, string that says 'NULL', etc.)? I use other tables that contain 0 as a value, so adding an if statement to change the zeros to anything else is out of the question.

I am currently displaying the model using PyQt's QTableView(). It may be possible to get the data from there instead, but I am not sure.

Thanks!