recentpopularlog in

kme : sql   116

« earlier  
SQLite Describe Table
<code class="language-sql">pragma table_info('albums');</code>
sqlite  sql  newbie  dba  solution 
22 days ago by kme
sql - In MySQL: How to pass a table name as stored procedure and/or function argument? - Stack Overflow
<code class="language-sql">CREATE PROCEDURE `test1`(IN tab_name VARCHAR(40) )
SET @t1 =CONCAT('SELECT * FROM ',tab_name );
PREPARE stmt3 FROM @t1;
EXECUTE stmt3;
END $$</code>
sql  mysql  dba  preparedstatements  storedprocedures  solution 
august 2019 by kme
How Can I Sort A 'Version Number' Column Generically Using a SQL Server Query - Stack Overflow
For sorting by chromosome then start coordinate, I used:

<code class="language-sql">SELECT [...]
ORDER BY SUBSTRING(chr from 4) + 0 ASC, start</code>
sql  dba  mysql  solution 
august 2019 by kme
GitHub - alex/csv-sql: Query your CSV files with SQL
Query your CSV files with SQL. Contribute to alex/csv-sql development by creating an account on GitHub.
csv  sql  datamining  commandline  rust  textprocessing 
june 2019 by kme
python - SQLAlchemy default DateTime - Stack Overflow |
You could use '', but that will calculate a timestamp once per transaction, which may not be what you want.

This probably works fine:

<code class="language-python">
import datetime
from sqlalchemy import Column, Integer, DateTime
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Test(Base):
__tablename__ = 'test'

id = Column(Integer, primary_key=True)
created_date = Column(DateTime, default=datetime.datetime.utcnow)

python  sqlalchemy  sql  timeanddate  timestamp  dba  solution 
november 2018 by kme
sql - Can I concatenate multiple MySQL rows into one field? - Stack Overflow |
You can use GROUP_CONCAT:
<code class="language-sql">SELECT person_id, GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies GROUP BY person_id</code>
dba  sql  query  mysql  solution  fuckina 
august 2018 by kme
python - get table columns from sqlAlchemy table model - Stack Overflow |
You get all of the columns from __table__.columns:
<code class="language-python">myTable.__table__.columns</code>

<code class="language-python">myTable.__table__.c</code>

The columns would be in format myTable.col1 (table name is included). If you want just column names, get the .key for each column:
<code class="language-python">
[column.key for column in myTable.__table__.columns]
python  sqlalchemy  dba  sql  solution  reference  dammitbrain 
august 2018 by kme
Sqlalchemy: Truncate all tables |
In order to get the Table instance, use ModelName.__table__. The '.delete()' method on the Table is the same as a SQL 'TRUNCATE TABLE tablename', except you don't have to do any introspection (or hard-code) the table name.

<code class="language-python">
def truncate_db(engine):
# delete all table data (but keep tables)
# we do cleanup before test 'cause if previous test errored,
# DB can contain dust
meta = MetaData(bind=engine, reflect=True)
con = engine.connect()
trans = con.begin()
con.execute('SET FOREIGN_KEY_CHECKS = 0;')
for table in meta.sorted_tables:
con.execute('SET FOREIGN_KEY_CHECKS = 1;')
sqlalchemy  dba  database  sql  python  solution 
august 2018 by kme
Remove DEFINER clause from MySQL Dumps - Stack Overflow |
Solution for error messages like
ERROR 1449 (HY000) at line 1434: The user specified as a definer ('superman'@'%') does not exist
I don't think there is a way to ignore adding DEFINERs to the dump. But there are ways to remove them after the dump file is created.

- Open the dump file in a text editor and replace all occurrences of DEFINER=root@localhost with an empty string ""

- Edit the dump (or pipe the output) using perl:
<code class="language-bash">perl -p -i.bak -e "s/DEFINER=\`\w.*\`@\`\d[0-3].*[0-3]\`//g" mydatabase.sql</code>

