pymssql
module reference¶
Complete documentation of pymssql
module classes, methods and properties.
Module-level symbols¶
Constants, required by the DB-API 2.0 (PEP 249) specification.
-
pymssql.
apilevel
¶ '2.0'
–pymssql
strives for compliance with DB-API 2.0.
-
pymssql.
paramstyle
¶ 'pyformat'
–pymssql
uses extended python format codes.
-
pymssql.
threadsafety
¶ 1
– Module may be shared, but not connections.
Functions¶
-
pymssql.
connect
(server='.', user='', password='', database='', timeout=0, login_timeout=60, charset='UTF-8', as_dict=False, host='', appname=None, port='1433')¶ Constructor for creating a connection to the database. Returns a
Connection
object.Parameters: - server (string) – database host
- user (string) – database user to connect as
- password (string) – user’s password
- database (string) – the database to initially connect to
- timeout (int) – query timeout in seconds, default 0 (no timeout)
- login_timeout (int) – timeout for connection and login in seconds, default 60
- charset (string) – character set with which to connect to the database
- as_dict (boolean) – whether rows should be returned as dictionaries instead of tuples
- appname (string) – Set the application name to use for the connection
- port (string) – the TCP port to use to connect to the server
-
pymssql.
get_dbversion
()¶ TBD
-
pymssql.
set_max_connections
(number)¶ Sets maximum number of simultaneous database connections allowed to be open at any given time. Default is 25.
-
pymssql.
get_max_connections
()¶ Gets current maximum number of simultaneous database connections allowed to be open at any given time.
Connection
class¶
-
class
pymssql.
Connection
(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
pymssql.connect()
. It accepts the following arguments. Note that in most cases you will want to use keyword arguments, instead of positional arguments.Parameters: - user (str) – Database user to connect as
- password (str) – User’s password
- host (str) –
Database host and instance you want to connect to. Valid examples are:
r'.\SQLEXPRESS'
– SQLEXPRESS instance on local machine (Windows only)r'(local)\SQLEXPRESS'
– same as above (Windows only)'SQLHOST'
– default instance at default port (Windows only)'SQLHOST'
– specific instance at specific port set up in freetds.conf (Linux/*nix only)'SQLHOST,1433'
– specified TCP port at specified host'SQLHOST:1433'
– the same as above'SQLHOST,5000'
– if you have set up an instance to listen on port 5000'SQLHOST:5000'
– the same as above
'.'
(the local host) is assumed if host is not provided. - database (str) – The database you want initially to connect to, by default SQL Server selects the database which is set as default for specific user
- timeout (int) – Query timeout in seconds, default is 0 (wait indefinitely)
- login_timeout (int) – Timeout for connection and login in seconds, default 60
- charset (str) – Character set with which to connect to the database
- as_dict (bool) – Whether rows should be returned as dictionaries instead of tuples. You can access columns by 0-based index or by name. Please see examples
Connection object properties¶
This class has no useful properties and data members.
Connection object methods¶
-
Connection.
autocommit
(status)¶ Where status is a boolean value. This method turns autocommit mode on or off.
By default, autocommit mode is off, what means every transaction must be explicitly committed if changed data is to be persisted in the database.
You can turn autocommit mode on, what means every single operation commits itself as soon as it succeeds.
-
Connection.
close
()¶ Close the connection.
-
Connection.
cursor
()¶ Return a cursor object, that can be used to make queries and fetch results from the database.
-
Connection.
commit
()¶ Commit current transaction. You must call this method to persist your data if you leave autocommit at its default value, which is
False
.See also pymssql examples.
-
Connection.
rollback
()¶ Roll back current transaction.
Cursor
class¶
-
class
pymssql.
Cursor
¶
This class represents a Cursor (in terms of Python DB-API specs) that is used to
make queries against the database and obtaining results. You create
Cursor
instances by calling cursor()
method on
an open Connection
connection object.
Cusor object properties¶
-
Cursor.
rowcount
¶ Returns number of rows affected by last operation. In case of
SELECT
statements it returns meaningful information only after all rows have been fetched.
-
Cursor.
connection
¶ This is the extension of the DB-API specification. Returns a reference to the connection object on which the cursor was created.
-
Cursor.
lastrowid
¶ This is the extension of the DB-API specification. Returns identity value of last inserted row. If previous operation did not involve inserting a row into a table with identity column,
None
is returned.
-
Cursor.
rownumber
¶ This is the extension of the DB-API specification. Returns current 0-based index of the cursor in the result set.
Cusor object methods¶
-
Cursor.
close
()¶ Close the cursor. The cursor is unusable from this point.
-
Cursor.
execute
(operation)¶ -
Cursor.
execute
(operation, params) operation is a string and params, if specified, is a simple value, a tuple, or
None
.Performs the operation against the database, possibly replacing parameter placeholders with provided values. This should be preferred method of creating SQL commands, instead of concatenating strings manually, what makes a potential of SQL Injection attacks. This method accepts the same formatting as Python’s builtin string interpolation operator.
If you call
execute()
with one argument, the%
sign loses its special meaning, so you can use it as usual in your query string, for example inLIKE
operator. See the examples.You must call
Connection.commit()
afterexecute()
or your data will not be persisted in the database. You can also setconnection.autocommit
if you want it to be done automatically. This behaviour is required by DB-API, if you don’t like it, just use the_mssql
module instead.
-
Cursor.
executemany
(operation, params_seq)¶ operation is a string and params_seq is a sequence of tuples (e.g. a list). Execute a database operation repeatedly for each element in parameter sequence.
-
Cursor.
fetchone
()¶ Fetch the next row of a query result, returning a tuple, or a dictionary if as_dict was passed to
pymssql.connect()
, orNone
if no more data is available. RaisesOperationalError
(PEP 249#operationalerror) if previous call toexecute*()
did not produce any result set or no call was issued yet.
-
Cursor.
fetchmany
(size=None)¶ Fetch the next batch of rows of a query result, returning a list of tuples, or a list of dictionaries if as_dict was passed to
pymssql.connect()
, or an empty list if no more data is available. You can adjust the batch size using the size parameter, which is preserved across many calls to this method. RaisesOperationalError
(PEP 249#operationalerror) if previous call toexecute*()
did not produce any result set or no call was issued yet.
-
Cursor.
fetchall
()¶ Fetch all remaining rows of a query result, returning a list of tuples, or a list of dictionaries if as_dict was passed to
pymssql.connect()
, or an empty list if no more data is available. RaisesOperationalError
(PEP 249#operationalerror) if previous call toexecute*()
did not produce any result set or no call was issued yet.
-
Cursor.
nextset
()¶ This method makes the cursor skip to the next available result set, discarding any remaining rows from the current set. Returns
True
value if next result is available,None
if not.
-
Cursor.
__iter__
()¶ -
Cursor.
next
()¶ These methods facilitate Python iterator protocol. You most likely will not call them directly, but indirectly by using iterators.
-
Cursor.
setinputsizes
()¶ -
Cursor.
setoutputsize
()¶ These methods do nothing, as permitted by DB-API specs.
Todo
Document all pymssql
PEP 249-mandated exceptions.