Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

How to transfer the output of an SQLite query into a spreadsheet (Excel)

C
chanisef last edited by
After running the query, I am getting 4 rows of output. How to transfer the same into an Excel file. I know I can do this manually, but there should be a method.

Unable to figure out how to access command line interface so that I can try with this code as per the above StackExchange post:
```
    sqlite> .mode csv sqlite> .output test.csv sqlite> select * from tbl1; sqlite> 
.output stdout
```
Reply Quote 0
1 Reply Last reply
inna last edited by

you will need to have SQLite3 installed on your system, the database in question on your system and then navigate to the installation directory (or have the program directory in your PATH) before you type the sqlite command. You could also see if your DB Browser GUI program has the ability to export directly to a CSV. Or, if you do really only do expect to deal with 4 lines of data at a time, you can probably control-A (highlight all), control-C (copy) and then paste into excel directly.

As your question is more of a how-do-i-use-a-dos-prompt question than a database question, further follow-up should probably be on one of the other stackexchanges (stack overflow?) or by reading the documentation for SQLite.
Reply Quote 0
1 Reply Last reply

sqlite 2984

2
Posts

0
Views

Suggested Topics

R

structural problem
Software Programming • c language sqlite struct • • Rossere

2

0
Votes

2
Posts

0
Views

A

Modified arguments sqlite3_mprintf Type char *And you've got some of them type int♪ You can use the function. char *itoa(int) from stdlib.h
How can I change the default route of a database on Android?
Software Programming • android android studio sqlite android sqlite • • Demir

2

0
Votes

2
Posts

0
Views

T