- Pipe the output through sed:
<code class="language-bash">mysqldump ... | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > triggers_backup.sql</code>
mysql  sql  dba  annoyance  errormessage  solution 
july 2018 by kme
python - sqlalchemy IS NOT NULL select - Stack Overflow |
Starting in version 0.7.9 you can use the filter operator .isnot instead of comparing constraints, like this:
<code class="language-python">query.filter(</code>
python  sqlalchemy  flask-admin  sql  orm  solution 
june 2018 by kme
sql server 2008 r2 - Referencing a calculated column in the where clause SQL - Stack Overflow |
There is no way to use a calculated column as a WHERE condition. Either repeat the expression, use a derived table, or whatever.
dba  sql  mysql  sortof  solution 
april 2018 by kme
How to add levenshtein function in mysql? - Stack Overflow |
NB: ENDIF / ENDWHILE needs to be "END IF" / "END WHILE" in MySQL 5.x.

<code class="language-sql">DELIMITER $$

CREATE FUNCTION levenshtein( s1 varchar(255), s2 varchar(255) ) returns int deterministic
DECLARE s1_len,
cost int;
declare s1_char char;
-- max strlen=255
declare cv0,
cv1 varbinary(256);
set s1_len = char_length(s1),
s2_len = char_length(s2),
cv1 = 0x00,
j = 1,
i = 1,
c = 0;
if s1 = s2 THEN
elseif s1_len = 0 THEN
RETURN s2_len;
elseif s2_len = 0 THEN
RETURN s1_len;
WHILE j <= s2_len do
SET cv1 = concat(cv1, unhex(hex(j))),
j = j + 1;
endWHILE;WHILE i <= s1_len do
SET s1_char = substring(s1, i, 1),
c = i,
cv0 = unhex(hex(i)),
j = 1;WHILE j <= s2_len do
SET c = c + 1;IF s1_char = Substring(s2, j, 1) then
SET cost = 0;
SET cost = 1;ENDIF;SET c_temp = conv(hex(substring(cv1, j, 1)), 16, 10) + cost;IF c > c_temp then
SET c = c_temp;ENDIF;SET c_temp = conv(hex(substring(cv1, j+1, 1)), 16, 10) + 1;IF c > c_temp then
SET c = c_temp;ENDIF;SET cv0 = concat(cv0, unhex(hex(c))),
j = j + 1;ENDWHILE;SET cv1 = cv0,

DELIMITER ;</code>
dba  mysql  database  stringmatching  editdistance  sql  solution 
april 2018 by kme
permissions - MySQL error 1449: The user specified as a definer does not exist - Stack Overflow |
In my case, the mysql.proc table had definers from some other database, probably because I'd imported a SQL dump where I'd accidentally included stored procedures in addition to just the *one* table I wanted.

This fixes that.
How to change the definer for views

Run this SQL to generate the necessary ALTER statements

<code class="language-sql">SELECT CONCAT("ALTER DEFINER=`youruser`@`host` VIEW ",
table_name, " AS ", view_definition, ";")
FROM information_schema.views
WHERE table_schema='your-database-name';</code>
sql  mysql  dba  storedprocedures  errormessage  solution 
april 2018 by kme
python - Dealing with duplicate primary keys on insert in SQLAlchemy (declarative style) - Stack Overflow
You should handle every IntegrityError the same way: roll back the transaction, and optionally try again. Some databases won't even let you do anything more than that after an IntegrityError. You could also acquire a lock on the table, or a finer-grained lock if the database allows it, at the beginning of the two conflicting transactions.

Using the with statement to explicitly begin a transaction, and automatically commit (or rollback on any exception):
<code class="language-python">from schema import Session
from schema.models import Bike

session = Session()
with session.begin():
pk = 123 # primary key
bike = session.query(Bike).filter_by(bike_id=pk).first()
if not bike: # no bike in DB
new_bike = Bike(pk, "shiny", "bike")
sql  sqlalchemy  mysql  dba 
april 2018 by kme
MySQL Bugs: #5159: Views: Comments are not preserved |
This feature request is 18 years old now. People still express interest about this and other related feature requests. You may not consider it vital, but please don't understimate what Bruno Aquino Filardi Filho wrote: self documenting database is really important.
mysql  sql  views  documentation  comments  featurerequest  bug  watchthisspace 
april 2018 by kme
python - method of iterating over sqlalchemy model's defined columns? - Stack Overflow |
From here you can simply call JobStatus.__table__.columns._data.keys() which gives you a nice, clean list.

<code class="language-python">
from sqlalchemy import inspect
sqlalchemy  sql  dba  introspection  solution 
march 2018 by kme
mysql - Increment autoincrement id field by one - Stack Overflow |
I see no good reason for this. Only problems. Before running the folowing statement, check if you have FOREIGN keys defined, that reference this id. Are they set to ON UPDATE CASCADE? Also, do you have any triggers that are related to this table?

But first consider, why you (think you) need this. Is it going to be used for ordering the table? In that case, as @Mark pointed, you should use a separate column to specify your desired order.

If, however, you decide you really want this, use:
<code class="language-sql">
UPDATE myTable
SET id = id + 1
WHERE id >= 53
ORDER BY id DESC;</code>
mysql  sql  dba  primarykey  autoincrement  solution 
march 2018 by kme
MySql - Is primary key unique by default? - Stack Overflow |
Primary key is always unique in every SQL. You dont have to explicitly define it as UNIQUE.

On a side note: You can only have one Primary key in a table and it never allows null values. Also you can have only one primary key constraint in the table(as the point of creating a primary key is to uniquely identify the row in your table) but you can more than one unique key constraint in your table.


An employee details table having EmpID as Primary key and EmpPhoneNo as unique key.
database  dba  sql  newbie  question  answered  primarykey 
march 2018 by kme
mysql - Differences between "Unique Key" and "Primary Key" - Database Administrators Stack Exchange |
A table can have at most one PRIMARY KEY constraint but it can have as many as you want UNIQUE KEY constraints.

Columns that are part of the PRIMARY KEY must be defined as NOT NULL. That is not required for columns that are part of UNIQUE KEY constraints. If the columns are not Nullable, then there is no difference between Unique and Primary Keys.

Another minor difference is that you can choose the name you want for a UNIQUE KEY constraint (and index). On the other hand, the PRIMARY KEY has the default name: PRIMARY.

Also helpful:

Most important difference is in their purpose.

* Primary Key: Purpose of Primary Key is to act as a “KEY”. A primary key is a key in a relational database used to identify records.
* Unique Index: Unique Index is an “INDEX” intended for performance. Optimizer knows that for a condition "where x =:x” there will be only one record as a result – so it can prepare a plan suitable for that.
* Unique Constraint: It is a “CONSTRAINT” which makes sure that there are no duplicate values in that column. It is a constraint for data integrity.

Apart from their purpose, following points are noteworthy.

* Unless otherwise specified PRIMARY KEY will try to create a CLUSTERED INDEX (This point is about SQL Server though, as mentioned in the comment)
* There can be only one PRIMARY KEY per table; but there can be many unique constraints and unique indexes
* PRIMARY KEY is always not null but columns with unique constraint can hold NULL values
dba  primarykey  database  mysql  sql  thisvsthat  newbie  answered 
march 2018 by kme
mysql - Insert auto increment primary key to existing table - Stack Overflow |
This is interesting, but I don't know how it could work:

You can add a new Primary Key column to an existing table, which can have sequence numbers, using command:
<code class="language-sql">ALTER TABLE mydb.mytable ADD pk_columnName INT IDENTITY
dba  mysql  sql  newbie  syntax  primarykey  altertable  solution 
february 2018 by kme
sql - Update one MySQL table with values from another - Stack Overflow |

<code class="language-sql">UPDATE tobeupdated
INNER JOIN original ON (tobeupdated.value = original.value)
SET =</code>

That should do it, and really its doing exactly what yours is. However, I prefer 'JOIN' syntax for joins rather than multiple 'WHERE' conditions, I think its easier to read

As for running slow, how large are the tables? You should have indexes on tobeupdated.value and original.value

EDIT: we can also simplify the query
<code class="language-sql">UPDATE tobeupdated
INNER JOIN original USING (value)

USING is shorthand when both tables of a join have an identical named key such as id. ie an equi-join -
dba  sql  innerjoin  solution  newbie 
february 2018 by kme
MySQL's INSERT IGNORE INTO & foreign keys - Stack Overflow |
INSERT IGNORE turns INSERTS that don't meet a primary key constraint into warnings (try SHOW WARNINGS right after0.
dba  mysql  sql  newbie  solution 
february 2018 by kme
mysql - How do I find which transaction is causing a "Waiting for table metadata lock" state? - Stack Overflow |
All I actually had to do was stop the web server. The Flask-Admin web application had a lock on the table metadata, and I was trying to ALTER TABLE changing the data type/size of the columns. No go while the application was running, and that makes sense in retrospect.
If you cannot find the process locking the table (cause it is alreay dead), it may be a thread still cleaning up like this

section TRANSACTION of
<code class="language-sql">show engine innodb status;</code>

at the end
<code>---TRANSACTION 1135701157, ACTIVE 6768 sec
MySQL thread id 5208136, OS thread handle 0x7f2982e91700, query id 882213399 xxxIPxxx my_user cleaning up</code>

as mentionned in a comment in Clear transaction deadlock?

you can try killing the transaction thread directly, here with
<code class="language-sql">KILL 5208136;</code>

worked for me.
dba  mysql  sql  processmanagement  solution  flask-admin 
february 2018 by kme
MySQL Count distinct values from one column - Stack Overflow |
Have you tried
<code class="language-sql">SELECT COUNT(DISTINCT B) FROM mytable WHERE C = 2;</code>
dba  sql  syntax  mysql  dammitbrain  newbie  solution 
february 2018 by kme
Find duplicate records in MySQL - Stack Overflow |
The key is to rewrite this query so that it can be used as a subquery.
<code class="language-sql">
SELECT firstname,
FROM list
FROM list
GROUP BY address
HAVING COUNT(id) > 1) dup
ON list.address = dup.address;

