Menu
  • HOME
  • TAGS

pypyodbc cursor.execute() List index out of range

Tag: python,sql-server,pypyodbc

It's late, I'm tired - I hope I am just making a stupid mistake but I can't seem to see it. Can anyone see what I may be doing wrong? If I run the SQL on SQL Server Management studio, it works fine. I can run other SQL statements against the cursor, just not this one.

Thanks in advance

System:

Windows 7, Python 3.4, SQLServer 2008, pypyodbc

Query:

IF EXISTS(SELECT * FROM sys.databases WHERE name='{MyDBName}')
BEGIN
 ALTER DATABASE {MyDBName} SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 
 DROP DATABASE {MyDBName}; 
END

CODE:

cursor.execute(query)
cursor.commit()

EXCEPTION:

ex = {IndexError} list index out of range

Best How To :

For posterity sake, I finally found my way through it and will share in case anyone reads this later:

It turns out that I need the autocommit property set to true when making the connection:

with pypyodbc.connect(cnx, auto_commit) as connection:
     cursor = connection.cursor()              

     cursor.execute(query)
     cursor.commit()

Note also that I change my query to just:

DROP DATABASE {MyDBName};

However, that alone didn't fix it without the auto-commit.

I hope that is helpful to someone, time for bed : )

Default the year based on month value

sql,sql-server

SQL Server is correct in what it's doing as you are requesting an additional row to be returned which if ran now 2015-06-22 would return "2016" Your distinct only works on the first select you've done so these are your options: 1) Use cte's with distincts with subq1 (syear, eyear,...

SQLAlchemy. 2 different relationships for 1 column

python,sqlalchemy

I'm afraid you can't do it like this. I suggest you have just one relationship users and validate the insert queries.

Count function counting only last line of my list

python,python-2.7

I don't know what you are exactly trying to achieve but if you are trying to count R and K in the string there are more elegant ways to achieve it. But for your reference I had modified your code. N = int(raw_input()) s = [] for i in range(N):...

How do I read this list and parse it?

python,list

Your list contains one dictionary you can access the data inside like this : >>> yourlist[0]["popularity"] 2354 [0] for the first item in the list (the dictionary). ["popularity"] to get the value associated to the key 'popularity' in the dictionary....

Calling function and passing arguments multiple times

python,function,loops

a,b,c = 1,2,3 while i<n: a,b,c = myfunction(a,b,c) i +=1 ...

Sql injected code is inserted to my database . How to remove it

sql-server

You can use the fact that html code starts with symbol <. Then: UPDATE TableName SET SomeColumn = CASE WHEN CHARINDEX('<', SomeColumn) > 0 THEN SUBSTRING(SomeColumn, 1, CHARINDEX('<', SomeColumn) - 1) ELSE SomeColumn END If this is not true then we will need more information about data. May be it...

Cannot Browse in sql to backup bak file

sql-server

Try this query RESTORE DATABASE Databasename FROM DISK = 'Z:\SQLServerBackups\tms.bak' ; ...

Strange Behavior: Floating Point Error after Appending to List

python,python-2.7,behavior

Short answer: your correct doesn't work. Long answer: The binary floating-point formats in ubiquitous use in modern computers and programming languages cannot represent most numbers like 0.1, just like no terminating decimal representation can represent 1/3. Instead, when you write 0.1 in your source code, Python automatically translates this to...

How to use template within Django template?

python,html,django,templates,django-1.4

You can use the include tag in order to supply the included template with a consistent variable name: For example: parent.html <div class="row"> <div class="col-md-12 col-lg-12 block block-color-1"> {% include 'templates/child.html' with list_item=mylist.0 t=50 only %} </div> </div> child.html {{ list_item.text|truncatewords:t }} UPDATE: As spectras recommended, you can use the...

How to change the IP address of Amazon EC2 instance using boto library

python,amazon-web-services,boto

Make sure you have set properly with ~/.boto and connect to aws, have the boto module ready in python. If not, go through this first: Getting Started with Boto For example, you need assign a new EIP 54.12.23.34 to the instance i-12345678 Make sure, EIP has been allocated(existed) and you...

Title search in SQL With replacement of noice words [on hold]

sql,sql-server,sql-server-2008

I think you want something like this: DECLARE @nw TABLE ( sn INT, [key] VARCHAR(100) ) INSERT INTO @nw VALUES ( 1, 'and' ), ( 2, 'on' ), ( 3, 'of' ), ( 4, 'the' ), ( 5, 'view' ) DECLARE @s VARCHAR(100) = 'view This of is the Man';...

