FAQ Database Discussion Community


Optimize script with Pyodbc method

python,sql-server,optimization,pyodbc
In order to fetch some data I need to later processing with Matlab, I'm using a python script to extract that data from a series of over 50 identical databases (i.e. all sharing the same table structure) I was able to to that with the code below. However, in order...

How to install SQL Server driver with pyodbc in Fedora?

python,sql-server,pyodbc
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=ipaddress;DATABASE=xxx;UID=xxxx;PWD=XXXXX') cursor = cnxn.cursor() I use this method. It's work on Windows. But, on Linux, Fedora, Mac is failed. Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1720.0' : file not found (0) (SQLDriverConnect)") I find these method to solve it. But I'm not success. https://technet.microsoft.com/en-us/library/hh568449(v=sql.110).aspx...

How to connect MS Access to Python using pyodbc

python,ms-access,pyodbc
I'm having trouble connecting a database in access with pyodbc. I've seen other example codes that appear near identical to mine that work: import pyodbc cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=PYODBC.accdb;UID=me;PWD=pass') cursor = cnxn.cursor() cursor.execute("SELECT Forename FROM Student") row = cursor.fetchone() if row: print(row) My machine is running on windows 7 home...

Python - PyODBC - multiple queries with loop

python,sql-server,python-3.x,pyodbc
I'm facing problems with sending multiple queries to SQL Server 2012 through pyODBC in Python. I have a DataFrame with queries and I want to use it to query DB. Something like this: cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=mySERVER;DATABASE=myDB;UID=myUID;PWD=myPSWD') cursor = cnxn.cursor() cursor2 = cnxn.cursor() for i in range(len(myDataFrame.Column_w_Queries)): query = '"'...

Using stored procedures[python + ms sql]

python,pyodbc
I'm have a stored procedure, code: DECLARE @RC int DECLARE @id varchar(13) DECLARE @pw varchar(13) DECLARE @depart varchar(32) DECLARE @class varchar(12) DECLARE @name varchar(12) DECLARE @birthday varchar(10) DECLARE @grade int DECLARE @subgrade int SELECT @id = 'test' SELECT @pw = '12345' SELECT @depart = 'none' SELECT @class = 'GM' SELECT...

Select nth to nth row while table still have values unselected with python and pyodbc

python-2.7,sql-server-2005,rabbitmq,pyodbc,python-pika
I have a table with 10,000 rows and I want to select the first 1000 rows and then select again and this time, the next set of rows, which is 1001-2001. I am using the BETWEEN clause in order to select the range of values. I can also increment the...

Iterating rows with Pyodbc

python,pyodbc
I am using Pyodbc to return a number of rows which are dumped into a JSON and sent to a server. I would like to iterate my SQL table and return all records. I am using cursor.fetchall() now, and the program returns one record. As shown below. When I use...

PyOdbc fails to connect to a sql server instance

python,sqlalchemy,pyodbc
I am trying to connect to a sql server instance using pyodbc version 3.0.6., SQLAlchemy 1.0.4 on Windows 7 using a Python 2.7 (32 bit). I am using a connection string as follows DRIVER={SQL Server};SERVER=mymachinename;DATABASE=mydb;UID=sa;PWD=admin1; but I keep getting this error Could not parse rfc1738 URL from string 'DRIVER={SQL Server};SERVER=mymachinename;DATABASE=mydb;UID=sa;PWD=admin1'...

Large select query hangs using Python and unixODBC connecting to SQL Server

python,sql-server,odbc,pyodbc,unixodbc
We have to run a number of large select queries against SQL Server from CentOS 6 boxes. On two servers, when using a pretty bland select statement, the rate of data returned from SQL Server starts fast before slowing down and receiving bursts of data every ~30 seconds. On two...

“Invalid precision or scale value” error from SQL Server with pypyodbc for Decimal('0.00'), Decimal('0.12')