You'll have to do it in the name of the DB I do it the following way.public static final String DATABASE_NAME = "ejemplo.db"; public DataBaseHelper(Context context, String PATH) { super(context, PATH+"/"+DATABASE_NAME, null, DATABASE_VERSION);} When I want to make a connection to the DBPATH="/data/data/mi_aplicacion/databases/" DataBaseHelper dataBaseHelper = new DataBaseHelper(getApplicationContext(), Constructores.path); The Path is the route where you want to store it must be a valid route so you don't get any error when you check back the logs you store or want to store.To find the SD card route there are several methods that can help you but with Android there is a problem and that that card route depends on the manufacturer and the android version will always not be in the same location. For example I use this code to find my route./** * metodo para listar los directorios de los almacenamientos externos * * @param pContext Contexto * @return Lista String con las rutas. */ public static String getStorageDirectories(Context pContext) { //Get primary & secondary external device storage (internal storage & micro SDCARD slot...) File[] listExternalDirs = ContextCompat.getExternalFilesDirs(pContext, null); File extStore = Environment.getExternalStorageDirectory(); for (int i = 0; i < listExternalDirs.length; i++) { if (listExternalDirs[i] != null) { String path = listExternalDirs[i].getAbsolutePath(); if (path.contains("-")) { return path; } else { if (path.contains("extsdcard")) { return path; } else { if (path.contains("extSdCard")) { return path; } else { if (path.contains("sdcard")) { return path; } else { if (path.contains("external_sd")) { return path; } else { if (path.contains("external_SD")) { return path; } else { if (path.contains("ext_sd")) { return path; } else { if (path.contains("sdext")) { return path; } else { if (path.contains("sdcard1")) { return path; }else { if(new File("/storage/sdcard1/").exists()) { path=path.replace(extStore.toString() ,"/storage/sdcard1"); return path; } } } } } } } } } } } } return ""; }
M

query processor ran out of internal resources - extremely long sql query
SQL, Database Testing • sql server sql server 2012 • • meriah

2

0
Votes

2
Posts

0
Views

Z

The error message is there for a reason. Using UNION on thousands of tables in a single query is performance suicide. Yes, I understand that your reporting application is already in production, but I would still strongly recommend that you to go back to your development team and update the application or, even better, rebuild its logic in a stored procedure instead. As you suggest in your question, create a temp table, run each of those queries sequentially and dump the results into the temp table. Then, compile the report from the temp table. All of this can be done in a stored procedure, which will also cover other important aspects like security and performance. This carries a range of advantages: Temp table can be indexed for performance You won't bloat your server's query plan cache (and in the process evict pretty much any other query plan that has ever been cached) The query won't take forever to "compile" (i.e. turn into an execution plan) I'm not trying to mess with you. It's my firm conviction that if you don't rebuild the report now, you will most likely have to some day, going forward.
K

Xamarin - SQLite Join
Software Programming • c sharp android sqlite xamarin c languageross platform • • Kadir

2

0
Votes

2
Posts

0
Views

T

SQLite does not support .Join via linq naturally. What you can do is make the query at hand._SQLiteConnection.Query<PROD>( "select * from tbPROD p INNER JOIN tbITPD pd on p.PROD_Id = pd.ITPD_PROD_id").ToList(); More details can be seen in https://developer.xamarin.com/guides/cross-platform/application_fundamentals/data/part_3_using_sqlite_orm/ .Now, if you want to use a third-party library, there a https://bitbucket.org/twincoders/sqlite-net-extensions , which allows to use .Join via linq.
M

Output another word instead of the word in the table in a query
SQL, Database Testing • oracle • • magpie

2

0
Votes

2
Posts

0
Views

K

Either use the SQL standard way of CASE WHEN ... as ypercybe suggested in the comments or for more neat result, DECODE function: DECODE(fieldref, 'enabled', 'unblocked', 'disabled', 'blocked')
Use name of table as input in procedure and store the output of SELECT statement in output variable SQL
SQL, Database Testing • sql server sql server 2012 • • irl

2

0
Votes

2
Posts

0
Views

S

First you're not using a valid syntax due your SELECT command has no FROM clause. Then you should use sp_executesql just to take care of the output parameters. create table xx (id int, column1 int); create table yy (id int, column1 int); insert into xx values (1, 1),(2,2),(3,3); insert into yy values (1,10),(3,30),(4,40); GO 6 rows affected CREATE PROCEDURE sample_proc ( @tablename VARCHAR(MAX), @myoutput DECIMAL(20,5) OUTPUT ) AS SET NOCOUNT ON; DECLARE @Cmd NVARCHAR(MAX); DECLARE @ParmDefinition NVARCHAR(500); SET @ParmDefinition = '@Total decimal(20,5) OUTPUT'; SET @Cmd = 'SELECT @Total = (SUM(column1)/3.3) FROM [' + @tablename + ']'; -- just to show SQL command. SELECT @Cmd; EXEC sp_executesql @Cmd, @ParmDefinition, @Total = @myoutput out; GO ✓ DECLARE @op DECIMAL(20,5); EXEC sample_proc 'xx', @op OUT SELECT @op; EXEC sample_proc 'yy', @op OUT SELECT @op; GO | (No column name) | | :------------------------------------------- | | SELECT @Total = (SUM(column1)/3.3) FROM [xx] | | (No column name) | | :--------------- | | 1.81818 | | (No column name) | | :------------------------------------------- | | SELECT @Total = (SUM(column1)/3.3) FROM [yy] | | (No column name) | | :--------------- | | 24.24242 | dbfiddle here
K

How to handle big data update based on excel spreadsheet
SQL, Database Testing • oracle excel update oracle 11g r2 • • kalena

2

0
Votes

2
Posts

0
Views

E

A Fast Method A probably faster way to do this is the following: Provide the data in a database table Execute a MERGE statement that merges the data from this table into the source table To provide the data in a database table you can use sql loader or [external tables]. http://docs.oracle.com/database/121/SQLRF/statements_9016.htm#SQLRF01606 create table big_tab( id number primary key, value1 number, value2 number, value3 number) ; create table mod_tab( id number, value1 number, value2 number, value3 number) ; Now assume you have the following data in big_tab. SQL> select * from big_tab; ID VALUE1 VALUE2 VALUE3 1 11 21 31 2 12 22 32 3 13 23 33 4 14 24 34 5 15 25 35 you have a csv file mod_data.csv with the following values 1,111,121,131 3,113,123,133 you can load this into the table with sqlldr load.ctl DIRECT=true with the sql loader control file sql.ldr containing LOAD DATA INFILE "mod_data.csv" TRUNCATE INTO TABLE mod_tab FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( id char, value1 char, value2 char, value3 char ) Using the option "DIRECT=true" will be faster thean loading without this option. Now you have SQL> select * from mod_tab; ID VALUE1 VALUE2 VALUE3 ---------- ---------- ---------- ---------- 1 111 121 131 3 113 123 133 you can merge this into big_tab with the following statement MERGE INTO big_tab using mod_tab on (big_tab.id=mod_tab.id) when matched then update set value1=mod_tab.value1, value2=mod_tab.value2, value3=mod_tab.value3 ; and get SQL> select * from big_tab; ID VALUE1 VALUE2 VALUE3 ---------- ---------- ---------- ---------- 1 111 121 131 2 12 22 32 3 113 123 133 4 14 24 34 5 15 25 35 If the processing was ok you can cleanup truncate table mod_tab; An Even Faster Method If you have a lot of data to modify, there is a way that is even better (at least if your io system is fast): create a auxiliary table with the same structure as big_tab table insert the modify rows in this auxiliary table insert the rows from big_table that will not be modified in the auxiliary table drop the big_tab table rename the auxiliary table to big_tab. add constraints and indexes to the new big_tab table This can be don by the following script create table aux_tab as select * from big_tab where 1=0; alter table mod_tab add primary key (id); insert /*+ APPEND */ into aux_tab select big_tab.id id, decode(mod_tab.id,null,big_tab.value1,mod_tab.value1) value1, decode(mod_tab.id,null,big_tab.value2,mod_tab.value2) value1, decode(mod_tab.id,null,big_tab.value3,mod_tab.value3) value3 from big_tab, mod_tab where big_tab.id=mod_tab.id(+) ; drop table big_tab; rename aux_tab to big_tab; alter table big_tab add primary key (id); alter table mod_tab drop primary key; truncate table mod_tab; And Even Faster You should always do your load and insert in direct path mod. Therefore I added and APPEND hint to the insert statement and the DIRECT=true option to the SQL*Loader command. But you can try to parallelize some actions to get even faster. Parallel statement work optimal on partitioned tables so it may make sense to partition big_tab and also mod_tab and aux_tab.
K

Django storage of SQLite
Software Programming • django sqlite • • kalyn

2

0
Votes

2
Posts

0
Views

E

class Propertytype(models.Model): description = models.CharField(max_length=140) class Property(models.Model): property = models.CharField(max_length=140) propertytype = models.ForeignKey(Propertytype) EEG = models.ForeignKey(EEG) class EEG(CommonInfo): number = models.CharField(max_length=30,null=True, blank=True) If I'm right about the task.Propertytype will include records of four elements (Element 1, .., ..Element4)
Y

How to optimise this query?
SQL, Database Testing • sql server performance query performance optimization • • yajahira

2

0
Votes

2
Posts

0
Views

Misread your post and wrote up a post telling you how to optimize the inserts, which seemed like an odd activity to do. The real query to optimize is this: select * from Person p where p.Name like 'a%'; That query gets back around 6100 rows out of 100000 rows for me. Even if you create a simple index on the name column it is unlikely that the query optimizer will use it. create index Person__name ON Person (name); select * from Person p WITH (INDEX (Person__name)) where p.Name like 'a%'; Disclaimer that I'm only using that hint for testing purposes. You don't want to use something like that in production unless you really know what you're doing. That hint forces the optimizer to use the index but I get worse results than not using it. If you truly need that query to be faster my only ideas are that you'll need to create a covering index, you'll need to change the structure of the table, or maybe there are some small gains to be had by writing the filter condition in a different way. Here is what I mean by a covering index: create index Person__name ON Person (name) INCLUDE (StreetAddress, Height, CompanyId) That index avoids the key lookups that you get with a simple index because every necessary column is included in it. However, the size of that index will be similar in size to the table. Any DML on the table will be slower because the index needs to be maintained. You could change the clustering index from ID to name. That would have significant impacts on DML performance and on other queries, but it would make your query faster. Perhaps instead of LIKE there is another string operator that you can use. I doubt that such changes would have a significant impact on your query's runtime.
C

Respond to function: moving from one table to another
Software Programming • python sqlite pyqt5 gui qtsql • • courtlanda

2

0
Votes

2
Posts

0
Views

D

In order to receive prompt and correct answers, you must provide a minimum reproducible app, which includes the formation and filling of tables.There's no need to make any references or even to write, 'bet any 2-3 records, 2 tables.'I've noted the lines you need to work with.Give attention to a copy of the table model Покупки should not have the same name as a copy of the model for the tables Товаров♪import sys from PyQt5 import QtCore, QtGui, QtWidgets from PyQt5.Qt import * class Ui_Wind(object): def setupUi(self, Wind): Wind.setObjectName("Wind") Wind.resize(948, 700) font = QtGui.QFont() font.setFamily("Agency FB") font.setPointSize(16) Wind.setFont(font) Wind.setAcceptDrops(False) Wind.setStyleSheet("background-color:rgb(255, 217, 179)") self.listView = QtWidgets.QListView(Wind) self.listView.setGeometry(QtCore.QRect(-10, 80, 981, 91)) self.listView.setStyleSheet("background-color: rgb(243, 194, 147)") self.listView.setObjectName("listView") self.pushButton = QtWidgets.QPushButton(Wind) self.pushButton.setGeometry(QtCore.QRect(0, 0, 231, 71)) font = QtGui.QFont() font.setFamily("Script MT Bold") font.setPointSize(14) font.setBold(True) font.setWeight(75) self.pushButton.setFont(font) self.pushButton.setCursor(QtGui.QCursor(QtCore.Qt.PointingHandCursor)) self.pushButton.setWhatsThis("") self.pushButton.setAccessibleName("") self.pushButton.setStyleSheet("background-color:\n""") self.pushButton.setText("Молочная продукция") icon = QtGui.QIcon() icon.addPixmap(QtGui.QPixmap("im.png"), QtGui.QIcon.Disabled, QtGui.QIcon.On) self.pushButton.setIcon(icon) self.pushButton.setIconSize(QtCore.QSize(230, 200)) self.pushButton.setObjectName("pushButton") self.listView_2 = QtWidgets.QListView(Wind) self.listView_2.setGeometry(QtCore.QRect(50, 170, 191, 811)) self.listView_2.setStyleSheet("background-color: rgb(255, 221, 187)") self.listView_2.setObjectName("listView_2") self.pushButton_2 = QtWidgets.QPushButton(Wind) self.pushButton_2.setGeometry(QtCore.QRect(240, 0, 231, 71)) font = QtGui.QFont() font.setFamily("Script MT Bold") font.setPointSize(14) font.setBold(True) font.setWeight(75) self.pushButton_2.setFont(font) self.pushButton_2.setCursor(QtGui.QCursor(QtCore.Qt.PointingHandCursor)) self.pushButton_2.setWhatsThis("") self.pushButton_2.setAccessibleName("") self.pushButton_2.setStyleSheet("background-color:\n""") self.pushButton_2.setText("Мясная продукция") icon1 = QtGui.QIcon() icon1.addPixmap(QtGui.QPixmap("мясо2.png"), QtGui.QIcon.Disabled, QtGui.QIcon.On) self.pushButton_2.setIcon(icon1) self.pushButton_2.setIconSize(QtCore.QSize(230, 200)) self.pushButton_2.setObjectName("pushButton_2") self.pushButton_3 = QtWidgets.QPushButton(Wind) self.pushButton_3.setGeometry(QtCore.QRect(480, 0, 231, 71)) font = QtGui.QFont() font.setFamily("Script MT Bold") font.setPointSize(14) font.setBold(True) font.setWeight(75) self.pushButton_3.setFont(font) self.pushButton_3.setCursor(QtGui.QCursor(QtCore.Qt.PointingHandCursor)) self.pushButton_3.setWhatsThis("") self.pushButton_3.setAccessibleName("") self.pushButton_3.setStyleSheet("background-color:\n""") self.pushButton_3.setText("Хлебобулочные Изделия") icon2 = QtGui.QIcon() icon2.addPixmap(QtGui.QPixmap("xlewb.png"), QtGui.QIcon.Disabled, QtGui.QIcon.On) self.pushButton_3.setIcon(icon2) self.pushButton_3.setIconSize(QtCore.QSize(230, 200)) self.pushButton_3.setObjectName("pushButton_3") self.pushButton_4 = QtWidgets.QPushButton(Wind) self.pushButton_4.setGeometry(QtCore.QRect(720, 0, 231, 71)) font = QtGui.QFont() font.setFamily("Script MT Bold") font.setPointSize(14) font.setBold(True) font.setWeight(75) self.pushButton_4.setFont(font) self.pushButton_4.setCursor(QtGui.QCursor(QtCore.Qt.PointingHandCursor)) self.pushButton_4.setWhatsThis("") self.pushButton_4.setAccessibleName("") self.pushButton_4.setStyleSheet("background-color:\n""") self.pushButton_4.setText("Товары_Для_Дома") icon3 = QtGui.QIcon() icon3.addPixmap(QtGui.QPixmap("таврары.png"), QtGui.QIcon.Disabled, QtGui.QIcon.On) self.pushButton_4.setIcon(icon3) self.pushButton_4.setIconSize(QtCore.QSize(230, 200)) self.pushButton_4.setObjectName("pushButton_4") self.listWidget = QtWidgets.QListWidget(Wind) self.listWidget.setGeometry(QtCore.QRect(90, 190, 321, 421)) self.listWidget.setStyleSheet("background-color: rgb(255, 221, 179)") self.listWidget.setObjectName("listWidget") self.listWidget_2 = QtWidgets.QListWidget(Wind) self.listWidget_2.setGeometry(QtCore.QRect(590, 190, 321, 421)) self.listWidget_2.setStyleSheet("background-color: rgb(255, 221, 179)") self.listWidget_2.setObjectName("listWidget_2") self.pushButton_5 = QtWidgets.QPushButton(Wind) self.pushButton_5.setGeometry(QtCore.QRect(440, 330, 131, 61)) self.pushButton_5.setObjectName("pushButton_5") self.pushButton_6 = QtWidgets.QPushButton(Wind) self.pushButton_6.setGeometry(QtCore.QRect(440, 410, 131, 61)) self.pushButton_6.setObjectName("pushButton_6") self.pushButton_7 = QtWidgets.QPushButton(Wind) self.pushButton_7.setGeometry(QtCore.QRect(680, 650, 141, 71)) self.pushButton_7.setObjectName("pushButton_7") self.pushButton_9 = QtWidgets.QPushButton(Wind) self.pushButton_9.setGeometry(QtCore.QRect(820, 650, 81, 71)) self.pushButton_9.setText("pushButton_9") icon4 = QtGui.QIcon() icon4.addPixmap(QtGui.QPixmap("изображение_2021-04-24_223918-removebg-preview.png"), QtGui.QIcon.Disabled, QtGui.QIcon.On) self.pushButton_9.setIcon(icon4) self.pushButton_9.setIconSize(QtCore.QSize(100, 100)) self.pushButton_9.setObjectName("pushButton_9") self.tableWidget = QtWidgets.QTableView(Wind) self.tableWidget.setGeometry(QtCore.QRect(100, 200, 301, 401)) self.tableWidget.setObjectName("tableWidget") self.tableWidget1 = QtWidgets.QTableView(Wind) self.tableWidget1.setGeometry(QtCore.QRect(600, 200, 301, 401)) self.tableWidget1.setObjectName("tableWidget1") self.retranslateUi(Wind) QtCore.QMetaObject.connectSlotsByName(Wind) def retranslateUi(self, Wind): _translate = QtCore.QCoreApplication.translate Wind.setWindowTitle(_translate("Wind", "Пампейка")) self.pushButton_5.setText(_translate("Wind", "Взять")) self.pushButton_6.setText(_translate("Wind", "Выгрузить")) self.pushButton_7.setText(_translate("Wind", "Купить")) class MainWindow(QtWidgets.QMainWindow, Ui_Wind): def init(self): super().init() self.setupUi(self) self.tableWidget.horizontalHeader().setStretchLastSection(True) self.tableWidget.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch) self.tableWidget1.horizontalHeader().setStretchLastSection(True) self.tableWidget1.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch) self.pushButton.clicked.connect(self.milk) self.pushButton_2.clicked.connect(self.mco) self.pushButton_3.clicked.connect(self.xlebw) self.pushButton_4.clicked.connect(self.hometov) self.pushButton_5.clicked.connect(self.add_row) self.pushButton_6.clicked.connect(self.on_del_record) ''' # ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? self.pushButton.clicked.connect(self.pokypki) self.pushButton_2.clicked.connect(self.pokypki) self.pushButton_3.clicked.connect(self.pokypki) self.pushButton_4.clicked.connect(self.pokypki) ''' db = QSqlDatabase.addDatabase('QSQLITE') db.setDatabaseName('Продукты.db') db.setDatabaseName('products.db') db.open() self.pokypki() def milk(self): # ? ? ? , Ui_Wind): self.model = QSqlTableModel(self) self.model.setTable("dairy") # Молочная_продукция self.model.select() self.tableWidget.setModel(self.model) def mco(self): # ? ? ? , Ui_Wind): self.model = QSqlTableModel(self) self.model.setTable("meat") # Мясная_продукция self.model.select() self.tableWidget.setModel(self.model) def xlebw(self): # ? ? ? , Ui_Wind): self.model = QSqlTableModel(self) self.model.setTable("bakery") # Хлебобулочные_Изделия self.model.select() self.tableWidget.setModel(self.model) def hometov(self): # ? ? ? , Ui_Wind): self.model = QSqlTableModel(self) self.model.setTable("household") # Товары_Для_Дома self.model.select() self.tableWidget.setModel(self.model) def pokypki(self): # ? ? ? , Ui_Wind): -----> vvvvvvvvvvvvvvv <------------------------------------------------------- !!! self.model_purchases = QSqlTableModel(self) self.model_purchases.setTable("purchases") # Покупки self.model_purchases.select() self.tableWidget1.setModel(self.model_purchases) def add_row(self): row = self.tableWidget.currentIndex().row() if row == -1: msg = QMessageBox.information(None, 'Внимание', 'Выберите продукт для покупки.') return id = self.model.record(row).value(0) name = self.model.record(row).value(1) price = self.model.record(row).value(3) r = self.model_purchases.record() r.setValue("id", id) r.setValue("name", name) r.setValue("price", price) self.model_purchases.insertRecord(-1, r) self.model_purchases.select() ''' self.model.insertRow(self.tableWidget.currentIndex().row()) name = self.model.record(row).value(1) opisanie = self.model.record(row).value(2) cena = self.model.record(row).value(3) self.model.insertRow(self.tableWidget1.currentIndex().row()) self.model.select() ''' def on_del_record(self): row = self.tableWidget1.currentIndex().row() if row == -1: msg = QMessageBox.information(self, 'Внимание', 'Выберите продукт для выгрузки.') return ''' name = self.model.record(row).value(1) opisanie = self.model.record(row).value(2) cena = self.model.record(row).value(3) self.model.removeRow(self.tableWidget1.currentIndex().row()) self.model.select() ''' self.model_purchases.removeRow(self.tableWidget1.currentIndex().row()) self.model_purchases.select() if name == "main": app = QtWidgets.QApplication(sys.argv) w = MainWindow() w.show() sys.exit(app.exec_()) Table design crisisCREATE-TABLE-db.pyfrom PyQt5.QtSql import QSqlDatabase, QSqlQuery db = QSqlDatabase.addDatabase('QSQLITE') db.setDatabaseName('products.db') db.open() query = QSqlQuery() query.exec_(""" CREATE TABLE dairy (id INT(11) PRIMARY KEY, name VARCHAR(30) NOT NULL, description VARCHAR(70) NOT NULL, price FLOAT)""") query.exec_("INSERT INTO dairy (id, name, description, price) " "VALUES (2018010401, 'Молоко 1', 'Описание 1', 59.52)") query.exec_("INSERT INTO dairy (id, name, description, price) " "VALUES (2018010422, 'Молоко 2', 'Описание 2', 159.2)") query.exec_("INSERT INTO dairy (id, name, description, price) " "VALUES (2018010555, 'Молоко 3', 'Описание 3', 75)") query.exec_("SELECT name, description, price FROM dairy") while query.next(): stu_name = query.value(0) stu_description = query.value(1) stu_price = query.value(2) print(stu_name, stu_description, stu_price) query.exec_(""" CREATE TABLE meat (id INT(11) PRIMARY KEY, name VARCHAR(30) NOT NULL, description VARCHAR(70) NOT NULL, price FLOAT)""") query.exec_("INSERT INTO meat (id, name, description, price) " "VALUES (1, 'Мясо 1', 'Описание 1', 159.52)") query.exec_("INSERT INTO meat (id, name, description, price) " "VALUES (2, 'Мясо 2', 'Описание 2', 559.2)") query.exec_("INSERT INTO meat (id, name, description, price) " "VALUES (3, 'Мясо 3', 'Описание 3', 275)") query.exec_(""" CREATE TABLE bakery (id INT(11) PRIMARY KEY, name VARCHAR(30) NOT NULL, description VARCHAR(70) NOT NULL, price FLOAT)""") query.exec_("INSERT INTO bakery (id, name, description, price) " "VALUES (1, 'Изделие 1', 'Описание 1', 19.52)") query.exec_("INSERT INTO bakery (id, name, description, price) " "VALUES (2, 'Изделие 2', 'Описание 2', 55.2)") query.exec_("INSERT INTO bakery (id, name, description, price) " "VALUES (3, 'Изделие 3', 'Описание 3', 25)") query.exec_(""" CREATE TABLE household (id INT(11) PRIMARY KEY, name VARCHAR(30) NOT NULL, description VARCHAR(70) NOT NULL, price FLOAT)""") query.exec_("INSERT INTO household (id, name, description, price) " "VALUES (1, 'Товар 1', 'Описание 1', 719.52)") query.exec_("INSERT INTO household (id, name, description, price) " "VALUES (2, 'Товар 2', 'Описание 2', 554.2)") query.exec_("INSERT INTO household (id, name, description, price) " "VALUES (3, 'Товар 3', 'Описание 3', 255)") query.exec_(""" CREATE TABLE purchases (id INT(11) NOT NULL, name VARCHAR(30) NOT NULL, price FLOAT)""") db.close()
Text information in field INTEGER SQLite
Software Programming • database sqlite • • morde