Also useful
<code class="language-sql">SELECT date FROM logs group by date having count(*) >= 2

but note
Be careful with this answer. It returns only one of the duplicates. If you have more than 2 copies of the same record you wont see them all, and after deleting the record returned you will still have duplicates in your table.
dba  sql  mysql  datamunging  duplicates  solution 
february 2018 by kme
mysql command for showing current configuration variables - Stack Overflow |
What you are looking for is this:
<code class="language-sql">SHOW VARIABLES;</code>

You can modify it further like any query:
<code class="language-sql">SHOW VARIABLES LIKE '%max%';</code>
dba  mysql  sql  syntax  newbie  dammitbrain  solution 
january 2018 by kme
MySQL :: MySQL 5.6 Reference Manual :: 20.5.3 Updatable and Insertable Views |
I think MySQL Workbench (6.x, as of 31 January 2018) can't deal with updateable views, regardless. Sequel Pro works like a champ though.

See this related bug:
dba  mysql  sql  views  reference  updateableviews 
january 2018 by kme
sql - How to get the next auto-increment id in mysql - Stack Overflow |
Two solutions, but only the second works if you just TRUNCATEd the table:

<code class="language-sql">SELECT LAST_INSERT_ID()</code>

<code class="language-sql">
FROM information_schema.tables
WHERE table_name = 'table_name'
AND table_schema = DATABASE( ) ;