python,sql-server,sql-server-2008,pyodbc,pypyodbc
I am developing an application in python. I am inserting some financial data into database. I have approx. 1300 tables all with same schema, one table for each company. I am using pypyodbc 1.3.3(latest). I have Decimal as datatype. I am using precision of as Decimal(8,2). In my table, datatypes...

update python last record only

python,sql-server,insert,pyodbc
I am a newbie python user and for some reason the below update does not work. What I expect is that it updates using a API pull and when I use the print it displays exactly what I expect, 5 records. These are 2 seperate XML files that are afterwards...

unable insert jpeg into filemaker db with pyodbc : HY011 ODBC Error

python,odbc,filemaker,pyodbc,unixodbc
I get this odbc error by trying to insert a JPEG into a FileMaker 14 database. When i run the same script against a MySQL for inserting a blob, there's no error. Inserting a text field does not produce an error. Also i have no clue and cannot find a...

Text inserted into Hyperlink field shows up but does not act as a link

python-2.7,ms-access,hyperlink,ms-access-2003,pyodbc
I'm having difficulty getting my pyodbc inserted hyperlinks to work in my Access 2003 database. It appears to look like a hyperlink but does nothing when clicked on. For it to work, I have to edit it in Access and only then does it recognize that, "oh yeah that is...

On Mac - pyodbc doesn't fetch long columns from mysql

python,mysql,osx,osx-yosemite,pyodbc
I have the following python code running on top of mysql: sql = "select col1 from table1" conn = pyodbc.connect(CONNECTION_STRING) cursor = conn.cursor() cursor.execute(sql) result = cursor.fetchall() The type of col1 is LONGTEXT. If a row has col1 longer than 255 characters it's not returned and result comes out empty,...

Python | Pyodbc | Encoding FetchAll() to Utf-8 with .Encode('utf-8')

python,csv,encoding,utf-8,pyodbc
thanks in advance. I am still very new to python. I am attempting to write the results of a pyodbc query using FetchAll to a CSV file. When I ran the code on our stage server, everything works great. On our Live server, the data has a encoding issue. I...

Connecting to ODBC using pyODBC

python,ms-access,odbc,pyodbc,dsn
I've read all the faq pages from the python odbc library as well as other examples and managed to connect to the DSN, using the following code: cnxn = pyodbc.connect("DSN=DSNNAME") cursor = cnxn.cursor() cursor.tables() rows = cursor.fetchall() for row in rows: print row.table_name but for everything else I keep getting...

Need help trying to get a Python multiprocess pool working

python,pyodbc,python-multiprocessing
I have a database table I am reading rows from ( in this instance ~360k rows ) and placing the pyodbc.row objects into a list for later consumption then writing using this script. from hashlib import sha1 import multiprocessing import datetime, os import pyodbc import math import traceback, sys source_rows...

python buffer size when querying Vertica using Pyodbc

python,buffer,pyodbc,vertica
i am using pyodbc to connect to Vertica DB and then i am using cursor to execute a query but the results of the query include a text field that is huge, the execute runs but it never returns anything, not even an exception. I can see this when i...

pyodbc INSERT INTO from a list

pyodbc,executemany
I am trying to insert data into an Access mdb file using a list as the source for the values. cursor.execute("select * from Components") cursor.executemany(""" INSERT INTO Components ([Database Number],Description, [Warehouse Code],[Supplier Code], Usage, Major1, Minor1) VALUES (?,?,?,?,?,?,?) """), input_list cursor.commit() I get the error "TypeError: function takes exactly 2...

Which cursor object is pyodbc's `Cursor.execute` returning?

python,pyodbc
According to PEP249, Cursor.execute has no defined return values. pyodbc, however, seems to make it return a cursor object; the docs say so, too, albeit rather briefly: execute(...) C.execute(sql, [params]) --> Cursor Is this guaranteed/documented somewhere in more detail? Looking at identities, the object returned appears to be the very...