2

0
Votes

2
Posts

0
Views

N

From https://www.sqlite.org/datatype3.html :SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container.Any column in an SQLite version 3 database, except an INTEGER PRIMARY KEY column, may be used to store a value of any storage class.From https://www.sqlite.org/faq.html#q3 :SQLite lets me insert a string into a database column of type integer!This is a feature, not a bug. SQLite uses dynamic typing. It does not enforce data type constraints. Data of any type can (usually) be inserted into any column. You can put arbitrary length strings into integer columns, floating point numbers in boolean columns, or dates in character columns. The datatype you assign to a column in the CREATE TABLE command does not restrict what data can be put into that column. Every column is able to hold an arbitrary length string. (There is one exception: Columns of type INTEGER PRIMARY KEY may only hold a 64-bit signed integer. An error will result if you try to put anything other than an integer into an INTEGER PRIMARY KEY column.)But SQLite does use the declared type of a column as a hint that you prefer values in that format. So, for example, if a column is of type INTEGER and you try to insert a string into that column, SQLite will attempt to convert the string into an integer. If it can, it inserts the integer instead. If not, it inserts the string. This feature is called type affinity.
T

Output a 2 column table to a three column query
SQL, Database Testing • sql server 2012 • • tahishae

2

0
Votes

2
Posts