-- or if you do not wish to use information_schema you can use this

dba  mysql  sql  syntax  newbie  solution 
january 2018 by kme
mysql - How to truncate a foreign key constrained table? - Stack Overflow |
<code class="language-sql">SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE table1;
TRUNCATE table2;


This will handily reset the AUTO_INCREMENT as well, while maintaining the table structure and comments.
mysql  dba  sql  syntax  newbie  solution 
january 2018 by kme
php - SQL Update query based on another table's field - Stack Overflow |
In MySQL, you can use join with update. This is how I interpret your question:

<code class="language-sql">update t1_users u join
t2_settings s
on u.uid = s.value and = 'beta_givebeta'
set t1.beta = 'yes';</code>
sql  mysql  syntax  maybesolution 
january 2018 by kme
mysql - SQL set values of one column equal to values of another column in the same table - Stack Overflow |
Sounds like you're working in just one table so something like this:
<code class="language-sql">
update your_table
set B = A
where B is null</code>
mysql  sql  newbie  syntax  solution 
january 2018 by kme
sql - Can a stored procedure/function return a table? - Stack Overflow |
In MySQL 5.x, a stored procedure can't be in a FROM clause. Apparently this is OK in SQL Server and Postgres, though.
dba  sql  mysql  annoyance  hellopostgres 
january 2018 by kme
MySQL :: MySQL 5.7 Reference Manual :: 23.2.1 Stored Routine Syntax |
An alternative summary of functions versus procedures:

