Databases > MySQL

Copy rows from one table to another

Posted by

0

copy_rows_fromSQL: 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

Advertisement

mysql-connector-python 2.1.5

Posted by

0

connector_invisibleThe installed version (mysql-connector-python-2.1.5-py3.4-winx64.msi) is undetected by Oracle’s MySQL

myqlconnector

connector…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()

python34lib
python36lib

“developer milestone”
https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-2.html

Duplicate values in a SQL table

Posted by

0

duplicatesmysql> use histology_db;
Database changed
mysql> SELECT word, count(word)
-> FROM dictionary
-> GROUP BY word
-> HAVING count(word) > 1;

create_viewCREATE VIEW duplicates AS …

create_view_verbsCREATE VIEW verbs AS …

duplicate_verbsmysql> 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 |
+———+——————+———-+

beam_of_electronsmysql> 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

duplicate_values_and_conditionFinding a duplicate value, one of which has category=”unclassified”

MySQL String Functions

Posted by

0

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_all-words_LIKE

MySQL_LIKE-BINARY

MySQL_bit_length

MySQL_is_blind_to_angstroms

MySQL_angstrom

MySQL

Posted by

0

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