0
Views

N

In SQL Server 2012 you can use the new IIF() function: SELECT EmployeeName, Shift1 = COUNT(IIF(Shift=1,1,NULL)), Shift2 = COUNT(IIF(Shift=2,1,NULL)) FROM dbo.tablename GROUP BY EmployeeName; This is just shorthand for a CASE expression, so on older versions you could do it this way: SELECT EmployeeName, Shift1 = COUNT(CASE WHEN Shift = 1 THEN 1 END), Shift2 = COUNT(CASE WHEN Shift = 2 THEN 1 END) FROM dbo.tablename GROUP BY EmployeeName; You can also use PIVOT: SELECT EmployeeName, Shift1 = [1], Shift2 = [2] FROM dbo.TableName AS t PIVOT (COUNT(Shift) FOR Shift IN ([1],[2])) AS p; Now, none of these seems to produce the results in the same order you've defined in the question, and I don't know it's possible to produce that order reliably given the sample data you've shown. # SQLfiddle demo #
A

Like sqlite insert dict.
Software Programming • python sqlite • • Avante

2

0
Votes

2
Posts

0
Views

D

There are no extra wires actually in the line, they are added only when the line is removed in single carcasses to screen the inner single carcasses (and it was understood that the inner single cave does not end).Back to the vocabulary with help ast.literal_eval (a safer function than evalShe's just gonna figure out the data structure in the line, but she's not gonna do any kind of code.Example of convertation (at the inlet of the request as a list where each line is tuple from one element):import ast res = cursor.fetchall() dicts = [ast.literal_eval(row[0]) for row in res] It's also better to keep dictionaries in json form, so you won't have a clear connection to Python syntaxis, you'll be able to work with these data in almost every programming language. Example:import json Запись в базу for data in data_dict: created_at = data['created_at'] cursor.execute("INSERT INTO Market (data, created_at) VALUES(?, ?);", (json.dumps(data), created_at) conn.commit() Чтение из базы sql = "SELECT data FROM Market WHERE created_time >= ? AND created_time <= ?;" cursor.execute(sql, (begin_time, end_time)) res = cursor.fetchall() data = [json.loads(row[0]) for row in res]
D

Comparing Queries
SQL, Database Testing • sql server performance query performance • • derk

2

0
Votes

2
Posts

0
Views

C

Is it realistic to assume that the data in tableA and tableB will never be in cache? What percentage of queries run on the system will be run when all of the data has to be pulled from disk? There are two trains of thought on testing queries and comparing them: run them both with a cold cache (where you clear cache / buffers etc. before every single query) run them both with a warm cache (where you first prime the cache by running the queries multiple times before bothering to start comparing metrics) Since ideally in real-world scenarios you will be dealing with 2. most of the time, it seems relatively futile to spend too much effort on 1. Unless you are in a case where the production environment is really low on memory, in which case I would rather spend money on more memory than the labor costs of trying to compare and tune queries that are always going to be dependent on physical I/O. I would think that realistic testing of 2. is more useful than 1., again in most cases. If you really want to do this, I suppose you could forcefully fill up the buffer with data from other tables, supplanting the data from these two. Though I would be really concerned about tinkering this way too much with a system where you don't have sysadmin privileges (it's probably for exactly this reason, or at least one of the reasons). The other suggestion would be: take a backup of the database, and restore it to a system where you are sysadmin. This may be dealing with different I/O subsystems, but aside from the differences in pure I/O performance, the relative differences between the queries should still be useful to compare when you can free the cache and the buffers freely.
S

