All Questions
12,386
questions
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 ...
- 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?
- 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?
- 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(...
- 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.
- 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 ...
- 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 ...
- 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
...
- 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 ...
- 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?
- 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 ...
- 11k
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 ...
- 10.8k
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 ...
- 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):
...
- 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 ...
- 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. ...
- 1,948
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 ...
- 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 ...
- 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]
user507220
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
"...
- 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 ...
- 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?
- 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')...
- 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 ...
- 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(...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 8,640
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 = ...
- 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 ...
- 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()
...
- 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 `...
- 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....
- 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 ...
- 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:...
- 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 ...
- 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 ...
- 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 = ...
- 791
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+"_%'")
- 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 ...
user11617
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',
...
- 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 ...
- 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()
- 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(...
- 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 ...
- 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,...
- 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 ...
- 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/...
- 655
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:
...
- 1,221