|
Cursor
Cursor API documentation
Cursors represent a database cursor (and map to ODBC HSTMTs), which is used to manage the context of a fetch operation. Cursors created from the same connection are not isolated, i.e., any changes done to the database by a cursor are immediately visible by the other cursors. variablesdescriptionThis read-only attribute is a list of 7-item tuples, each containing (name, type_code, display_size, internal_size, precision, scale, null_ok). pyodbc only provides values for name, type_code, internal_size, and null_ok. The other values are set to None. This attribute will be None for operations that do not return rows or if one of the execute methods has not been called. The type_code member is the class type used to create the Python objects when reading rows. For example, a varchar column's type will be str. rowcountThe number of rows modified by the previous DDL statement. This is -1 if no SQL has been executed or if the number of rows is unknown. Note that it is not uncommon for databases to report -1 after a select statement for performance reasons. (The exact number may not be known before the first records are returned to the application.) methodsexecutecursor.execute(sql, *parameters) --> Cursor Prepares and executes SQL. The optional parameters may be passed as a sequence, as specified by the DB API, or as individual values. # standard
cursor.execute("select a from tbl where b=? and c=?", (x, y))
# pyodbc extension
cursor.execute("select a from tbl where b=? and c=?", x, y)The return value is always the cursor itself: for row in cursor.execute("select user_id, user_name from users"):
print row.user_id, row.user_name
row = cursor.execute("select * from tmp").fetchone()
rows = cursor.execute("select * from tmp").fetchall()
count = cursor.execute("update users set last_logon=? where user_id=?", now, user_id).rowcount
count = cursor.execute("delete from users where user_id=1").rowcountAs suggested in the DB API, the last prepared statement is kept and reused if you execute the same SQL again, makingexecuting the same SQL with different parameters will be more efficient. executemanycursor.executemany(sql, seq_of_parameters) --> None Executes the same SQL statement for each set of parameters. seq_of_parameters is a sequence of sequences. params = [ ('A', 1), ('B', 2) ]
executemany("insert into t(name, id) values (?, ?)", params)This will execute the SQL statement twice, once with ('A', 1) and once with ('B', 2). fetchonecursor.fetchone() --> Row or None Returns the next row or None when no more data is available. A ProgrammingError exception is raised if no SQL has been executed or if it did not return a result set (e.g. was not a SELECT statement). cursor.execute("select user_name from users where user_id=?", userid)
row = cursor.fetchone()
if row:
print row.user_namefetchallcursor.fetchall() --> list of rows Returns a list of all remaining rows. Since this reads all rows into memory, it should not be used if there are a lot of rows. Consider iterating over the rows instead. However, it is useful for freeing up a Cursor so you can perform a second query before processing the resulting rows. A ProgrammingError exception is raised if no SQL has been executed or if it did not return a result set (e.g. was not a SELECT statement). cursor.execute("select user_id, user_name from users where user_id < 100")
rows = cursor.fetchall()
for row in rows:
print row.user_id, row.user_namefetchmanycursor.fetchmany([size=cursor.arraysize]) --> list Returns a list of remaining rows, containing no more than size rows, used to process results in chunks. The list will be empty when there are no more rows. The default for cursor.arraysize is 1 which is no different than calling fetchone(). A ProgrammingError exception is raised if no SQL has been executed or if it did not return a result set (e.g. was not a SELECT statement). commitcursor.commit() --> None Commits pending transactions on the connection that created this cursor. This affects all cursors created by the same connection! This is no different than calling commit on the connection. The benefit is that many uses can now just use the cursor and not have to track the connection. rollbackcursor.rollback() --> None Rolls back pending transactions on the connection that created this cursor. This affects all cursors created by the same connection! skipcursor.skip(count) --> None Skips the next count records by calling SQLFetchScroll with SQL_FETCH_NEXT. For convenience, skip(0) is accepted and will do nothing. nextsetcursor.nextset() --> True or None This method will make the cursor skip to the next available set, discarding any remaining rows from the current set. If there are no more sets, the method returns None. Otherwise, it returns a true value and subsequent calls to the fetch methods will return rows from the next result set. This method is primarily used if you have stored procedures that return multiple results. close()Closes the cursor. A ProgrammingError exception will be raised if any operation is attempted with the cursor. Cursors are closed automatically when they are deleted, so calling this is not usually necessary when using CPython. setinputsizes, setoutputsizeThese are optional in the API and are not supported. callproc(procname[,parameters])This is not yet supported since there is no way for pyodbc to determine which parameters are input, output, or both. You will need to call stored procedures using execute(). You can use your database's format or the ODBC escape format. tablescursor.tables(table=None, catalog=None, schema=None, tableType=None) --> Cursor Creates a result set of tables in the database that match the given criteria. The table, catalog, and schema interpret the '' and '%' characters as wildcards. The escape character is driver specific, so use Connection.searchescape. Each row has the following columns. See the SQLTables documentation for more information.
for row in cursor.tables():
print row.table_name
# Does table 'x' exist?
if cursor.tables(table='x').fetchone():
print 'yes it does'columnscursor.columns(table=None, catalog=None, schema=None, column=None) --> Cursor Creates a result set of column information in the specified tables using the SQLColumns function. Each row has the following columns:
# columns in table x
for row in cursor.columns(table='x'):
print row.column_namestatisticscursor.statistics(table, catalog=None, schema=None, unique=False, quick=True) --> Cursor Creates a result set of statistics about a single table and the indexes associated with the table by executing SQLStatistics. If code unique is True only unique indexes are returned; if False all indexes are returned. If quick is True, CARDINALITY and PAGES are returned only if they are readily available. Otherwise NULL is returned on those colummns. Each row has the following columns:
rowIdColumnscursor.rowIdColumns(table, catalog=None, schema=None, nullable=True) --> Cursor Executes SQLSpecialColumns with SQL_BEST_ROWID which creates a result set of columns that uniquely identify a row. Each row has the following columns.
rowVerColumnscursor.rowVerColumns(table, catalog=None, schema=None, nullable=True) --> Cursor Executes SQLSpecialColumns with SQL_ROWVER which creates a result set of columns that are automatically updated when any value in the row is updated. Returns the Cursor object. Each row has the following columns.
primaryKeysprimaryKeys(table, catalog=None, schema=None) --> Cursor Creates a result set of column names that make up the primary key for a table by executing the SQLPrimaryKeys function. Each row has the following columns:
foreignKeyscursor.foreignKeys(table=None, catalog=None, schema=None, foreignTable=None, foreignCatalog=None, foreignSchema=None) --> Cursor Executes the SQLForeignKeys function and creates a result set of column names that are foreign keys in the specified table (columns in the specified table that refer to primary keys in other tables) or foreign keys in other tables that refer to the primary key in the specified table. Each row has the following columns:
procedurescursor.procedures(procedure=None, catalog=None, schema=None) --> Cursor Executes SQLProcedures and creates a result set of information about the procedures in the data source. Each row has the following columns:
getTypeInfocursor.getTypeInfo(sqlType=None) --> Cursor Executes SQLGetTypeInfo a creates a result set with information about the specified data type or all data types supported by the ODBC driver if not specified. Each row has the following columns:
|
The executemany example should be cursor.executemany()
and for completeness, cnxn.commit() should be added to the example as well
I should of done some more research before littering the wiki, here's how I've accomplished what I wanted to do. I wanted to get a result set and then not only iterate through the data set returned but also through each column in the data set. Cursor.description is what I must of missed:
connection = pyodbc.connect("DSN=%s;UID=%s;PWD=%s" % (dsn, uid, pwd)) cursor = connection.cursor() cursor.execute(""" select 1 AS "TESTCOLUMN1", 2 AS "TESTCOLUMN2" from sysibm.sysdummy1 """) for row in cursor.fetchall(): # This is the part i'm trying to do with no luck: columnOrdinal = 0 for column in cursor.description: print "Column name is %s " % column[0] , print "Column value is %s " % row[columnOrdinal] columnOrdinal += 1Because extracting the column names is so common, I'm going to (1) provide a direct list of them and (2) return a named tuple for description.
In the meantime, this might be helpful too:
I'm new to this and I need help. Is there a way to get query from cursor? For example, cursor.execute('select name from contacts where name like ?', 'john')
I want to print 'select name from contacts where name like john'
Thank you in advance
You sure can, you can do all kinds of where conditions!
cursor.execute("select from " + filename + " limit 1") being a variable?
cursor.execute("select name from contacts where name like john") case?
the list comprehension above probably should read:
cols = [t[0] for t in cursor.description]
Is there an easy way to find the number of columns in a table?
Thanks in advance.
Does anyone know of a way to copy a table from 1 DB to another?
The documentation on the columns(...) function appears inaccurate.
Running the function against SQL Server's Adventure Works dataset gives:
The Pyodbc documentation states that "is_nullable: One of SQL_NULLABLE, SQL_NO_NULLS, SQL_NULLS_UNKNOWN", however the columns function appears to return a string of either "YES" or "NO", as per Microsoft's documentation on the function (http://msdn.microsoft.com/en-us/library/ms711683%28VS.85%29.aspx).
In fact, according to Microsoft's documentation, SQL_NULLABLE, SQL_NO_NULLS and SQL_NULLS_UNKNOWN are actually used for the "nullable" column.
I am also unsure why there is one more column returned (56 in the example above) than both Pyodbc's and Microsoft's documentation suggests.
BUG:
If using:
cursor.execite(""" SELECT FROM table WHERE 1=1 print('test print') """)You will get a "pyodbc.ProgrammingError??: No results. Previous SQL was not a query." so avoid print() at any cost.
"SELECT FROM table" is not a SQL Server Query, you need to put the column names between "SELECT" and "FROM"
Hi, how can i RESTORE a database? I'm trying to use this code:
restore=self.connect.cursor().execute(""" USE master; RESTORE DATABASE %s FROM DISK='%s' WITH REPLACE; """ % (database,file_path)) while restore.nextset(): pass restore.commit()but nothing happing, no error occur. What's the problem?
Getting below error when running a SELECT query via cursor.execute()
pyodbc.Error: ('HY000', "HY000? DataDirect?Sybase Wire Protocol driver? Server?Attempt to BEGIN TRANSACTION in database 'hist_db' failed because database is READ ONLY.\n (3906) (SQLExecDirectW)")
The database is set to readonly due to regulatory reasons, the same SELECT query can be run from other DB client, even from vbs script. Does this pyodbc cursor support lock type adLockReadOnly like ADODB does?
Getting below error:
when trying to get the result from:Looks like your stored procedure doesn't return anything. Check cursor.description after executing.
Need to use to 2 connections, one to DB1 and other to DB2.
Do something like "INSERT INTO DB2.TABLE1 SELECT FROM DB1.TABLE1"?
Is it possible?
The documentation of the cursor description attribute seems to be incorrect. It states that "pyodbc only provides values for name, type_code, internal_size, and null_ok. The other values are set to None." but I've seen in my code that all seem to be provided except display_size.
Is this correct, or can we not rely on those values?
You will get a "pyodbc.ProgrammingError??? http://wdfshare.blogspot.com
the solutions here are really useful. I am kinda stocked with getting around my code. forgive any silly questions as i am new to mysql. I tried running this code and gets stocked with "cursor.execute(sql,tuple(args))"
>def back_pack(cursor,sql,args): >>>>>rowLst= >>>>>if args==: >>>>>>>>>cursor.excute(sql) >>>>>>>else: >>>>>>cursor.execute(sql,tuple(args)) >>>>>>>>>columnlst=zip(cursor.description)0?
and i get an error of : mysql_exceptions.ProgrammingError?: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use cursor.execute(sql,tuple(args)).
http://anekajaringmurah.blogspot.com/ http://pusatjaringmurah.blogspot.com/ http://anekajaringpengaman.blogspot.com/ http://agenjaringpengaman.blogspot.com/ http://jaringpengamanfutsal.blogspot.com/ http://jaring-pengamanmurah.blogspot.com/ http://jaringcenter.blogspot.com/ http://agenjaringjakata.blogspot.com/ http://jualjaringpengamanmurah.blogspot.com/ http://jaringsafetyjakarta.blogspot.com/ http://jaringpengaman-murah.blogspot.com/ http://jaringmurah.blogspot.com/ http://jaring-murah.blogspot.com/ http://jaringpengamanmurah.blogspot.com/ http://jaringbangunan.blogspot.com/ http://agenjaringsafety.blogspot.com/ http://sentral-jaring.blogspot.com/ http://sentraljaring.blogspot.com/ http://tokojaringpengaman.blogspot.com/ http://pusatjaringjakarta.blogspot.com/ http://tokojaringpengamanmurah.blogspot.com/ http://jualjaringsafetymurah.blogspot.com/ https://pancasamudera.wordpress.com/ https://pasangjaringfutsal.wordpress.com/ https://jualtambangmurah.wordpress.com/ https://tokojaring.wordpress.com/ https://jualjaringfutsal.wordpress.com/ https://jaringfutsal.wordpress.com/
Can we not create a table using select into where 1=2, like below:
select into tempdb5..test from tempdb..test where 1=2
Throws below error: Sybase.DatabaseError??: Msg 3803, Level 16, State 1 The statement used to define the cursor 'ctmp2b3185190758' is neither a SELECT nor an EXECUTE
database: DB2 on an AS400 server odbc datasource Iseries Access Driver OS: windows server 2012 r2 when i execute the following line con.cursor("{CALL LIBRARYNAME.SOMESPNAME('arg1')}")
i get this error No results. Previous SQL was not a query.