Руководство по подключение к экземпляру sql server и выполнение запросов с помощью sql server management studio (ssms)tutorial: connect to and query a sql server instance by using sql server management studio (ssms)

Important note about Cursors¶

A connection can have only one cursor with an active query at any time. If you have used other Python DBAPI databases, this can lead to surprising results:

c1 = conn.cursor()
c1.execute('SELECT * FROM persons')

c2 = conn.cursor()
c2.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')

print( "all persons" )
print( c1.fetchall() )  # shows result from c2 query!

print( "John Doe" )
print( c2.fetchall() )  # shows no results at all!

In this example, the result printed after will be the result of the second query (the list where ) and the result printed after “John Doe” will be empty. This happens because the underlying TDS protocol does not have client side cursors. The protocol requires that the client flush the results from the first query before it can begin another query.

(Of course, this is a contrived example, intended to demonstrate the failure mode. Actual use cases that follow this pattern are usually much more complicated.)

Here are two reasonable workarounds to this:


SET

The command is used with UPDATE to specify which columns and values that should be updated in a table.

The following SQL updates the first customer (CustomerID = 1) with a new ContactName and a new City:

Example

UPDATE Customers SET ContactName = ‘Alfred Schmidt’, City= ‘Frankfurt’ WHERE CustomerID = 1;

The following SQL will update the «ContactName» field to «Juan» for all records where Country is «Mexico»:

Example


UPDATE Customers SET ContactName=’Juan’ WHERE Country=’Mexico’;

Note: Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated!

SQL References

SQL Keywords ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE

MySQL Functions String Functions ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE

Date Functions ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK

Advanced Functions BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION

SQL Server Functions String Functions ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN

Date Functions CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR

Advanced Functions CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME

MS Access Functions String Functions Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase

Numeric Functions Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val

Date Functions Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year

Other Functions CurrentUser Environ IsDate IsNull IsNumeric

SQL OperatorsSQL Data TypesSQL Quick Ref

SQL Справочник

SQL Ключевые слова ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE

MySQL Функции Функции строк ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER Функции чисел ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE Функции дат ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK Функции расширений BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION

SQL Server функции Функции строк ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER Функции чисел ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN Функции дат CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR Функции расширений CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME

MS Access функции Функции строк Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase Функции чисел Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val Функции дат Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year Другие функции CurrentUser Environ IsDate IsNull IsNumeric


SQL ОператорыSQL Типы данныхSQL Краткий справочник

Exceptions¶

exception

Root of the exception hierarchy.

exception

Raised for important warnings like data truncations while inserting, etc. A subclass of .

exception

Base class of all other error exceptions. You can use this to catch all errors with one single except statement. A subclass of .

exception

Raised for errors that are related to the database interface rather than the database itself. A subclass of .

exception

Raised for errors that are related to the database. A subclass of .

exception

Raised for errors that are due to problems with the processed data like division by zero, numeric value out of range, etc. A subclass of .

exception

Raised for errors that are related to the database’s operation and not necessarily under the control of the programmer, e.g. an unexpected disconnect occurs, the data source name is not found, a transaction could not be processed, a memory allocation error occurred during processing, etc. A subclass of .

exception

Raised when the relational integrity of the database is affected, e.g. a foreign key check fails. A subclass of .

exception

Raised when the database encounters an internal error, e.g. the cursor is not valid anymore, the transaction is out of sync, etc. A subclass of .

exception

Raised for programming errors, e.g. table not found or already exists, syntax error in the SQL statement, wrong number of parameters specified, etc. A subclass of .

exception

Raised in case a method or database API was used which is not supported by the database, e.g. requesting a on a connection that does not support transaction or has transactions turned off. A subclass of .

exception

Raised by when has been specified to the and the query sent to the server doesn’t involve columns names in its results. A subclass of .


Note

isn’t a PEP-249-mandated exception but rather a pymssql extension.

Connection class¶

class (user, password, host, database, timeout, login_timeout, charset, as_dict)

This class represents an MS SQL database connection. You can create an instance of this class by calling constructor . It accepts the following arguments. Note that in most cases you will want to use keyword arguments, instead of positional arguments.

Parameters:
  • user () – Database user to connect as
  • password () – User’s password
  • host () –

    Database host and instance you want to connect to. Valid examples are:

    • – SQLEXPRESS instance on local machine (Windows only)
    • – same as above (Windows only)
    • – default instance at default port (Windows only)
    • – specific instance at specific port set up in freetds.conf (Linux/*nix only)
    • – specified TCP port at specified host
    • – the same as above
    • – if you have set up an instance to listen on port 5000
    • – the same as above

    (the local host) is assumed if host is not provided.

  • database () – The database you want initially to connect to, by default SQL Server selects the database which is set as default for specific user
  • timeout () – Query timeout in seconds, default is 0 (wait indefinitely)
  • login_timeout () – Timeout for connection and login in seconds, default 60
  • charset () – Character set with which to connect to the database
  • as_dict () – Whether rows should be returned as dictionaries instead of tuples. You can access columns by 0-based index or by name. Please see examples

С этим читают