Very slow request with small difference
SQL, Database Testing • sqlite • • Saumya

2

0
Votes

2
Posts

0
Views

T

It would be interesting to know if there are indexes on your tables (and if there are, their structure as well). There is the slight possibility that these would help to understand the reason behind the third query being much slower than the second one. Well, this does not answer this mystery, but apart from the above, you should omit the Supplier table from your queries. Since you don't select any data from it, the only thing it does is multiplying the number of the rows selected (SELECT any_column FROM table1 t1, table2 t2 without further conditions on which t1 row to be joined to which t2 row produces the Cartesian product of the two tables - it can be written as table1 t1 CROSS JOIN table2 t2 in other RDBMSes, however in SQLite CROSS JOIN only fixes the order in which tables are processed). You apparently use SELECT DISTINCT to get rid of the multiplicated results (@ypercube, thanks for the suggestion). It would be interesting to see whether the speed difference is still there after these modifications. If the answer is 'yes', than again I would try to swap the two parts of the WHERE clause and check the difference again. (Well, SQLite seems to be quite smart avoiding plan gotchas like this, but you never can tell with bees.)

How to transfer the output of an SQLite query into a spreadsheet (Excel)

Suggested Topics

structural problem Software Programming • c language sqlite struct • • Rossere

How can I change the default route of a database on Android? Software Programming • android android studio sqlite android sqlite • • Demir