Inputs: Takes a list of arguments, all are inputs.
Could also get input from running a query?

Outputs: Produces a single return value -- see RETURN clause. Can be any valid MySQL data type.

(Can that return value be a result set? Other docs say that "functions cannot return a result set", but this probably means "cannot return a result set direct to the client", which is something _procedures_ can do. So far as I can tell, functions cannot return a cursor, which would be a related concept.)

Calling method: Can be used where a built-in function like ABS() might be used, such as in an assignment, expression, or where a field value is expected.

Inputs: Takes a list of arguments. Procedure definition specifies whether these arguments are for input and/or output, via IN, OUT or INOUT.

Could also get input from running a query.

Outputs: Unlike a Function, does not produce a return value per se. However, can send values back to caller via OUT or INOUT arguments.

Also, procedures can contain a bare SELECT statement, which sends a result set to the client. A procedure can contain multiple such SELECT statements, if other details enable the client to receive multiple result sets.
mysql  dba  sql  syntax  explained  solution  forthecomments 
january 2018 by kme
mysql - Using a stored procedure as subquery - Stack Overflow |
The gist: functions return a value; procedures can return values (for INOUT parameters) or results set(s) if there's a bare SELECT within them.
dba  mysql  sql  storedroutines  functions  procedures  syntax  newbie 
january 2018 by kme
Writing optional parameters within stored procedures in MySQL? - Stack Overflow |
Optional Parameters are not yet supported on MySQL. I'm suggesting that you pass null value in your parameter and inside your stored procedure has an IF statement.
<code class="language-sql">
(IN param VARCHAR(25))
-- statements ;
ELSE commands
-- statements ;
DELIMITER ;</code>
dba  sql  mysql  syntax  workaround  solution 
january 2018 by kme
Find duplicate records in MySQL - Stack Overflow |
<code class="language-sql">SELECT firstname,
FROM list
FROM list
GROUP BY address
HAVING COUNT(id) > 1) dup
ON list.address = dup.address;</code>
dba  sql  duplicates  solution 
january 2018 by kme
MySQL - create table by using another table |
<code class="language-sql">
SELECT * FROM other_table;

-- if you need to empty its contents, then:
3. Create a new table based on one or more existing tables, and at the same time create extra new column(s).

The query below is similar to the one shown above, but in the create table statement, it has specified a new column to be created with data type, not null constraint, and auto_increment attribute. In the new table product_sold_by_order2, all the columns in the SELECT statement are appended to the right side of the extra new column ID. This gives us the flexibility to add extra new columns without having to write and run another ALTER table query after the new table is created.