Twilio Client Python not Working in IOS Browser

javascript,python,ios,flask,twilio

Twilio developer evangelist here. Twilio Client uses WebRTC and falls back to Flash in order to make web browsers into phones. Unfortunately Safari on iOS supports neither WebRTC nor Flash so Twilio Client cannot work within any browser on iOS. It is possible to build an iOS application to use...

Using counter on array for one value while keeping index of other values

python,collections

To count how often one value occurs and at the same time you want to select those values, you'd simply select those values and count how many you selected: fruits = [f for f in foods if f[0] == 'fruit'] fruit_count = len(fruits) If you need to do this for...

Inserting a variable in MongoDB specifying _id field

python,mongodb,pymongo

Insert only accepts a final document or an array of documents, and an optional object which contains additional options for the collection. db.collection.insert( <document or array of documents>, { // options writeConcern: <document>, ordered: <boolean> } ) You may want to add the _id to the document in advance, but...

Setting time limit in SQL Query

sql-server

Assuming that you want between 10:00 AM and 5:00 PM, you can use this SELECT CASE WHEN CAST(GETDATE() AS TIME) BETWEEN '10:00:00' AND '17:00:00' THEN 1 ELSE 0 END In this context, Select * from table makes no sense, unless you have a time column and want to evaluate that....

Foreign key in C#

c#,sql,sql-server,database

You want create relationship in two table Refer this link http://www.c-sharpcorner.com/Blogs/5608/create-a-relationship-between-two-dataset-tables.aspx...

Get unique row by single column where duplicates exist

sql,sql-server

SELECT MIN(date),thread_id FROM messages GROUP BY thread_id HAVING COUNT(thread_id) > 1 ...

Identify that a string could be a datetime object

python,regex,algorithm,python-2.7,datetime

What about fuzzyparsers: Sample inputs: jan 12, 2003 jan 5 2004-3-5 +34 -- 34 days in the future (relative to todays date) -4 -- 4 days in the past (relative to todays date) Example usage: >>> from fuzzyparsers import parse_date >>> parse_date('jun 17 2010') # my youngest son's birthday datetime.date(2010,...

SQL Server / C# : Filter for System.Date - results only entries at 00:00:00

c#,asp.net,sql-server,date,gridview-sorting

What happens if you change all of the filters to use 'LIKE': if (DropDownList1.SelectedValue.ToString().Equals("Start")) { FilterExpression = string.Format("Start LIKE '{0}%'", TextBox1.Text); } Then, you're not matching against an exact date (at midnight), but matching any date-times which start with that date. Update Or perhaps you could try this... if (DropDownList1.SelectedValue.ToString().Equals("Start"))...

Error connecting to MSSQL using PHP

php,sql-server,pdo,odbc,sqlsrv

Change it to: $this->link = new PDO( "sqlsrv:Server={$this->serverName},{$this->port};Database={$this->db};", $this->uid, $this->pwd ); The default SQL Server port is 1433. Note the curly brackets, they allow for class variables....

ONLY display certain rows from an inner joined table using a certain colum as a parameter from one of the inner joined tables

sql-server,join

If I understood correctly this should be what you're looking for SELECT A.UserName, A.[Email ID], A.[Supervisor Email ID] FROM A INNER JOIN B ON A.UserCode = B.UserCode WHERE B.ACTIVE_FLAG = 'Y' ...

Pandas Dataframe Complex Calculation

python,python-2.7,pandas,dataframes

I believe the following does what you want: In [24]: df['New_Col'] = df['ActualCitations']/pd.rolling_sum(df['totalPubs'].shift(), window=2) df Out[24]: Year totalPubs ActualCitations New_Col 0 1994 71 191.002034 NaN 1 1995 77 2763.911781 NaN 2 1996 69 2022.374474 13.664692 3 1997 78 3393.094951 23.240376 So the above uses rolling_sum and shift to generate the...

Find the tf-idf score of specific words in documents using sklearn

python,scikit-learn,tf-idf

Yes. See .vocabulary_ on your fitted/transformed TF-IDF vectorizer. In [1]: from sklearn.datasets import fetch_20newsgroups In [2]: data = fetch_20newsgroups(categories=['rec.autos']) In [3]: from sklearn.feature_extraction.text import TfidfVectorizer In [4]: cv = TfidfVectorizer() In [5]: X = cv.fit_transform(data.data) In [6]: cv.vocabulary_ It is a dictionary of the form: {word : column index in...

In sklearn, does a fitted pipeline reapply every transform?

python,scikit-learn,pipeline,feature-selection

The pipeline calls transform on the preprocessing and feature selection steps if you call pl.predict. That means that the features selected in training will be selected from the test data (the only thing that makes sense here). It is unclear what you mean by "apply" here. Nothing new will be...

Python - Opening and changing large text files

python,replace,out-of-memory,large-files

You need to read one bite per iteration, analyze it and then write to another file or to sys.stdout. Try this code: mesh = open("file.mesh", "r") mesh_out = open("file-1.mesh", "w") c = mesh.read(1) if c: mesh_out.write("{") else: exit(0) while True: c = mesh.read(1) if c == "": break if c...

represent an index inside a list as x,y in python

python,list,numpy,multidimensional-array

According to documentation of numpy.reshape , it returns a new array object with the new shape specified by the parameters (given that, with the new shape, the amount of elements in the array remain unchanged) , without changing the shape of the original object, so when you are calling the...

How to remove structure with python from this case?

python,python-2.7

It's complicated to use regex, a stupid way I suggested: def remove_table(s): left_index = s.find('<table>') if -1 == left_index: return s right_index = s.find('</table>', left_index) return s[:left_index] + remove_table(s[right_index + 8:]) There may be some blank lines inside the result....

How to select next row after select in SQL Server?

sql,sql-server

The query can be written as: ; WITH Base AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY Shift_Date) RN FROM #Table1 ) , WithC AS ( SELECT * FROM Base WHERE Shift2 = 'C' ) SELECT * FROM WithC UNION SELECT WithCNext.* FROM WithC C LEFT JOIN Base WithCNext ON...

Pandas - Dropping multiple empty columns

python,pandas

You can just subscript the columns: df = df[df.columns[:11]] This will return just the first 11 columns or you can do: df.drop(df.columns[11:], axis=1) To drop all the columns after the 11th one....

Python recursive function not recursing

python,recursion

Afraid I don't know much about python, but I can probably help you with the algorithm. The encoding process repeats the following: multiply the current total by 17 add a value (a = 1, b = 2, ..., z = 26) for the next letter to the total So at...

Implement reference key in SQL Server

sql-server,sql-server-2008

It's called a "one-to-zero-or-one" relationship, as one Line might be associated to zero or one TestPacks. You can implement it by using a FK that allows NULL values. CREATE TABLE TestPack (id INT, PRIMARY KEY (id)) CREATE TABLE Line (id INT, TestPackId INT NULL, FOREIGN KEY (TestPackId) REFERENCES TestPack(id)) By...

Displaying a 32-bit image with NaN values (ImageJ)

python,image-processing,imagej

The display range of your image might not be set correctly. Try outputImp.resetDisplayRange() or outputImp.setDisplayRange(Stats.min, Stats.max) See the ImagePlus javadoc for more info....

INSERT INTO fails due to incorrect conversion T-SQL

sql-server,tsql

The problem is that there's no implicit conversion from varchar (your literal) to sql_variant. Just add an explicit conversion and you're done: cast('FooBar' as sql_variant) ...

How to check for multiple attributes in a list

python,python-2.7

You can create a set holding the different IDs and then compare the size of that set to the total number of quests. The difference tells you how many IDs are duplicated. Same for names. Something like this (untested): def test_quests(quests): num_total = len(quests) different_ids = len(set((q.ID for q in...

Fastest way to add a grouping column which divides the result per 4 rows

sql,sql-server,tsql,sql-server-2012

Try this: SELECT col, (ROW_NUMBER() OVER (ORDER BY col) - 1) / 4 + 1 AS grp FROM mytable grp is equal to 1 for the first four rows, equal to 2 for the next four, equal to 3 for the next four, etc. Demo here Alternatively, the following can...

The event loop is already running

python,python-3.x,pyqt,pyqt4

I think the problem is with your start.py file. You have a function refreshgui which re imports start.py import will run every part of the code in the file. It is customary to wrap the main functionality in an ''if __name__ == '__main__': to prevent code from being run on...

Matplotlib: Plot the result of an SQL query

python,sql,matplotlib,plot

Take this for a starter code : import numpy as np import matplotlib.pyplot as plt from sqlalchemy import create_engine import _mssql fig = plt.figure() ax = fig.add_subplot(111) engine = create_engine('mssql+pymssql://**:****@127.0.0.1:1433/AffectV_Test') connection = engine.connect() result = connection.execute('SELECT Campaign_id, SUM(Count) AS Total_Count FROM Impressions GROUP BY Campaign_id') ## the data data =...

SQL Multiple LIKE Statements

sql,sql-server,tsql,variables,like

WITH CTE AS ( SELECT VALUE FROM ( VALUES ('B79'), ('BB1'), ('BB10'), ('BB11'), ('BB12'), ('BB18'), ('BB2'), ('BB3'), ('BB4'), ('BB5'), ('BB6'), ('BB8'), ('BB9'), ('BB94'), ('BD1'), ('BD10'), ('BD11'), ('BD12'), ('BD13'), ('BD14'), ('BD15'), ('BD16'), ('BD17'), ('BD18'), ('BD19'), ('BD2'), ('BD20'), ('BD21'), ('BD22'), ('BD3'), ('BD4'), ('BD5'), ('BD6') ) V(VALUE) ) SELECT * FROM tbl_ClientFile...

How does the class_weight parameter in scikit-learn work?

python,scikit-learn

First off, it might not be good to just go by recall alone. You can simply achieve a recall of 100% by classifying everything as the positive class. I usually suggest using AUC for selecting parameters, and then finding a threshold for the operating point (say a given precision level)...

Sort when values are None or empty strings python

python,list,sorting,null

If you want the None and '' values to appear last, you can have your key function return a tuple, so the list is sorted by the natural order of that tuple. The tuple has the form (is_none, is_empty, value); this way, the tuple for a None value will be...

SQL Customized search with special characters

sql,sql-server,sql-server-2008

Here is my attempt using Jeff Moden's DelimitedSplit8k to split the comma-separated values. First, here is the splitter function (check the article for updates of the script): CREATE FUNCTION [dbo].[DelimitedSplit8K]( @pString VARCHAR(8000), @pDelimiter CHAR(1) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN WITH E1(N) AS ( SELECT 1 UNION ALL SELECT...

Can someone explain to me how this statement is an exclude?

sql,sql-server,tsql

I can explain... a query that's very close to yours. Let me alter it to: SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON (t1.ColumnX = t2.ColumnX AND t2.columnY = 1) WHERE t2.tableID IS NULL This query retrieves all rows from t1, then checks to see if...

trying to understand LSH through the sample python code

python,similarity,locality-sensitive-hash

a. It's a left shift: https://docs.python.org/2/reference/expressions.html#shifting-operations It shifts the bits one to the left. b. Note that ^ is not the "to the power of" but "bitwise XOR" in Python. c. As the comment states: it defines "number of bits per signature" as 2**10 → 1024 d. The lines calculate...

how to enable a entry by clicking a button in Tkinter?

python,tkinter

You need to use the configure method of each widget: def rakhi(): entry1.configure(state="normal") entry2.configure(state="normal") ...

Python: histogram/ binning data from 2 arrays.

python,histogram,large-files

if you only need to do this for a handful of points, you could do something like this. If intensites and radius are numpy arrays of your data: bin_width = 0.1 # Depending on how narrow you want your bins def get_avg(rad): average_intensity = intensities[(radius>=rad-bin_width/2.) & (radius<rad+bin_width/2.)].mean() return average_intensities #...

Replace nodejs for python?

python,node.js,webserver

You might want to have a look at Tornado. It is well-documented and features built-in support for WebSockets. If you want to steer clear of the Tornado-framework, there are several Python implementations of Socket.io. Good luck!...

Sum of two variables in RobotFramework

python,automated-tests,robotframework

By default variables are string in Robot. So your first two statements are assigning strings like "xx,yy" to your vars. Then "evaluate" just execute your statement as Python would do. So, adding your two strings with commas will produce a list: $ python >>> 1,2+3,4 (1, 5, 4) So you...

How do variables inside python modules work?

python,module,python-module

The convention is to declare constants in modules as variables written in upper-case (Python style guide: https://www.python.org/dev/peps/pep-0008/#global-variable-names). But there's no way to prevent someone else to re-declare such a variable -- thus ignoring conventions -- when importing a module. There are two ways of working around this when importing modules...

group indices of list in list of lists

python,list

Use collections.OrderedDict: from collections import OrderedDict od = OrderedDict() lst = [2, 0, 1, 1, 3, 2, 1, 2] for i, x in enumerate(lst): od.setdefault(x, []).append(i) ... >>> od.values() [[0, 5, 7], [1], [2, 3, 6], [4]] ...

Take thousand value in SQL

sql,sql-server

SELECT CONVERT(INT,YourColumn) % 1000 FROM dbo.YourTable ...