query processor ran out of internal resources - extremely long sql query SQL, Database Testing • sql server sql server 2012 • • meriah

Xamarin - SQLite Join Software Programming • c sharp android sqlite xamarin c languageross platform • • Kadir

Output another word instead of the word in the table in a query SQL, Database Testing • oracle • • magpie

Use name of table as input in procedure and store the output of SELECT statement in output variable SQL SQL, Database Testing • sql server sql server 2012 • • irl

How to handle big data update based on excel spreadsheet SQL, Database Testing • oracle excel update oracle 11g r2 • • kalena

Django storage of SQLite Software Programming • django sqlite • • kalyn

How to optimise this query? SQL, Database Testing • sql server performance query performance optimization • • yajahira

Respond to function: moving from one table to another Software Programming • python sqlite pyqt5 gui qtsql • • courtlanda

Text information in field INTEGER SQLite Software Programming • database sqlite • • morde

Output a 2 column table to a three column query SQL, Database Testing • sql server 2012 • • tahishae

Like sqlite insert dict. Software Programming • python sqlite • • Avante

Comparing Queries SQL, Database Testing • sql server performance query performance • • derk

Very slow request with small difference SQL, Database Testing • sqlite • • Saumya

structural problem
Software Programming • c language sqlite struct • • Rossere

