pyodbc
Python ODBC bridge
pyodbc
Has anybody got recent experience with deploying a Django application with an SQL Server database back end? Our workplace is heavily invested in SQL Server and will not support Django if there isn't a sufficiently developed back end for it.
I'm aware of mssql.django-pyodbc and django-mssql as unofficially supported back ends. Both projects seem to have only one person contributing which is a bit of a worry though the contributions seem to be somewhat regular.
Are there any other back ends for SQL Server that are well supported? Are the two I mentioned here 'good enough' for production? What are your experiences?
Source: (StackOverflow)
Using virtualenv and just trying to install pyodbc. All resources I can find claim this should be extremely straightforward. After all the basic installs of MySQL, etc., just do:
pip install pyodbc
However, I am seeing a very strange error. It has nothing (as far as I can tell) to do with missing libraries, and after Googling for this sort of error for a long time, I can't find anything constructive on it at all.
(local-dev)espears@espears-w ~ $ pip install pyodbc
Downloading/unpacking pyodbc
Could not find any downloads that satisfy the requirement pyodbc
Some externally hosted files were ignored (use --allow-external pyodbc to allow).
Cleaning up...
No distributions at all found for pyodbc
Storing debug log for failure in /home/espears/.pip/pip.log
So I tried with the "allow-external" option and it does not help:
(local-dev)espears@espears-w ~ $ pip install --allow-external pyodbc
You must give at least one requirement to install (see "pip help install")
But the help documentation makes it appear that I am using this option correctly, e.g. from the output of running pip help install:
Package Index Options:
...
--allow-external <package> Allow the installation of externally hosted files
Here's the result in the PIP log file:
(local-dev)espears@espears-w ~ $ cat /home/espears/.pip/pip.log
Downloading/unpacking pyodbc
Getting page https://pypi.python.org/simple/pyodbc/
URLs to search for versions for pyodbc:
* https://pypi.python.org/simple/pyodbc/
Analyzing links from page https://pypi.python.org/simple/pyodbc/
Skipping link http://code.google.com/p/pyodbc (from https://pypi.python.org/simple/pyodbc/); not a file
Skipping link http://code.google.com/p/pyodbc/downloads/list (from https://pypi.python.org/simple/pyodbc/); not a file
Not searching http://code.google.com/p/pyodbc (from https://pypi.python.org/simple/pyodbc/) for files because external urls are disallowed.
Not searching http://code.google.com/p/pyodbc (from https://pypi.python.org/simple/pyodbc/) for files because external urls are disallowed.
Not searching http://code.google.com/p/pyodbc (from https://pypi.python.org/simple/pyodbc/) for files because external urls are disallowed.
Not searching http://code.google.com/p/pyodbc (from https://pypi.python.org/simple/pyodbc/) for files because external urls are disallowed.
Not searching http://code.google.com/p/pyodbc (from https://pypi.python.org/simple/pyodbc/) for files because external urls are disallowed.
Not searching http://code.google.com/p/pyodbc (from https://pypi.python.org/simple/pyodbc/) for files because external urls are disallowed.
Not searching http://code.google.com/p/pyodbc (from https://pypi.python.org/simple/pyodbc/) for files because external urls are disallowed.
Not searching http://code.google.com/p/pyodbc (from https://pypi.python.org/simple/pyodbc/) for files because external urls are disallowed.
Not searching http://code.google.com/p/pyodbc (from https://pypi.python.org/simple/pyodbc/) for files because external urls are disallowed.
Not searching http://code.google.com/p/pyodbc (from https://pypi.python.org/simple/pyodbc/) for files because external urls are disallowed.
Not searching http://code.google.com/p/pyodbc (from https://pypi.python.org/simple/pyodbc/) for files because external urls are disallowed.
Not searching http://code.google.com/p/pyodbc (from https://pypi.python.org/simple/pyodbc/) for files because external urls are disallowed.
Not searching http://code.google.com/p/pyodbc/downloads/list (from https://pypi.python.org/simple/pyodbc/) for files because external urls are disallowed.
Not searching http://code.google.com/p/pyodbc/downloads/list (from https://pypi.python.org/simple/pyodbc/) for files because external urls are disallowed.
Not searching http://code.google.com/p/pyodbc/downloads/list (from https://pypi.python.org/simple/pyodbc/) for files because external urls are disallowed.
Not searching http://code.google.com/p/pyodbc/downloads/list (from https://pypi.python.org/simple/pyodbc/) for files because external urls are disallowed.
Not searching http://code.google.com/p/pyodbc/downloads/list (from https://pypi.python.org/simple/pyodbc/) for files because external urls are disallowed.
Not searching http://code.google.com/p/pyodbc/downloads/list (from https://pypi.python.org/simple/pyodbc/) for files because external urls are disallowed.
Not searching http://code.google.com/p/pyodbc/downloads/list (from https://pypi.python.org/simple/pyodbc/) for files because external urls are disallowed.
Not searching http://code.google.com/p/pyodbc/downloads/list (from https://pypi.python.org/simple/pyodbc/) for files because external urls are disallowed.
Not searching http://code.google.com/p/pyodbc/downloads/list (from https://pypi.python.org/simple/pyodbc/) for files because external urls are disallowed.
Not searching http://code.google.com/p/pyodbc/downloads/list (from https://pypi.python.org/simple/pyodbc/) for files because external urls are disallowed.
Not searching http://code.google.com/p/pyodbc/downloads/list (from https://pypi.python.org/simple/pyodbc/) for files because external urls are disallowed.
Not searching http://code.google.com/p/pyodbc/downloads/list (from https://pypi.python.org/simple/pyodbc/) for files because external urls are disallowed.
Could not find any downloads that satisfy the requirement pyodbc
Some externally hosted files were ignored (use --allow-external pyodbc to allow).
Cleaning up...
Removing temporary dir /home/espears/.virtualenvs/local-dev/build...
No distributions at all found for pyodbc
Exception information:
Traceback (most recent call last):
File "/home/espears/.virtualenvs/local-dev/local/lib/python2.7/site-packages/pip/basecommand.py", line 122, in main
status = self.run(options, args)
File "/home/espears/.virtualenvs/local-dev/local/lib/python2.7/site-packages/pip/commands/install.py", line 270, in run
requirement_set.prepare_files(finder, force_root_egg_info=self.bundle, bundle=self.bundle)
File "/home/espears/.virtualenvs/local-dev/local/lib/python2.7/site-packages/pip/req.py", line 1157, in prepare_files
url = finder.find_requirement(req_to_install, upgrade=self.upgrade)
File "/home/espears/.virtualenvs/local-dev/local/lib/python2.7/site-packages/pip/index.py", line 285, in find_requirement
raise DistributionNotFound('No distributions at all found for %s' % req)
DistributionNotFound: No distributions at all found for pyodbc
It makes it seem that if I can just get the --allow-external option to work, it will work because it clearly sees the common location for getting pyodbc.
But I am puzzled why this is needed at all, when virtually all references to installing pyodbc do not need to go out of the way to enable external links. I've also been installing many packages all day today, with few issues and certainly not encountering this allow-external need in any other case.
I am using pip version 1.5 with Python 2.7.3 on Ubuntu 12.04.
Source: (StackOverflow)
I'm having a bit of trouble successfully using pyodbc on Debian Lenny (5.0.7). Specifically, I appear to be having trouble fetching NVARCHAR values (not a SQL Server expert, so go easy on me :) ).
Most traditional queries work OK. For instance, a count of rows in table1 yields
cursor.execute("SELECT count(id) from table1")
<pyodbc.Cursor object at 0xb7b9b170>
>>> cursor.fetchall()
[(27, )]
As does a full dump of ids
>>> cursor.execute("SELECT id FROM table1")
<pyodbc.Cursor object at 0xb7b9b170>
>>> cursor.fetchall()
[(0.0, ), (3.0, ), (4.0, ), (5.0, ), (6.0, ), (7.0, ), (8.0, ), (11.0, ), (12.0, ), (18.0, ), (19.0, ), (20.0, ), (21.0, ), (22.0, ), (23.0, ), (24.0, ), (25.0, ), (26.0, ), (27.0, ), (28.0, ), (29.0, ), (32.0, ), (33.0, ), (34.0, ), (35.0, ), (36.0, ), (37.0, )]
But a dump of names (again, of type NVARCHAR) does not
>>> cursor.execute("SELECT name FROM table1")
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
pyodbc.ProgrammingError: ('42000', '[42000] [FreeTDS][SQL Server]Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier. (4004) (SQLExecDirectW)')
... the critical error being
pyodbc.ProgrammingError: ('42000', '[42000] [FreeTDS][SQL Server]Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier. (4004) (SQLExecDirectW)')
This is consistent across tables.
I've tried a variety of different versions of each, but now I'm running unixODBC 2.2.11 (from lenny repos), FreeTDS 0.91 (built from source, with ./configure --enable-msdblib --with-tdsver=8.0), and pyodbc 3.0.3 (built from source).
With a similar combination (unixODBC 2.3.0, FreeTDS 0.91, pyodbc 3.0.3), the same code works on Mac OS X 10.7.2.
I've searched high and low, investigating the solutions presented here and here and recompiling different versions of unixODBC and FreeTDS, but still no dice. Relevant configuration files provided below:
user@host:~$ cat /usr/local/etc/freetds.conf
#$Id: freetds.conf,v 1.12 2007/12/25 06:02:36 jklowden Exp $
#
# This file is installed by FreeTDS if no file by the same
# name is found in the installation directory.
#
# For information about the layout of this file and its settings,
# see the freetds.conf manpage "man freetds.conf".
# Global settings are overridden by those in a database
# server specific section
[global]
# TDS protocol version
tds version = 8.0
client charset = UTF-8
# Whether to write a TDSDUMP file for diagnostic purposes
# (setting this to /tmp is insecure on a multi-user system)
; dump file = /tmp/freetds.log
; debug flags = 0xffff
# Command and connection timeouts
; timeout = 10
; connect timeout = 10
# If you get out-of-memory errors, it may mean that your client
# is trying to allocate a huge buffer for a TEXT field.
# Try setting 'text size' to a more reasonable limit
text size = 64512
# A typical Sybase server
[egServer50]
host = symachine.domain.com
port = 5000
tds version = 5.0
# A typical Microsoft server
[egServer70]
host = ntmachine.domain.com
port = 1433
tds version = 8.0
[foo]
host = foo.bar.com
port = 1433
tds version = 8.0
user@host:~$ cat /etc/odbc.ini
[foo]
Description = Foo
Driver = foobar
Trace = No
Database = db
Server = foo.bar.com
Port = 1433
TDS_Version = 8.0
user@host:~$ cat /etc/odbcinst.ini
[foobar]
Description = Description
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
CPTimeout =
CPReuse =
Any advice or direction would be very much appreciated!
Source: (StackOverflow)
Am having an issue connecting to a Microsoft SQL Server instance from pyODBC within an Ubuntu (12.10) machine.
The error I am getting back is:
pyodbc.Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data Source name not found, and no default driver specified (0) (SQLDriverConnect)')
The connection string am using for pyodbc is:
self.corpus_cnxn = pyodbc.connect('DRIVER={FreeTDS};SERVER=UKEDN-06880;DATABASE=db1;UID=user;PWD=pass')
This seems to work fine from pyODBC within Windows (just need to change the DRIVER to 'SQL Server' instead of 'FreeTDS'), and it work fine when I try to connect from the Ubuntu machine using the tsql Tool from the terminal, with the following command:
tsql -S UKEDN-06880 -p 1433 -U user -P pass
I can select any table with no issues, it just doesn't seem to work from within pyODBC.
Any help or advice will be much appreciated, my Linux skills are uber weak and am totally stuck, although since it works from tsql I get the feeling that am very close!
Source: (StackOverflow)
pyodbc is a very nice thing, but the Windows installers only work with their very specific python version. With the release of Python 3.4, the only available installers just stop once they don't see 3.3 in the registry (though 3.4 is certainly there).
Copying the .pyd and .egg-info files from a 3.3 installation into the 3.4 site-packages directory doesn't seem to do the trick. When importing pyodbc, an ImportError is thrown: ImportError: DLL load failed: %1 is not a valid Win32 application.
Is there a secret sauce that can be added to make the 3.3 file work correctly? Or do we just need to wait for a 3.4 installer version?
Source: (StackOverflow)
How do I serialize pyodbc cursor output (from .fetchone, .fetchmany or .fetchall) as a Python dictionary?
I'm using bottlepy and need to return dict so it can return it as JSON.
Source: (StackOverflow)
What library should I use to connect to odbc from python on windows? Is there a good alternative for pywin32 when it comes to odbc?
I'm looking for something well-documented, robust, actively maintained, etc. pyodbc looks good -- are there any others?
Source: (StackOverflow)
When running pip install pyodbc, I get
In file included from .../build/pyodbc/src/buffer.cpp:12:
.../build/pyodbc/src/pyodbc.h:52:10: fatal error: 'sql.h' file not found
#include <sql.h>
^
1 error generated.
error: command 'cc' failed with exit status 1
It seems that Mavericks has no sql.h under /usr/include
Did anyone manage to install pyodbc? Is there a known workaround?
Source: (StackOverflow)
I am running Linux (2.6.18-164.15.1.el5.centos.plus) and trying to install pyodbc. I am doing pip install pyodbc and get a very long list of errors, which end in
error: command 'gcc' failed with exit status 1
I looked in /root/.pip/pip.log and saw the following:
InstallationError: Command /usr/local/bin/python -c "import setuptools; file='/home/build/pyodbc/setup.py'; execfile('/home/build/pyodbc/setup.py')" install --single-version-externally-managed --record /tmp/pip-7MS9Vu-record/install-record.txt failed with error code 1
Has anybody had a similar issue installing pyodbc?
Thanks,
Igor
Source: (StackOverflow)
I'm using the following software stack on Ubuntu 10.04 Lucid LTS to
connect to a database:
- python 2.6.5 (ubuntu package)
- pyodbc git trunk commit
eb545758079a743b2e809e2e219c8848bc6256b2
- unixodbc 2.2.11 (ubuntu package)
- freetds 0.82 (ubuntu package)
- Windows with Microsoft SQL Server 2000 (8.0)
I get this error when trying to do native parameter binds in arguments
to a SQL SERVER function:
Traceback (most recent call last):
File "/home/nosklo/devel/testes/sqlfunc.py", line 32, in <module>
cur.execute("SELECT * FROM fn_FuncTest(?)", ('test',))
pyodbc.ProgrammingError: ('42000', '[42000] [FreeTDS][SQL
Server]SqlDumpExceptionHandler: Process 54 generated fatal exception
c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this
process.\r\n (0) (SQLPrepare)')
Here's the reproduction code:
import pyodbc
constring = 'server=myserver;uid=uid;pwd=pwd;database=db;TDS_Version=8.0;driver={FreeTDS}'
con = pyodbc.connect(constring)
print 'VERSION: ', con.getinfo(pyodbc.SQL_DBMS_VER)
cur = con.cursor()
try:
cur.execute('DROP FUNCTION fn_FuncTest')
con.commit()
print "Function dropped"
except pyodbc.Error:
pass
cur.execute('''
CREATE FUNCTION fn_FuncTest (@testparam varchar(4))
RETURNS @retTest TABLE (param varchar(4))
AS
BEGIN
INSERT @retTest
SELECT @testparam
RETURN
END''')
con.commit()
Now the function is created. If I try to call it using a value direct in the query (no native binds of values) it works fine:
cur.execute("SELECT * FROM fn_FuncTest('test')")
assert cur.fetchone()[0] == 'test'
However I get the error above when I try to do a native bind (by using a parameter placeholder and passing the value separately):
cur.execute("SELECT * FROM fn_FuncTest(?)", ('test',))
Further investigation reveals some weird stuff I'd like to relate:
- Everything works fine if I change TDS Version to 4.2 (however,
version report from sql server is wrong -- using TDS version
4.2 I get
'95.08.0255' instead of the real version '08.00.0760').
- Everything works fine for the other two types of functions ->
functions that return a value and functions that are just a SELECT
query (like a view) both work fine. You can even define a new function
that returns the result of a query on the other (broken) function, and
this way everything will work, even when doing native binds on the
parameters. For example:
CREATE FUNCTION fn_tempFunc(@testparam
varchar(4)) RETURNS TABLE AS RETURN (SELECT * FROM
fn_FuncTest(@testparam))
- Connection gets very unstable after this error, you can't recover.
- The error happens when trying to bind any type of data.
How can I pursue this further? I'd like to do native binds to function parameters.
Source: (StackOverflow)
So I am having great difficulty getting all three of these to work together in harmony. I guess I'll list all the various configurations, along with the test code to see if a pair of fresh eyes can realize my stupidity.
I'm running 12.04 Unbuntu Server and I'm trying to connect to a MSSQL Server 2008 and end up using it with PyODBC.
However, when just putting in
tsql -S T2 -U Foo -P Bar
I get the
1>
2>
3>
4>
5>
6>
7>
8>
9>
10>
11>
and etc.
Anyway, if anyone would be able to help (and I would be eternally grateful if you can clear me of this haze), here are my current configurations.
This is my /etc/odbc.ini
[ODBC Data Sources]
odbcname = MySQL
T2 = MSSQL
[odbcname]
Driver = /usr/lib/odbc/libmyodbc.so
Description = MyODBC 3.51 Driver DSN
SERVER = Foobar
PORT = 3306
USER = Foo
Password = Bar
Database = Foobar
OPTION = 3
SOCKET =
[T2]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
SERVER = FOOBAR
PORT = 1433
USER = Foo
Password = Bar
Database = Foobar
OPTION = 3
SOCKET =
[Default]
Driver = /usr/local/lib/libmyodbc3.so
Description = MyODBC 3.51 Driver DSN
SERVER = FOOBAR
PORT = 3306
USER = foo
Password = bar
Database = FOOBAR
OPTION = 3
SOCKET =
The following is my /etc/odbcinst.ini
[FreeTDS]
Description=FreeTDS Driver
Driver=/usr/lib/odbc/libtdsodbc.so
Setup=/usr/lib/odbc/libtdsS.so
CPTimeout=
CPReuse=
FileUsage=1
The following is my freetds.conf
# This file is installed by FreeTDS if no file by the same
# name is found in the installation directory.
#
# For information about the layout of this file and its settings,
# see the freetds.conf manpage "man freetds.conf".
# Global settings are overridden by those in a database
# server specific section
[global]
# TDS protocol version
; tds version = 4.2
# Whether to write a TDSDUMP file for diagnostic purposes
# (setting this to /tmp is insecure on a multi-user system)
; dump file = /tmp/freetds.log
; debug flags = 0xffff
# Command and connection timeouts
; timeout = 10
; connect timeout = 10
# If you get out-of-memory errors, it may mean that your client
# is trying to allocate a huge buffer for a TEXT field.
# Try setting 'text size' to a more reasonable limit
#text size = 64512
[T2]
host = FOOBAR
port = 1433
tds version = 7.0
client charset = UTF-8
text size = 20971520
[global]
# TDS protocol version
tds version = 7.0
And my Python test file just for good measure
import pyodbc
import sys
try:
#tempsystrends = pyodbc.connect('DRIVER=FreeTDS;SERVER=FOOBAR;PORT=1433;DATABASE=T2;UID=FOO;PWD=bar;TDS_Version=7.0;')
cursor = tempsystrends.cursor()
except pyodbc.Error as e:
print "Error: %s" % (e.args[1])
sys.exit(1)
Source: (StackOverflow)
I am trying to create tables in a MS Access DB with python using pyodbc but when I run my script no tables are created and no errors are given. My code:
#!/usr/bin/env python
import pyodbc
con = pyodbc.connect(r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=Z:\Data\Instruments\testDB.accdb; Provider=MSDASQL;')
cur = con.cursor()
string = "CREATE TABLE TestTable(symbol varchar(15), leverage double, shares integer, price double)"
cur.execute(string)
What could be wrong?
Source: (StackOverflow)
I am using Ubuntu 9.04
I have installed the following package versions:
unixodbc and unixodbc-dev: 2.2.11-16build3
tdsodbc: 0.82-4
libsybdb5: 0.82-4
freetds-common and freetds-dev: 0.82-4
python2.6-dev
I have configured /etc/unixodbc.ini like this:
[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
CPTimeout =
CPReuse =
UsageCount = 2
I have configured /etc/freetds/freetds.conf like this:
[global]
tds version = 8.0
client charset = UTF-8
text size = 4294967295
I have grabbed pyodbc revision 31e2fae4adbf1b2af1726e5668a3414cf46b454f from http://github.com/mkleehammer/pyodbc and installed it using "python setup.py install"
I have a windows machine with Microsoft SQL Server 2000 installed on my local network, up and listening on the local ip address 10.32.42.69. I have an empty database created with name "Common". I have the user "sa" with password "secret" with full privileges.
I am using the following python code to setup the connection:
import pyodbc
odbcstring = "SERVER=10.32.42.69;UID=sa;PWD=secret;DATABASE=Common;DRIVER=FreeTDS"
con = pyodbc.connect(odbcstring)
cur = con.cursor()
cur.execute("""
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'testing')
DROP TABLE testing
""")
cur.execute('''
CREATE TABLE testing (
id INTEGER NOT NULL IDENTITY(1,1),
myimage IMAGE NULL,
PRIMARY KEY (id)
)
''')
con.commit()
Everything WORKS up to this point. I have used SQLServer's Enterprise Manager on the server and the new table is there.
Now I want to insert some data on the table.
cur = con.cursor()
# using web data for exact reproduction of the error by all.
# I'm actually reading a local file in my real code.
url = 'http://www.forestwander.com/wp-content/original/2009_02/west-virginia-mountains.jpg'
data = urllib2.urlopen(url).read()
sql = "INSERT INTO testing (myimage) VALUES (?)"
Now here on my original question, I was having trouble using cur.execute(sql, (data,)) but now I've edited the question, because following Vinay Sajip's answer below (THANKS), I have changed it to:
cur.execute(sql, (pyodbc.Binary(data),))
con.commit()
And insertion is working perfectly. I can confirm the size of the inserted data using the following test code:
cur.execute('SELECT DATALENGTH(myimage) FROM testing WHERE id = 1')
data_inside = cur.fetchone()[0]
assert data_inside == len(data)
Which passes perfectly!!!
Now the problem is on retrieval of the data back.
I am trying the common approach:
cur.execute('SELECT myimage FROM testing WHERE id = 1')
result = cur.fetchone()
returned_data = str(result[0]) # transforming buffer object
print 'Original: %d; Returned: %d' % (len(data), len(returned_data))
assert data == returned_data
However that fails!!
Original: 4744611; Returned: 4096
Traceback (most recent call last):
File "/home/nosklo/devel/teste_mssql_pyodbc_unicode.py", line 53, in <module>
assert data == returned_data
AssertionError
I've put all the code above in a single file here, for easy testing of anyone that wants to help.
Now for the question:
I want python code to insert an image file into mssql. I want to query the image back and show it to the user.
I don't care about the column type in mssql. I am using the "IMAGE" column type on the example, but any binary/blob type would do, as long as I get the binary data for the file I inserted back unspoiled. Vinay Sajip said below that this is the preferred data type for this in SQL SERVER 2000.
The data is now being inserted without errors, however when I retrieve the data, only 4k are returned. (Data is truncated on 4096).
How can I make that work?
EDITS: Vinay Sajip's answer below gave me a hint to use pyodbc.Binary on the field. I have updated the question accordingly. Thanks Vinay Sajip!
Alex Martelli's comment gave me the idea of using the DATALENGTH MS SQL function to test if the data is fully loaded on the column. Thanks Alex Martelli !
Source: (StackOverflow)
I used pyodbc with python before but now I have installed it on a new machine ( win 8 64 bit, Python 2.7 64 bit, PythonXY with Spyder).
Before I used to (at the bottom you can find more real examples):
columns = [column[0] for column in cursor.description]
temp = cursor.fetchall()
data = pandas.DataFrame(temp,columns=columns)
and it would work fine. Now it seems like DataFrame is not able to convert from the data fetched from the cursor anymore. It returns:
Shape of passed values is (x,y), indices imply (w,z)
I kind of see where the issue is. Basically, imagine I fetch only one row. Then DataFrame would like to shape it (1,1), one element only. While I would like to have (1,X) where X is the length of the list.
I am not sure why the behavior changed. Maybe it is the Pandas version I have, or the pyodbc, but updating is problematic. I tried to update some modules but it screws up everything, any method I use (binaries--for the right machine/installation--pip install, easy-install,anything! etc.. which is very frustrating indeed. I would probably avoid Win 8 64 bit from now on for Python).
Real examples:
sql = 'Select * form TABLE'
cursor.execute(sql)
columns = [column[0] for column in cursor.description]
data = cursor.fetchall()
con.close()
results = DataFrame(data, columns=columns)
Returns:
* ValueError: Shape of passed values is (1, 1540), indices imply (51, 1540)
Notice that:
ipdb> type(data)
<type 'list'>
ipdb> np.shape(data)
(1540, 51)
ipdb> type(data[0])
<type 'pyodbc.Row'>
Now, for example, if we do:
ipdb> DataFrame([1,2,3],columns=['a','b','c'])
* ValueError: Shape of passed values is (1, 3), indices imply (3, 3)
and if we do:
ipdb> DataFrame([[1,2,3]],columns=['a','b','c'])
a b c
0 1 2 3
However, even trying:
ipdb> DataFrame([data[0]], columns=columns)
*** ValueError: Shape of passed values is (1, 1), indices imply (51, 1)
or
ipdb> DataFrame(data[0], columns=columns)
*** PandasError: DataFrame constructor not properly called!
Please help :) Thanks!
Source: (StackOverflow)