<code class="language-sql">
create table product_sold_by_order2(ID int not null auto_increment, PRIMARY KEY (ID))
select distinct y.OrderID,
round(y.UnitPrice * y.Quantity * (1 - y.Discount), 2) as TotalSales
from Products x
inner join Order_Details y on x.ProductID = y.ProductID
order by y.OrderID;
sql  mysql  dba  syntax  solution 
january 2018 by kme
Passing FieldName as Parameter in MySQL Stored Procedure - Stack Overflow |
You can use prepared statements, for example -

<code class="language-sql">
CREATE `VSK_Comments_UpdateAction`(IN FieldName varchar(30),IN FieldValue tinyint,CID bigint)
SET @query = CONCAT('Update comments Set ', FieldName, '=? WHERE commentid=?');
PREPARE stmt FROM @query;
SET @FieldValue = FieldValue;
EXECUTE stmt USING @FieldValue, @CID;
sql  mysql  procedure  syntax  dba  solution 
december 2017 by kme
python - Flask-SQLalchemy update a row's information - Stack Overflow |
How to do a SQL "UPDATE" with Flask-SQLAlchemy:
Retrieve an object using the tutorial shown in the Flask-SQLAlchemy documentation. Once you have the entity that you want to change, change the entity itself. Then, db.session.commit().

For example:

admin = User.query.filter_by(username='admin').first() = ''

user = User.query.get(5) = 'New Name'

Flask-SQLAlchemy is based on SQLAlchemy, so be sure to check out the SQLAlchemy Docs as well.
flask  sqlalchemy  sql  dammitbrain  webdevel  solution 
september 2017 by kme
mysql - Using backticks around field names - Stack Overflow |
It looks like backticks are used for quoting identifiers (column names) rather than string literals (stuff you'd want to save in the database).

Problem is, not ANSI-SQL compliant.
sql  mysql  syntax  dammitbrain  newbie  reference  solution 
september 2017 by kme
sqlacodegen bug ? - Google Groups
I have this same problem and no one answered this guy.

Basically, sqlacodegen makes Table variables rather than proper classes whenever the table doesn't contain a primary key.
sqlalchemy  annoyance  sql  python  watchthisspace  needshelp 
june 2017 by kme
database - Import csv into sqlite with autoincrementing primary key - Stack Overflow
An empty field in a CSV file is just an empty string, which is not valid for an INTEGER PRIMARY KEY column.

Import into a temporary table without that column, then copy the data over with:

INSERT INTO Allegiance(CharacterID, Title) SELECT * FROM TempTable;
sqlite  sql  newbie  syntax  solution 
june 2017 by kme
innodb - How can I check MySQL engine type for a specific table? - Stack Overflow

This will give you (among other things) an Engine column, which is what you want.

Also works:

To show a list of all the tables in a database and their engines, use this SQL query:

FROM information_schema.TABLES

Replace dbname with your database name.

And, even more concise:

SHOW CREATE TABLE <tablename>;

Less parseable but more readable that SHOW TABLE STATUS.
dba  mysql  sql  solution 
january 2017 by kme
Remove the binding to ^D by oylenshpeegul · Pull Request #40 · ewaters/altsql-shell · GitHub
Solution: remove the lines in lib/perl5/App/AltSQL/ that look like:

<code class="language-perl">
$term->bindkey('^D', sub {
print "\n";
altsql  sql  mysql  annoyance  solution  keybinding 
january 2017 by kme
sql - Drop MySQL databases matching some wildcard? - Stack Overflow
Note that this doesn't work in AltSQL, which gets hung up on the 'DELIMITER //' line.

We can do this with stored procedures. Here is one below:
<code class="language-perl">
drop procedure if exists droplike;
delimiter //
create procedure droplike(pattern varchar(20))
set group_concat_max_len = 65535;
select @drop:= concat( 'drop table ', group_concat(table_name) , ';' ) from information_schema.tables where table_schema = "database_name" and table_name like pattern;
prepare statement from @drop;
execute statement;
end //
delimiter ;
mysql  sql  dba  maintenance  solution 
january 2017 by kme
« earlier      
per page:    204080120160

Copy this bookmark:

to read