How can I change the default route of a database on Android?
Software Programming • android android studio sqlite android sqlite • • Demir

query processor ran out of internal resources - extremely long sql query
SQL, Database Testing • sql server sql server 2012 • • meriah

Xamarin - SQLite Join
Software Programming • c sharp android sqlite xamarin c languageross platform • • Kadir

Output another word instead of the word in the table in a query
SQL, Database Testing • oracle • • magpie

Use name of table as input in procedure and store the output of SELECT statement in output variable SQL
SQL, Database Testing • sql server sql server 2012 • • irl

How to handle big data update based on excel spreadsheet
SQL, Database Testing • oracle excel update oracle 11g r2 • • kalena

Django storage of SQLite
Software Programming • django sqlite • • kalyn

How to optimise this query?
SQL, Database Testing • sql server performance query performance optimization • • yajahira

Respond to function: moving from one table to another
Software Programming • python sqlite pyqt5 gui qtsql • • courtlanda

Text information in field INTEGER SQLite
Software Programming • database sqlite • • morde

Output a 2 column table to a three column query
SQL, Database Testing • sql server 2012 • • tahishae

Like sqlite insert dict.
Software Programming • python sqlite • • Avante

Comparing Queries
SQL, Database Testing • sql server performance query performance • • derk

Very slow request with small difference
SQL, Database Testing • sqlite • • Saumya