All Questions

Tagged with
Filter by
Sorted by
Tagged with
826 votes
9 answers
899k views

How can I temporarily disable a foreign key constraint in MySQL?

Is it possible to temporarily disable constraints in MySQL? I have two Django models, each with a foreign key to the other one. Deleting instances of a model returns an error because of the foreign ...
jul's user avatar
  • 36.9k
483 votes
10 answers
605k views

How to delete a record in Django models?

I want to delete a particular record like: delete from table_name where id = 1; How can I do this in a django model?
user426795's user avatar
  • 11.4k
463 votes
26 answers
321k views

How can I see the raw SQL queries Django is running?

Is there a way to show the SQL that Django is running while performing a query?
spence91's user avatar
  • 78.5k
435 votes
6 answers
298k views

How to perform OR condition in django queryset?

I want to write a Django query equivalent to this SQL query: SELECT * from user where income >= 5000 or income is NULL. How to construct the Django queryset filter? User.objects.filter(...
Elisa's user avatar
  • 6,975
421 votes
5 answers
275k views

Getting the SQL from a Django QuerySet

How do I get the SQL that Django will use on the database from a QuerySet object? I'm trying to debug some strange behavior, but I'm not sure what queries are going to the database.
exupero's user avatar
  • 9,336
338 votes
10 answers
435k views

How to execute raw SQL in Flask-SQLAlchemy app

How do you execute raw SQL in SQLAlchemy? I have a python web app that runs on flask and interfaces to the database through SQLAlchemy. I need a way to run the raw SQL. The query involves multiple ...
starwing123's user avatar
  • 3,493
260 votes
16 answers
124k views

django test app error - Got an error creating the test database: permission denied to create database

When I try to test any app with command (I noticed it when I tried to deploy myproject using fabric, which uses this command): python manage.py test appname I get this error: Creating test database ...
Andrius's user avatar
  • 20.5k
187 votes
16 answers
184k views

How can I get dict from sqlite query?

db = sqlite.connect("test.sqlite") res = db.execute("select * from table") With iteration I get lists coresponding to the rows. for row in res: print row I can get name of the columns ...
Meloun's user avatar
  • 14.4k
180 votes
13 answers
196k views

How do I get a raw, compiled SQL query from a SQLAlchemy expression?

I have a SQLAlchemy query object and want to get the text of the compiled SQL statement, with all its parameters bound (e.g. no %s or other variables waiting to be bound by the statement compiler or ...
cce's user avatar
  • 4,944
179 votes
16 answers
251k views

Python list in SQL query as parameter [duplicate]

I have a Python list, say l = [1,5,8] I want to write a SQL query to get the data for all the elements of the list, say select name from students where id = |IN THE LIST l| How do I accomplish this?
Mohit Ranka's user avatar
  • 4,243
175 votes
7 answers
164k views

What is the SQL ''LIKE" equivalent on Django ORM queries?

What is the equivalent of the following SQL statement in Django? SELECT * FROM table_name WHERE string LIKE pattern; I tried this: result = table.objects.filter( pattern in string ) but it didn't ...
Aswin Murugesh's user avatar
159 votes
4 answers
191k views

SQLAlchemy: how to filter date field?

Here is model: class User(Base): ... birthday = Column(Date, index=True) #in database it's like '1987-01-17' ... I want to filter between two dates, for example to choose all users in ...
Vitalii Ponomar's user avatar
154 votes
10 answers
125k views

How to log all sql queries in Django?

How can I log all SQL queries that my django application performed? I want to log everything, including SQLs from admin site. I saw this question and a FAQ answer but I still can't figure out where ...
Oleg Pavliv's user avatar
  • 20.8k
141 votes
5 answers
312k views

sqlalchemy: how to join several tables by one query?

I have the following SQLAlchemy mapped classes: class User(Base): __tablename__ = 'users' email = Column(String, primary_key=True) name = Column(String) class Document(Base): ...
barankin's user avatar
  • 1,883
141 votes
15 answers
252k views

Python SQL query string formatting

I'm trying to find the best way to format an sql query string. When I'm debugging my application I'd like to log to file all the sql query strings, and it is important that the string is properly ...
ssoler's user avatar
  • 5,064
138 votes
13 answers
470k views

Connecting to Microsoft SQL server using Python

I am trying to connect to SQL through python to run some queries on some SQL databases on Microsoft SQL server. From my research online and on this forum the most promising library seems to be pyodbc. ...
Christopher Ell's user avatar
137 votes
5 answers
232k views

sqlalchemy filter multiple columns

How do I combine two columns and apply filter? For example, I want to search in both the "firstname" and "lastname" columns at the same time. Here is how I have been doing it if searching only one ...
teggy's user avatar
  • 6,165
135 votes
5 answers
491k views

How to use variables in SQL statement in Python?

I have the following Python code: cursor.execute("INSERT INTO table VALUES var1, var2, var3,") where var1 is an integer, var2 and var3 are strings. How can I write the variable names ...
user111606's user avatar
  • 1,353
134 votes
6 answers
170k views

How to query database by id using SqlAlchemy?

I need to query a SQLAlchemy database by its id something similar to User.query.filter_by(username='peter') but for id. How do I do this? [Searching over Google and SO didn't help]
user avatar
116 votes
8 answers
43k views

Django: Adding "NULLS LAST" to query

I would like to sort a model by using Postgresql's "NULLS LAST" option. How could it be done? I tried something like MyModel.objects.all().extra(order_by=('-price', 'NULLS LAST')) But I get "...
GabiMe's user avatar
  • 18.3k
113 votes
10 answers
214k views

How to count rows with SELECT COUNT(*) with SQLAlchemy?

I'd like to know if it's possible to generate a SELECT COUNT(*) FROM TABLE statement in SQLAlchemy without explicitly asking for it with execute(). If I use: session.query(table).count() then it ...
tiho's user avatar
  • 6,805
104 votes
9 answers
56k views

Getting random row through SQLAlchemy

How do I select one or more random rows from a table using SQLAlchemy?
cnu's user avatar
  • 36.6k
97 votes
6 answers
170k views

Select NULL Values in SQLAlchemy

Here's my (PostgreSQL) table -- test=> create table people (name varchar primary key, marriage_status varchar) ; test=> insert into people values ('Ken', 'married')...
Jerry's user avatar
  • 2,527
97 votes
4 answers
309k views

reading external sql script in python

I am working on a learning how to execute SQL in python (I know SQL, not Python). I have an external sql file. It creates and inserts data into three tables 'Zookeeper', 'Handles', 'Animal'. Then I ...
mpg's user avatar
  • 3,779
94 votes
1 answer
134k views

Importing data from a MySQL database into a Pandas data frame including column names [duplicate]

I am importing data from a MySQL database into a Pandas data frame. The following excerpt is the code that I am using: import mysql.connector as sql import pandas as pd db_connection = sql.connect(...
vFlav's user avatar
  • 1,109
94 votes
3 answers
116k views

How to perform a left join in SQLALchemy?

I have a SQL query which perfroms a series of left joins on a few tables: SELECT <some attributes> FROM table1 t1 INNER JOIN table2 t2 ON attr = 1 AND attr2 = 1 LEFT JOIN table3 t3 ON ...
user1742188's user avatar
  • 4,783
92 votes
4 answers
111k views

How to use "AND" in a Django filter?

How do I create an "AND" filter to retrieve objects in Django? e.g I would like to retrieve a row which has a combination of two words in a single field. For example the following SQL query ...
gath's user avatar
  • 25k
92 votes
2 answers
204k views

Pandas read_sql with parameters

Are there any examples of how to pass parameters with an SQL query in Pandas? In particular I'm using an SQLAlchemy engine to connect to a PostgreSQL database. So far I've found that the following ...
tobycoleman's user avatar
  • 1,674
88 votes
13 answers
118k views

python pandas to_sql with sqlalchemy : how to speed up exporting to MS SQL?

I have a dataframe with ca 155,000 rows and 12 columns. If I export it to csv with dataframe.to_csv , the output is an 11MB file (which is produced instantly). If, however, I export to a Microsoft ...
Pythonista anonymous's user avatar
87 votes
10 answers
55k views

Passing table name as a parameter in psycopg2

I have the following code, using pscyopg2: sql = 'select %s from %s where utctime > %s and utctime < %s order by utctime asc;' data = (dataItems, voyage, dateRangeLower, dateRangeUpper) rows = ...
Caligari's user avatar
  • 1,411
87 votes
3 answers
55k views

Get SQL query count during a Django shell session

Is there a way to print the number of raw SQL queries performed by the Django ORM during a Django shell session? This sort of information is already provided by the Django debug toolbar (e.g, 5 ...
Jian's user avatar
  • 10.7k
86 votes
7 answers
168k views

Raw SQL queries in Django views

How would I perform the following using raw SQL in views.py? from app.models import Picture def results(request): all = Picture.objects.all() yes = Picture.objects.filter(vote='yes').count() ...
David542's user avatar
  • 108k
86 votes
7 answers
145k views

Selecting distinct column values in SQLAlchemy/Elixir

In a little script I'm writing using SQLAlchemy and Elixir, I need to get all the distinct values for a particular column. In ordinary SQL it'd be a simple matter of SELECT DISTINCT `column` FROM `...
David Z's user avatar
  • 130k
84 votes
3 answers
106k views

Get the number of rows in table using SQLAlchemy

I am using SQLAlchemy in Python, and I want to know how to get the total number of rows in a column. I have variables defined: engine = sqlalchemy.create_engine(url, ehco=False) Session = sqlalchemy....
dbmikus's user avatar
  • 5,188
84 votes
11 answers
148k views

How to see the real SQL query in Python cursor.execute using pyodbc and MS-Access

I use the following code in Python (with pyodbc for a MS-Access base). cursor.execute("select a from tbl where b=? and c=?", (x, y)) It's Ok but, for maintenance purposes, I need to know the ...
philnext's user avatar
  • 3,352
83 votes
11 answers
137k views

How to create a large pandas dataframe from an sql query without running out of memory?

I have trouble querying a table of > 5 million records from MS SQL Server database. I want to select all of the records, but my code seems to fail when selecting to much data into memory. This works:...
slizb's user avatar
  • 5,952
82 votes
3 answers
71k views

Pandas equivalent of GROUP BY HAVING in SQL

What is the most efficient way to use groupby and in parallel apply a filter in pandas? Basically I am asking for the equivalent in SQL of select * ... group by col_name having condition I think ...
Mannaggia's user avatar
  • 4,679
79 votes
10 answers
333k views

TypeError: tuple indices must be integers, not str

I am trying to pull data from a database and assign them to different lists. This specific error is giving me a lot of trouble "TypeError: tuple indices must be integers, not str" I tried ...
Harsha Jasti's user avatar
  • 1,184
78 votes
24 answers
324k views

PYODBC--Data source name not found and no default driver specified

import pyodbc connection = pyodbc.connect('Driver = {SQL Server};Server=SIWSQL43A\SIMSSPROD43A;' 'Database=CSM_reporting;Trusted_Connection=yes;') Error: connection = ...
user8560985's user avatar
76 votes
7 answers
266k views

Python: Number of rows affected by cursor.execute("SELECT ...)

How can I access the number of rows affected by: cursor.execute("SELECT COUNT(*) from result where server_state='2' AND name LIKE '"+digest+"_"+charset+"_%'")
Tie-fighter's user avatar
  • 1,043
76 votes
9 answers
68k views

What is an efficient way of inserting thousands of records into an SQLite table using Django?

I have to insert 8000+ records into a SQLite database using Django's ORM. This operation needs to be run as a cronjob about once per minute. At the moment I'm using a for loop to iterate through all ...
user avatar
72 votes
6 answers
166k views

Not all parameters were used in the SQL statement (Python, MySQL)

I get an error on the following Python code: import mysql.connector cnx = mysql.connector.connect(user='root', password='', host='127.0.0.1', ...
Enthuziast's user avatar
  • 1,195
71 votes
2 answers
164k views

Pandas left outer join multiple dataframes on multiple columns

I am new to using DataFrame and I would like to know how to perform a SQL equivalent of left outer join on multiple columns on a series of tables Example: df1: Year Week Colour Val1 2014 ...
user3311225's user avatar
  • 1,045
70 votes
9 answers
61k views

How to TRUNCATE TABLE using Django's ORM?

To empty a database table, I use this SQL Query: TRUNCATE TABLE `books` How to I truncate a table using Django's models and ORM? I've tried this, but it doesn't work: Book.objects.truncate()
Silver Light's user avatar
  • 45.1k
70 votes
6 answers
62k views

Pandas query function not working with spaces in column names

I have a dataframe with spaces in column names. I am trying to use query method to get the results. It is working fine with 'c' column but getting error for 'a b' import pandas as pd a = pd.DataFrame(...
Bhushan Pant's user avatar
  • 1,550
69 votes
2 answers
63k views

SQLAlchemy "default" vs "server_default" performance

Is there a performance advantage (or disadvantage) when using default instead of server_default for mapping table column default values when using SQLAlchemy with PostgreSQL? My understanding is that ...
benvc's user avatar
  • 14.8k
67 votes
8 answers
196k views

Python and SQLite: insert into table

Having the following table schema: tablename( name varchar[100], age int, sex char[1] ) Having a list that has 3 rows each representing a table row: row1 = [laks,444,M] row2 = [kam,445,...
webminal.org's user avatar
  • 46.1k
67 votes
9 answers
304k views

Retrieving Data from SQL Using pyodbc

I am trying to retrieve data from an SQL server using pyodbc and print it in a table using Python. However, I can only seem to retrieve the column name and the data type and stuff like that, not the ...
Mike's user avatar
  • 1,611
64 votes
27 answers
85k views

pip install mysqlclient returns "fatal error C1083: Cannot open file: 'mysql.h': No such file or directory

Here is this issue: I attempt to install mysqlclient like so C:\Users\amccommon349>pip install mysqlclient Collecting mysqlclient Using cached https://files.pythonhosted.org/packages/ec/fd/...
Aaron McCommon's user avatar
64 votes
2 answers
155k views

Python, SQLAlchemy pass parameters in connection.execute

I am using SQLAlchemy connection.execute(sql) to transform select results to array of maps. Have following code def __sql_to_data(sql): result = [] connection = engine.connect() try: ...
Denis's user avatar
  • 1,221

1
2 3 4 5
248