SQL: How to copy rows from one table to another
http://stackoverflow.com/questions/57168/how-to-copy-a-row-from-one-sql-server-table-to-another
Databases > MySQL
SHOW CREATE VIEW v
CREATE VIEW nloview AS
select nlo.id, nlo.word
from nlo left join forms on forms.word = nlo.word
where isnull(forms.word)
MySQL
MySQL JOIN / LEFT JOIN
https://www.codeproject.com/kb/database/visual_sql_joins.aspx
from:
Chapter 14
The Language of SQL, 2nd Edition
By Larry Rockoff
Published Jul 27, 2016 by Addison-Wesley Professional
http://www.informit.com/store/language-of-sql-9780134658254
As in all computer languages, there is frequently more than one way to specify
any desired objective.
p. 1
MySQL Foreign key
MySQL Foreign key
http://www.mysqltutorial.org/mysql-foreign-key
SQL > GROUP BY
SQL > GROUP BY
mysql-connector-python 2.1.5
The installed version (mysql-connector-python-2.1.5-py3.4-winx64.msi) is undetected by Oracle’s MySQL
…ending support for OS versions that are obsolete or have reached end of life, we plan to discontinue building all MySQL binaries for Microsoft Windows 7 as of June 23, 2016
http://www.mysql.com/support/eol-notice.html
MySQL 5.7.1 since ~ April 2013
mysql-connector-python 2.1.5
MySQL driver written in Python
https://pypi.python.org/pypi/mysql-connector-python is this page updated??
https://dev.mysql.com/downloads/connector/python/2.1.html
MySQL Connector/Python Developer Guide
http://dev.mysql.com/doc/connector-python/en/index.html
Chapter 1 Introduction to MySQL Connector/Python
http://dev.mysql.com/doc/connector-python/en/connector-python-introduction.html
up to and including MySQL Server version 5.7.
Chapter 3 Connector/Python Versions
http://dev.mysql.com/doc/connector-python/en/connector-python-versions.html
http://dev.mysql.com/doc/connector-python/en/connector-python-coding.html
Another recommendation is to declare a numeric primary key for each table, which offers the fastest way to look up values and can act as a pointer to associated values in other tables (a foreign key).
=======================
import mysql.connector
help(module)
dir(module)
mysql.connector.__version__
======================
For nonbuffered cursors, rows are not fetched from the server until a row-fetching method is called.
In this case, you must be sure to fetch all rows of the result set before executing any other statements on the same connection, or an InternalError (Unread result found) exception will be raised.
http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursorbuffered.html
solution: cnx.close()
“developer milestone”
https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-2.html
Duplicate values in a SQL table
mysql> use histology_db;
Database changed
mysql> SELECT word, count(word)
-> FROM dictionary
-> GROUP BY word
-> HAVING count(word) > 1;
CREATE VIEW duplicates AS …
CREATE VIEW verbs AS …
mysql> SELECT d.word_ID, LEFT(d.word, 10), d.category
-> FROM dictionary d
-> INNER JOIN (SELECT word
-> FROM dictionary
-> GROUP BY word
-> HAVING (count(word_ID) > 1) ) v
-> ON d.word = v.word;
+———+——————+———-+
| word_ID | LEFT(d.word, 10) | category |
+———+——————+———-+
| 15 | study | verb |
| 19 | study | noun |
| 57 | focus | noun |
| 160 | advance | noun |
| 183 | focus | verb |
| 193 | section | noun |
| 247 | section | verb |
| 343 | advance | Verb |
+———+——————+———-+
mysql> SELECT d.word_ID, LEFT(d.word, 10) AS multiple, d.category
-> FROM dictionary d
-> INNER JOIN (SELECT word
-> FROM dictionary
-> GROUP BY word
-> HAVING (count(word_ID) > 1) ) v
-> ON d.word = v.word
-> ORDER BY multiple, d.category;
http://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table
http://www.w3schools.com/sql/sql_join.asp
mysql> SELECT category, count(word_ID)
-> FROM dictionary
-> GROUP BY category;
+————–+—————-+
| category | count(word_ID) |
+————–+—————-+
| adjective | 58 |
| adverb | 8 |
| conjunction | 3 |
| determiner | 1 |
| noun | 201 |
| unclassified | 52 |
| verb | 61 |
+————–+—————-+
INNER JOIN
http://dev.mysql.com/doc/refman/5.6/en/example-foreign-keys.html
Finding a duplicate value, one of which has category=”unclassified”
MySQLCursor.fetchone()
9.5.8 Method MySQLCursor.fetchone()
http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-fetchone.html
# Using a while loop
cursor.execute(“SELECT * FROM employees”)
row = cursor.fetchone()
while row is not None:
___print(row)
___row = cursor.fetchone()
# Using the cursor as iterator
cursor.execute(“SELECT * FROM employees”)
for row in cursor:
___print(row)
MySQL String Functions
MySQL String Functions
http://dev.mysql.com/doc/refman/5.6/en/string-functions.html
CHAR_LENGTH(str)
Returns number of characters in argument.
mysql> select char_length(“ß”);
+——————-+
| char_length(“ß”) |
+——————-+
| 1 |
+——————-+
INSTR(str,substr)
Returns the position of the first occurrence of substring substr in string str.
This is the same as the two-argument form of LOCATE(), except that the order of the arguments is rev ersed.
LEFT(str, len)
Returns the leftmost len characters from the string str, or NULL if any argument is NULL.
LENGTH()
Returns the length of the string str, measured in bytes. A multibyte character counts as multiple bytes. This means that for a string containing five 2-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.
mysql> select length(“ß”);
+————–+
| length(“ß”) |
+————–+
| 2 |
+————–+
expr LIKE pat [ESCAPE ‘escape_char’]
Pattern matching using SQL simple regular expression comparison. Returns 1 (TRUE) or 0 (FALSE). If either expr or pat is NULL, the result is NULL.
mysql> SELECT “a” = “a “, “a” LIKE “a “;
+————+—————+
| “a” = “a ” | “a” LIKE “a ” |
+————+—————+
| 1 | 0 |
+————+—————+
With LIKE you can use the following two wildcard characters in the pattern. …
LOCATE()
mysql> SELECT word_ID, word, category
-> FROM dictionary
-> WHERE LOCATE(“solution”, word) > 0;
http://www.w3resource.com/mysql/string-functions/mysql-locate-function.php
Collation of Expressions
http://dev.mysql.com/doc/refman/5.6/en/charset-collation-expressions.html
MySQL
MySQL 3.6 Reference Manual
http://dev.mysql.com/doc/refman/5.6/en/index.html
used in batch mode: you place your queries in a file beforehand, then tell mysql to execute the contents of the file.
>SELECT USER();
root@localhost
> SHOW DATABASES;
http://dev.mysql.com/doc/refman/5.6/en/database-use.htm
> USE world;
> SHOW TABLES;
> DESCRIBE table_in_world;
> SELECT COUNT(*) FROM table_name;
> SELECT DATABASE();
==============================
http://dev.mysql.com/doc/refman/5.6/en/creating-database.html
==============================
String Types (BLOB or TEXT)
http://www.tutorialspoint.com/mysql/mysql-data-types.htm
https://wiki.python.org/moin/TkInter
https://wiki.python.org/moin/GuiProgramming
Python has a huge number of GUI frameworks (or toolkits) available for it, from TkInter (traditionally bundled with Python, using Tk
25.1. tkinter — Python interface to Tcl/Tk
https://docs.python.org/3.3/library/tkinter.html
SyntaxError: multiple statements found while compiling a single statement
When you see multiple statements are being declared, that means you’re seeing a script, which will be executed later. But in the interactive interpreter, you can’t do more than one statement at a time.
http://stackoverflow.com/questions/21226808/syntaxerror-multiple-statements-found-while-compiling-a-single-statement
finding tables with no PRIMARY KEY
https://www.simple-talk.com/sql/t-sql-programming/exploring-your-database-schema-with-sql
How to add a column
http://www.tech-recipes.com/rx/378/add-a-column-to-an-existing-mysql-table
===================
mysql> create table dictionary
(word_ID int(6) NOT NULL AUTO_INCREMENT,
word text NOT NULL,
category text NOT NULL,
concept int(1),
authority text,
notes text,
PRIMARY KEY (word_ID));
———————————————-
http://dev.mysql.com/doc/refman/5.1/en/alter-table-examples.html
CREATE TABLE t2 LIKE dictionary;
ALTER TABLE t2 MODIFY category text NOT NULL;
INSERT INTO t2 SELECT * FROM dictionary;
DROP TABLE dictionary;
ALTER TABLE t2 RENAME dictionary;
====================================
create table first3000
-> (id int(5) not null auto_increment,
-> word text not null,
-> category text not null,
-> for_db text,
-> concept int(1),
-> primary key (id));
=======================
http://www.w3resource.com/sql/aggregate-functions/sum-function.php
Limits on Table Size
http://dev.mysql.com/doc/refman/5.7/en/table-size-limit.html
Keywords and reserved words
https://dev.mysql.com/doc/refman/5.6/en/keywords.html
references
