After upgrading ASP.NET Core 1.1 project to 2.1.4, I am no longer able to execute custom queries I wrote. Please see below stack trace and code. I don’t think so its an issue with the code as it worked just fine when it was running in v1.1. Anyone have any workaround in the mean time? I would hate to downgrade my project back to 1.1.
Exception message:
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such column: c.state_id'.
Stack trace:
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such column: c.state_id'.
at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
Microsoft.EntityFrameworkCore.Query:Error: An exception occurred in the database while iterating the results of a query for context type 'Sunbelt.Data.ApplicationDbContext'.
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such column: c.state_id'.
at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
Steps to reproduce
Include a complete code listing (or project/solution) that we can run to reproduce the issue.
This query use to work fine in EF Core 1.0 version. After upgrading to 2.1 it is no longer working.
# region
var results = from c in _context.Customers
join BState in _context.States on c.StateId equals BState.Id into lBStates
from lBState in lBStates.Where(r => r.Id == c.StateId).DefaultIfEmpty()
join MState in _context.States on c.MStateId equals MState.Id into LMStates
from LMState in LMStates.Where(r => r.Id == c.MStateId).DefaultIfEmpty()
join status in _context.Statuses on c.StatusId equals status.Id into lstatuses
from lstatus in lstatuses.Where(r => r.Id == c.StatusId).DefaultIfEmpty()
join u in _context.Employees on c.ZoneManager equals u.Id into lus
from lu in lus.Where(r => r.Id == c.ZoneManager).DefaultIfEmpty()
join regions in _context.Regions on c.RegionId equals regions.Id into lregions
from lregion in lregions.Where(r => r.Id == c.Id).DefaultIfEmpty()
join zone in _context.Zones on c.ZoneId equals zone.Id into lzones
from lzone in lzones.Where(r => r.Id == c.ZoneId).DefaultIfEmpty()
join storetype in _context.StoreTypes on c.StoreTypeId equals storetype.Id into lstoretypes
from lstoretype in lstoretypes.Where(r => r.Id == c.StoreTypeId).DefaultIfEmpty()
join gasbrand in _context.GasBrands on c.GasBrandId equals gasbrand.Id into lgasbrands
from lgasbrand in lgasbrands.Where(r => r.Id == c.GasBrandId).DefaultIfEmpty()
join propanebrand in _context.PropaneBrands on c.PropaneBrandId equals propanebrand.Id into lpropandbrands
from lpropanebrand in lpropandbrands.Where(r => r.Id == c.PropaneBrandId).DefaultIfEmpty()
join wasteservice in _context.WasteServices on c.WasteServiceId equals wasteservice.Id into lwasteservices
from lwasteservice in lwasteservices.Where(r => r.Id == c.WasteServiceId).DefaultIfEmpty()
join donationbox in _context.YesNos on c.DonationBoxId equals donationbox.Id into ldonationboxes
from ldonationbox in ldonationboxes.Where(r => r.Id == c.DonationBoxId).DefaultIfEmpty()
join pumptoppersize in _context.PumpTopperSizes on c.PumpTopperSizeId equals pumptoppersize.Id into lpumptoppersizes
from lpumptopper in lpumptoppersizes.Where(r => r.Id == c.PumpTopperSizeId).DefaultIfEmpty()
join fountainbrand in _context.FountainMachines on c.FountainMachineId equals fountainbrand.Id into lfountainbrands
from lfountainbrand in lfountainbrands.Where(r => r.Id == c.FountainMachineId).DefaultIfEmpty()
join coffee in _context.Coffees on c.CoffeeId equals coffee.Id into lcoffees
from lcoffee in lcoffees.Where(r => r.Id == c.CoffeeId).DefaultIfEmpty()
join slushy in _context.Slushies on c.SlushyId equals slushy.Id into lslushies
from lslushy in lslushies.Where(r => r.Id == c.SlushyId).DefaultIfEmpty()
join milk in _context.Milks on c.MilkId equals milk.Id into lmilks
from lmilk in lmilks.Where(r => r.Id == c.MilkId).DefaultIfEmpty()
join storeenrolledinmilkprogram in _context.YesNos on c.StoreEnrolledInMilkProgram equals storeenrolledinmilkprogram.Id into lstoreenrolledinmilkprograms
from lstoreenrolledinmilkprogram in lstoreenrolledinmilkprograms.Where(r => r.Id == c.StoreEnrolledInMilkProgram).DefaultIfEmpty()
join insurancecheck in _context.InsuranceCheck on c.InsuranceCheckId equals insurancecheck.Id into linsurancechecks
from linsurancecheck in linsurancechecks.Where(r => r.Id == c.InsuranceCheckId).DefaultIfEmpty()
join rtcacct in _context.YesNos on c.RtcAcct equals rtcacct.Id into lrtcaccts
from lrtcacct in lrtcaccts.Where(r => r.Id == c.RtcAcct).DefaultIfEmpty()
join atmoption in _context.AtmOptions on c.AtmOptionId equals atmoption.Id into latmoptions
from latmoption in latmoptions.Where(r => r.Id == c.AtmOptionId).DefaultIfEmpty()
select new CustomerViewModel()
{
Id = c.Id,
MemberID = c.MemberCode,
FirstName = c.FirstName,
LastName = c.LastName,
OwnerName = c.OwnerName,
ContactPerson = c.ContactPerson,
CorporateName = c.CorpName,
BusinessName = c.BName,
BusinessAddress = c.BAddress,
BusinessCity = c.BCity,
BusinessState = lBState == null ? string.Empty : lBState.StateCode,
BusinessZipCode = c.BZip,
BusinessPhone = c.BPhone,
BusinessCellphone = c.BCellphone,
MailingAddress = c.MAddress,
MailingCity = c.MCity,
MailingState = LMState == null ? string.Empty : LMState.StateCode,
MailingZip = c.MZip,
Fax = c.Fax,
Email = c.Email,
SalesTaxCode = c.SalesTaxCode,
FederalTaxCode = c.FederalTaxCode,
Zone = lzone == null ? string.Empty : lzone.Zone,
District = c.District,
Region = lregion == null ? string.Empty : lregion.RegionNumber,
ZoneManager = lu == null ? string.Empty : lu.FirstName,
DateInactive = c.DateInactive == null ? c.DateInactive : c.DateInactive.Value.Date,
InactiveReason = c.InactiveReason,
DateJoined = c.DateJoined == null ? c.DateJoined : c.DateJoined.Value.Date,
SignedBy = c.SignedBy,
Comments = c.Comments,
Status = lstatus == null ? string.Empty : lstatus.Status,
StoreType = lstoretype == null ? string.Empty : lstoretype.Type,
GasBrand = lgasbrand == null ? string.Empty : lgasbrand.BrandName,
NoOfMpd = c.NoOfMpd,
PropaneBrand = lpropanebrand == null ? string.Empty : lpropanebrand.BrandName,
WasteService = lwasteservice == null ? string.Empty : lwasteservice.Name,
DonationBox = ldonationbox == null ? string.Empty : ldonationbox.Confirm,
BannerSize = c.BannerSize,
PumpTopperSize = lpumptopper == null ? string.Empty : lpumptopper.Size,
NoOfPumpTopper = c.NoOfPumpTopper,
HotFood = c.HotFood,
FountainMachine = lfountainbrand == null ? string.Empty : lfountainbrand.BrandName,
NoOfNozzels = c.NoOfNozzels,
NoOfCokeNozzels = c.NoOfCokeNozzels,
NoOfDrPepperNozzels = c.NoOfDrPepperNozzels,
NoOfPepsiNozzels = c.NoOfPepsiNozzels,
Coffee = lcoffee == null ? string.Empty : lcoffee.BrandName,
Slushy = lslushy == null ? string.Empty : lslushy.BrandName,
NoOfColdVault = c.NoOfColdVault,
NoOfCarbDoors = c.NoOfCarbDoors,
NoOfNonCarbDoors = c.NoOfNonCarbDoors,
NoOfStoreOptions = c.NoOfStoreOptions,
NoOfBeers = c.NoOfBeers,
NoOfCarbShelves = c.NoOfCarbShelves,
NoOfNonCarbShelves = c.NoOfNonCarbShelves,
NoOfCokeNonCarbs = c.NoOfCokeNonCarbs,
NoOfRedbullShelves = c.NoOfRedbullShelves,
NoOfMonsterShelves = c.NoOfMonsterShelves,
NoOfGatoradeShelves = c.NoOfGatoradeShelves,
NoOfArizonaFacings = c.NoOfArizonaFacings,
NoOfNesquickFacings = c.NoOfNesquickFacings,
NoOfAquafinaFacings = c.NoOfAquafinaFacings,
NoOfWildCatFacings = c.NoOfWildCatFacings,
NoOfJarritosFacings = c.NoOfJarritosFacings,
NoOfBangFacings = c.NoOfBangFacings,
NoOfXyiencFacings = c.NoOfXyiencFacings,
NoOfBodyArmourFacings = c.NoOfBodyArmourFacings,
ShelvesInDoor = c.ShelvesInDoor,
Milk = lmilk == null ? string.Empty : lmilk.BrandName,
NoOfMilkShelves = c.NoOfMilkShelves,
StoreEnrolledInMilkProgram = lstoreenrolledinmilkprogram == null ? string.Empty : lstoreenrolledinmilkprogram.Confirm,
InsuranceCheck = linsurancecheck == null ? string.Empty : linsurancecheck.Option,
InsuranceDate = c.InsuranceDate == null ? c.InsuranceDate : c.InsuranceDate.Value.Date,
RtcAcct = lrtcacct == null ? string.Empty : lrtcacct.Confirm,
Electricity = c.Electricity,
AtmOption = latmoption == null ? string.Empty : latmoption.Option,
AtmProcessor = c.AtmProcessor,
GasJobber = c.GasJobber,
StoreSquareFootage = c.StoreSquareFootage,
IceCream = c.IceCream,
NoOfPepsiNonCarbs = c.NoOfPepsiNonCarbs,
NoOfDrPepperNonCarbs = c.NoOfDrPepperNonCarbs,
NoOfStoreOptionShelves = c.NoOfStoreOptionShelves,
Wholesalers = c.Wholesalers,
NoOfPosters = c.NoOfPosters,
PosterSizes = c.PosterSizes,
Fritolay = c.Fritolay,
OtherSnacks = c.OtherSnacks,
StandingCooler = c.StandingCooler,
CounterTopCooler = c.CounterTopCooler,
MidSizeCooler = c.MidSizeCooler,
Created = c.Created == null ? c.Created : c.Created.Value.Date,
Createdby = c.Createdby,
Modified = c.Modified == null ? c.Modified : c.Modified.Value.Date,
Modifiedby = c.Modifiedby,
CoolerModified = c.CoolerModified == null ? c.CoolerModified : c.CoolerModified.Value.Date,
SpacePaymentEligible = c.SpacePaymentEligible
};
# endregion
return results.AsNoTracking().ToList();
Here is the query it generates in output window.
SELECT "c"."id", "c"."member_code" AS "MemberID", "c"."first_name" AS "FirstName", "c"."last_name" AS "LastName", "c"."owner_name" AS "OwnerName", "c"."contact_person" AS "ContactPerson", "c"."corp_name" AS "CorporateName", "c"."b_name" AS "BusinessName", "c"."b_address" AS "BusinessAddress", "c"."b_city" AS "BusinessCity", CASE
WHEN "t"."id" IS NULL
THEN '' ELSE "t"."state_code"
END AS "BusinessState", "c"."b_zip" AS "BusinessZipCode", "c"."b_phone" AS "BusinessPhone", "c"."b_cellphone" AS "BusinessCellphone", "c"."m_address" AS "MailingAddress", "c"."m_city" AS "MailingCity", CASE
WHEN "t0"."id" IS NULL
THEN '' ELSE "t0"."state_code"
END AS "MailingState", "c"."m_zip" AS "MailingZip", "c"."fax", "c"."email", "c"."sales_tax_code" AS "SalesTaxCode", "c"."federal_tax_code" AS "FederalTaxCode", CASE
WHEN "t4"."id" IS NULL
THEN '' ELSE "t4"."zone"
END AS "Zone", "c"."district", CASE
WHEN "t3"."id" IS NULL
THEN '' ELSE "t3"."region_number"
END AS "Region", CASE
WHEN "t2"."id" IS NULL
THEN '' ELSE "t2"."first_name"
END AS "ZoneManager", CASE
WHEN "c"."date_inactive" IS NULL
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END, "c"."date_inactive", "c"."date_inactive", "c"."InactiveReason", CASE
WHEN "c"."date_joined" IS NULL
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END, "c"."date_joined", "c"."date_joined", "c"."SignedBy", "c"."comments", CASE
WHEN "t1"."id" IS NULL
THEN '' ELSE "t1"."status"
END AS "Status", CASE
WHEN "t5"."id" IS NULL
THEN '' ELSE "t5"."type"
END AS "StoreType", CASE
WHEN "t6"."id" IS NULL
THEN '' ELSE "t6"."brand_name"
END AS "GasBrand", "c"."no_of_mpd" AS "NoOfMpd", CASE
WHEN "t7"."id" IS NULL
THEN '' ELSE "t7"."brand_name"
END AS "PropaneBrand", CASE
WHEN "t8"."id" IS NULL
THEN '' ELSE "t8"."name"
END AS "WasteService", CASE
WHEN "t9"."id" IS NULL
THEN '' ELSE "t9"."confirm"
END AS "DonationBox", "c"."banner_size" AS "BannerSize", CASE
WHEN "t10"."id" IS NULL
THEN '' ELSE "t10"."size"
END AS "PumpTopperSize", "c"."no_of_pump_topper" AS "NoOfPumpTopper", "c"."hot_food" AS "HotFood", CASE
WHEN "t11"."id" IS NULL
THEN '' ELSE "t11"."brand_name"
END AS "FountainMachine", "c"."no_of_nozzels" AS "NoOfNozzels", "c"."no_of_coke_nozzels" AS "NoOfCokeNozzels", "c"."no_of_dr_pepper_nozzels" AS "NoOfDrPepperNozzels", "c"."no_of_pepsi_nozzels" AS "NoOfPepsiNozzels", CASE
WHEN "t12"."id" IS NULL
THEN '' ELSE "t12"."brand_name"
END AS "Coffee", CASE
WHEN "t13"."id" IS NULL
THEN '' ELSE "t13"."brand_name"
END AS "Slushy", "c"."no_of_cold_vault" AS "NoOfColdVault", "c"."no_of_carb_doors" AS "NoOfCarbDoors", "c"."no_of_non_carb_doors" AS "NoOfNonCarbDoors", CAST("c"."no_of_store_options" AS TEXT) AS "NoOfStoreOptions", "c"."no_of_beers" AS "NoOfBeers", "c"."no_of_carb_shelves" AS "NoOfCarbShelves", "c"."no_of_non_carb_shelves" AS "NoOfNonCarbShelves", "c"."no_of_coke_non_carbs" AS "NoOfCokeNonCarbs", "c"."no_of_redbull_shelves" AS "NoOfRedbullShelves", "c"."no_of_monster_shelves" AS "NoOfMonsterShelves", "c"."no_of_gatorade_shelves" AS "NoOfGatoradeShelves", "c"."no_of_arizona_facings" AS "NoOfArizonaFacings", "c"."no_of_nesquick_facings" AS "NoOfNesquickFacings", "c"."no_of_aquafina_facings" AS "NoOfAquafinaFacings", "c"."no_of_wild_cat_facings" AS "NoOfWildCatFacings", "c"."no_of_jarritos_facings" AS "NoOfJarritosFacings", "c"."no_of_bang_facings" AS "NoOfBangFacings", "c"."no_of_xyienc_facings" AS "NoOfXyiencFacings", "c"."no_of_body_armour_facings" AS "NoOfBodyArmourFacings", "c"."shelves_in_door" AS "ShelvesInDoor", CASE
WHEN "t14"."id" IS NULL
THEN '' ELSE "t14"."brand_name"
END AS "Milk", "c"."no_of_milk_shelves" AS "NoOfMilkShelves", CASE
WHEN "t15"."id" IS NULL
THEN '' ELSE "t15"."confirm"
END AS "StoreEnrolledInMilkProgram", CASE
WHEN "t16"."id" IS NULL
THEN '' ELSE "t16"."option"
END AS "InsuranceCheck", CASE
WHEN "c"."insurance_date" IS NULL
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END, "c"."insurance_date", "c"."insurance_date", CASE
WHEN "t17"."id" IS NULL
THEN '' ELSE "t17"."confirm"
END AS "RtcAcct", "c"."electricity", CASE
WHEN "t18"."id" IS NULL
THEN '' ELSE "t18"."option"
END AS "AtmOption", "c"."atm_processor" AS "AtmProcessor", "c"."gas_jobber" AS "GasJobber", "c"."store_square_footage" AS "StoreSquareFootage", "c"."ice_cream" AS "IceCream", "c"."no_of_pepsi_non_carbs" AS "NoOfPepsiNonCarbs", "c"."no_of_dr_pepper_non_carbs" AS "NoOfDrPepperNonCarbs", "c"."no_of_store_option_shelves" AS "NoOfStoreOptionShelves", "c"."wholesalers", "c"."no_of_posters" AS "NoOfPosters", "c"."poster_sizes" AS "PosterSizes", "c"."fritolay", "c"."other_snacks" AS "OtherSnacks", "c"."standing_cooler" AS "StandingCooler", "c"."counter_top_cooler" AS "CounterTopCooler", "c"."mid_size_cooler" AS "MidSizeCooler", CASE
WHEN "c"."created" IS NULL
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END, "c"."created", "c"."created", "c"."createdby", CASE
WHEN "c"."modified" IS NULL
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END, "c"."modified", "c"."modified", "c"."modifiedby", CASE
WHEN "c"."cooler_modified" IS NULL
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END, "c"."cooler_modified", "c"."cooler_modified", "c"."SpacePaymentEligible"
FROM "customers" AS "c"
LEFT JOIN (
SELECT "BState".*
FROM "states" AS "BState"
WHERE "BState"."id" = "c"."state_id"
) AS "t" ON "c"."state_id" = "t"."id"
LEFT JOIN (
SELECT "MState".*
FROM "states" AS "MState"
WHERE "MState"."id" = "c"."m_state_id"
) AS "t0" ON "c"."m_state_id" = "t0"."id"
LEFT JOIN (
SELECT "status".*
FROM "statuses" AS "status"
WHERE "status"."id" = "c"."status_id"
) AS "t1" ON "c"."status_id" = "t1"."id"
LEFT JOIN (
SELECT "u".*
FROM "employees" AS "u"
WHERE "u"."id" = "c"."zone_manager"
) AS "t2" ON "c"."zone_manager" = "t2"."id"
LEFT JOIN (
SELECT "regions".*
FROM "regions" AS "regions"
WHERE "regions"."id" = "c"."id"
) AS "t3" ON "c"."region_id" = "t3"."id"
LEFT JOIN (
SELECT "zone".*
FROM "zones" AS "zone"
WHERE "zone"."id" = "c"."zone_id"
) AS "t4" ON "c"."zone_id" = "t4"."id"
LEFT JOIN (
SELECT "storetype".*
FROM "store_types" AS "storetype"
WHERE "storetype"."id" = "c"."store_type_id"
) AS "t5" ON "c"."store_type_id" = "t5"."id"
LEFT JOIN (
SELECT "gasbrand".*
FROM "gas_brands" AS "gasbrand"
WHERE "gasbrand"."id" = "c"."gas_brand_id"
) AS "t6" ON "c"."gas_brand_id" = "t6"."id"
LEFT JOIN (
SELECT "propanebrand".*
FROM "propane_brands" AS "propanebrand"
WHERE "propanebrand"."id" = "c"."propane_brand_id"
) AS "t7" ON "c"."propane_brand_id" = "t7"."id"
LEFT JOIN (
SELECT "wasteservice".*
FROM "waste_services" AS "wasteservice"
WHERE "wasteservice"."id" = "c"."waste_service_id"
) AS "t8" ON "c"."waste_service_id" = "t8"."id"
LEFT JOIN (
SELECT "donationbox".*
FROM "yes_nos" AS "donationbox"
WHERE "donationbox"."id" = "c"."donation_box_id"
) AS "t9" ON "c"."donation_box_id" = "t9"."id"
LEFT JOIN (
SELECT "pumptoppersize".*
FROM "pump_topper_sizes" AS "pumptoppersize"
WHERE "pumptoppersize"."id" = "c"."pump_topper_size_id"
) AS "t10" ON "c"."pump_topper_size_id" = "t10"."id"
LEFT JOIN (
SELECT "fountainbrand".*
FROM "fountain_machines" AS "fountainbrand"
WHERE "fountainbrand"."id" = "c"."fountain_machine_id"
) AS "t11" ON "c"."fountain_machine_id" = "t11"."id"
LEFT JOIN (
SELECT "coffee".*
FROM "coffees" AS "coffee"
WHERE "coffee"."id" = "c"."coffee_id"
) AS "t12" ON "c"."coffee_id" = "t12"."id"
LEFT JOIN (
SELECT "slushy".*
FROM "slushies" AS "slushy"
WHERE "slushy"."id" = "c"."slushy_id"
) AS "t13" ON "c"."slushy_id" = "t13"."id"
LEFT JOIN (
SELECT "milk".*
FROM "milks" AS "milk"
WHERE "milk"."id" = "c"."milk_id"
) AS "t14" ON "c"."milk_id" = "t14"."id"
LEFT JOIN (
SELECT "storeenrolledinmilkprogram".*
FROM "yes_nos" AS "storeenrolledinmilkprogram"
WHERE "storeenrolledinmilkprogram"."id" = "c"."store_enrolled_in_milk_program"
) AS "t15" ON "c"."store_enrolled_in_milk_program" = "t15"."id"
LEFT JOIN (
SELECT "insurancecheck".*
FROM "insurance_check" AS "insurancecheck"
WHERE "insurancecheck"."id" = "c"."insurance_check_id"
) AS "t16" ON "c"."insurance_check_id" = "t16"."id"
LEFT JOIN (
SELECT "rtcacct".*
FROM "yes_nos" AS "rtcacct"
WHERE "rtcacct"."id" = "c"."rtc_acct"
) AS "t17" ON "c"."rtc_acct" = "t17"."id"
LEFT JOIN (
SELECT "atmoption".*
FROM "atm_options" AS "atmoption"
WHERE "atmoption"."id" = "c"."atm_option_id"
) AS "t18" ON "c"."atm_option_id" = "t18"."id"
Further technical details
EF Core version: 2.1.4
Database Provider: Microsoft.EntityFrameworkCore.Sqlite
Operating system: Windows 10
IDE: Visual Studio 2017 15.4
-
#1
from sqlite3 import *
base = connect(«BASE.db»)
cursor = base.cursor()
LIST10 = list(cursor.execute(«»»SELECT Номер10 FROM Words»»»))
LIST11 = list(cursor.execute(«»»SELECT Номер11 FROM Words»»»))
LIST12 = list(cursor.execute(«»»SELECT Номер12 FROM Words»»»))
LIST15 = list(cursor.execute(«»»SELECT Номер15 FROM Words»»»))
cursor.execute(«»»DELETE FROM Words»»»)
def write_funck(list_num,num):
INP = None
while INP != »:
INP = input(‘Введите слово для номера 10: ‘)
list_num.append(INP.lower())
list_num.remove(»)
word_set = set(list_num)
list_num = list(word_set)
def main():
choose = int(input(‘выберете номер задания(10,11,12,15): ‘))
if choose == 10:
write_funck(LIST10,choose)
elif choose == 11:
write_funck(LIST11,choose)
elif choose == 12:
write_funck(LIST12,choose)
else:
write_funck(LIST15,choose)
main()
cursor.execute(f»»»INSERT INTO Words(Номер10) VALUES({LIST10})»»»)
cursor.execute(f»»»INSERT INTO Words(Номер11) VALUES({LIST11})»»»)
cursor.execute(f»»»INSERT INTO Words(Номер12) VALUES({LIST12})»»»)
cursor.execute(f»»»INSERT INTO Words(Номер15) VALUES({LIST15})»»»)
base.commit()
cursor.close()
-
#3
sqlite3(No such column(Элементы массива)) — это значит что в таблице нет такого столбца.
Вот тут есть пример вставки данных в таблицу:
https://younglinux.info/sqlite/insert
Так проблема в том,что я добавляю в столбец(Номер10) массив состоящий из слов.Но в качестве столбцов программа использует элементы массива, и я не понимаю почему.Вроде по синтаксису Insert сделан правильно.
-
#4
По коду не понятно какие столбцы у вас в таблице (сколько их и какого они типа). Покажите код которым создаете таблицу.
-
#5
По коду не понятно какие столбцы у вас в таблице (сколько их и какого они типа). Покажите код которым создаете таблицу.
-
#6
Список в базу можно добавить в виде строки (т. к. в виде списка не примет):
Python:
from sqlite3 import *
base = connect('BASE.db')
cursor = base.cursor()
l = ['1', '2', '3']
cursor.execute("""INSERT INTO Words ('Номер10') VALUES (?)""", (str(l),))
base.commit()
base.close()
-
#7
Или можно список объединить в строку с помощью join и записать, а при получении разделять через split:
Запись:
Python:
from sqlite3 import *
base = connect('BASE.db')
cursor = base.cursor()
l = ['1', '2', '3']
x = ",".join(l)
cursor.execute("""INSERT INTO Words ('Номер10') VALUES (?) """, (x,))
base.commit()
base.close()Чтение:
Python:
from sqlite3 import *
base = connect('BASE.db')
cursor = base.cursor()
c = cursor.execute("""SELECT Номер10 FROM Words WHERE rowid=1""")
l = c.fetchone()
y = l[0].split(',')
print(y)Запись в определенный ряд таблицы:
Python:
base = connect('BASE.db')
cursor = base.cursor()
l = ['1', '2', '3']
x = ",".join(l)
cursor.execute("""UPDATE Words SET 'Номер10' = (?) WHERE rowid=1""", (x,))
base.commit()
base.close()
-
#8
Список в базу можно добавить в виде строки (т. к. в виде списка не примет):
Python:
from sqlite3 import * base = connect('BASE.db') cursor = base.cursor() l = ['1', '2', '3'] cursor.execute("""INSERT INTO Words ('Номер10') VALUES (?)""", (str(l),)) base.commit() base.close()
Спасибо вам огромное)))))
Содержание
- SQLite Forum
- (1) By Gwendal Roué (groue) on 2020-04-19 12:41:17 [source]
- (2) By Neal (_Neal_) on 2020-04-19 14:25:17 in reply to 1 [link] [source]
- (3) By Ryan Smith (cuz) on 2020-04-19 16:04:35 in reply to 1 [link] [source]
- (4) By Gwendal Roué (groue) on 2020-04-19 16:33:28 in reply to 3 [link] [source]
- (5.1) By Gwendal Roué (groue) on 2020-04-19 17:01:01 edited from 5.0 in reply to 3 [link] [source]
- (6) By Ryan Smith (cuz) on 2020-04-20 01:03:49 in reply to 5.1 [link] [source]
- (7) By Keith Medcalf (kmedcalf) on 2020-04-20 02:24:44 in reply to 5.1 [link] [source]
- (8.1) By Gwendal Roué (groue) on 2020-04-20 07:37:48 edited from 8.0 in reply to 7 [link] [source]
- (9) By Gwendal Roué (groue) on 2020-04-20 16:13:36 in reply to 7 [link] [source]
SQLite Forum
(1) By Gwendal Roué (groue) on 2020-04-19 12:41:17 [source]
I’m working on a SQL generator that wants to «flatten» a to-many relation backed by a foreign key into a to-one relation by focusing on a single row in the associated rows.
Sometimes examples make things more clear. Let’s say we want to fetch «all chats along with their latest post», given the following schema:
In my experiments looking for a SQL pattern that provides correct results, while remaining composable (I’m not looking for the most efficient SQL, and generally speaking I trust the SQLite query planner), I stumbled upon a surprising behavior with SQLite version 3.28.0.
This query works:
This one fails with a «no such column» error:
I have two questions, if someone can help. The first is the most important to me. The second is just a request for clarification:
- Can I rely on the fact that the first query will never give such an error in the future?
- Why can some subqueries refer to outer tables, and some others can not?
(2) By Neal (_Neal_) on 2020-04-19 14:25:17 in reply to 1 [link] [source]
Columns from both of the join operands can be referenced in ON clause — thus your first query works. But your second query is trying to refer to first join operand’s column in second join operand — that is not allowed.
(3) By Ryan Smith (cuz) on 2020-04-19 16:04:35 in reply to 1 [link] [source]
- Yes the first query will always work, but
- That is not the problem with query 2 — The SELECT is very happy, it’s the JOIN that is the problem because you are joining to an as-yet non existing table/view (which the join creates) but then trying to refer to chat.id in that second join-created-view, however «chat» never appears in THAT join’s SELECT .. FROM, and you cannot magically grab it from some other JOIN you did earlier. The JOINS are not the same and won’t be in the same For-Loop at the same row/position at the same moment — that only works for correlated sub-queries, which your JOIN is not.
Perhaps read up on Correlated sub-queries if what I say did not make immediate sense, once you understand the limitations of those, it should become more clear why your JOIN did not behave as expected, and how you could make that sort of thing work.
(4) By Gwendal Roué (groue) on 2020-04-19 16:33:28 in reply to 3 [link] [source]
Perhaps read up on Correlated sub-queries if what I say did not make immediate sense
I’ll surely do! Thank you Ryan for hinting me at a better understanding of what I’m trying to achieve. That’s very well appreciated, and exactly what I was hoping for, even if I was too shy to ask for it 🙂
(5.1) By Gwendal Roué (groue) on 2020-04-19 17:01:01 edited from 5.0 in reply to 3 [link] [source]
Perhaps read up on Correlated sub-queries if what I say did not make immediate sense
So I did. A correlated sub query executes as if it runs once for each row in the outer query.
But «executes as if» and «actually executes» are not always the same. The database is allowed to perform whatever optimization it wants, as long as the observed behavior matches the expected one.
So does the working query below exhibits a quadratic behavior?
I’m not quite used to query planning interpretation yet:
(6) By Ryan Smith (cuz) on 2020-04-20 01:03:49 in reply to 5.1 [link] [source]
Please forgive me, I may not be 100% following your question, and it’s very late, but I’ll give it a try.
Firstly, I don’t know what «exhibits quadratic behavior» means in this case, but I will assume you are asking «will the QP do a Cartesian join where it steps ALL the «FROM rows» (chat) times ALL the JOINed rows (post). [aka O(n^2)]
That answer is no, even though it seems like it would have to.
The Query you propose (for which the Query plan is given) seemingly will start by walking (iterating through) the entire chat table (because you offer no filter in a WHERE clause or such).
Now note that the QP is free to choose to start with the joined table if it pleases, but won’t in this case as reported by the query plan.
It then will simply look up the correlated value using the PK (rowid) of the «post» table, causing only O(log n) look-ups. Note that your ORDER BY and DESC LIMIT 1 statements has no effect and is completely ignored here because the link is to a rowid (or stated otherwise, the unique primary key) so row number N can only ever be row number N, no matter how it is «ordered» or limited, in any lookup.
i.e. — this should have the same query plan:
This may well NOT be the case for other types of correlated sub-query which perhaps do not use the primary key. In that case, the order-by would help to make the QP construct a temporary table with temporary index to do the look-ups. It is hard to guarantee though, and best practice when doing correlated sub-queries is to ensure you have Indexes on the looked-up fields.
I find it best to think of the correlated sub-query as a form of Table/View, and then asking myself if I can see a clear relationship between the tables and how I can phrase the query so the Query-planner cannot miss that same relationship. (And then test it with EXPLAIN of course).
Another way of doing the above would be with a Common Table Expression (CTE — more good reading material) which is like creating a view inside your query which may look something like this:
which is roughly equivalent to:
but with the advantage that multiple CTEs can be added and that they can refer each other in their respective SELECT clauses.
This is all tricky, because the Query Planner can decide, based on many whims, how it wants to traverse query tables. It may be that your Query suddenly switches to another plan after adding some data and doing ANALYZE. The best is to test with some real-World data.
Hope that helps and best of luck!
(7) By Keith Medcalf (kmedcalf) on 2020-04-20 02:24:44 in reply to 5.1 [link] [source]
The performance will be, at best case, O(N*M^2) where N is the number of rows in chat and M is the number of rows in post. It will probably degrade a lot faster than that. We recently discussed that in another thread.
You appear to be trying to find the «earliest post» for each «thread». Note the following comments:
the chat table is useless when querying data as it does nothing except waste time and you can tell this because
(a) there is nothing from that table in the select list; and,
(b) removing all reference to it does not affect the query result in any way.
(chatid, date) must be a candidate key for the post table (that is, it must be unique). If it is not unique then the concept of top and bottom of a chatid cannot use that field and some other tiebreaker must be used. If some other tiebreaker must be used, just use that directly and forget about the added complication of adding something useless.
So, you want the following:
(8.1) By Gwendal Roué (groue) on 2020-04-20 07:37:48 edited from 8.0 in reply to 7 [link] [source]
The performance will be, at best case, O(N*M^2) where N is the number of rows in chat and M is the number of rows in post. It will probably degrade a lot faster than that.
All right. Thank you very much for pointing this out! I can’t use this technique, then, because I won’t ship a library which generates SQL with such a bad complexity.
We recently discussed that in another thread.
I’d be happy to check this conversation.
You appear to be trying to find the «earliest post» for each «thread».
Actually, the example wants to load both thread («chat») and latest post. Imagine you want to feed the main screen of a chat app, with all conversations and a preview of the last message.
the chat table is useless when querying data as it does nothing except waste time and you can tell this because (a) there is nothing from that table in the select list; and, (b) removing all reference to it does not affect the query result in any way.
So, the point is actually to get chat information along: its a SELECT chat.*, post.*
(chatid, date) must be a candidate key for the post table (that is, it must be unique).
I really appreciate your help. This is not an acceptable constraint, in the general case, unfortunately:
In the chat/post example, we can not require unique post dates in a thread. This would exclude a whole set of reasonable and real use cases. If the user wants to disambiguate the «last post», the user would would have to sort per date and another disambiguation column (such as the primary key). If the user does not perform such disambiguation, then any post can be chosen (meaning I can add the primary key as an extra disambiguation ordering clause in the SQL I generate).
The chat/post example is just one example, for illustration purpose. My general case is the following:
- one «parent» table
- one «child» table
- a foreign key from child to parent
- a set of ordering clauses on the child table, which identifies one child per parent. The ordering clauses may not identify a single child per parent. The set of ordering clauses may actually be empty.
- An SQL query
- which selects ( parent.*, child.* ) pairs,
- where the child is the first child of each parent according by the set of ordering clauses (any child is ok if the ordering clauses are ambiguous)
- where the child may actually be missing (if a parent has no child), in a similar fashion to LEFT JOIN
- of acceptable complexity
- Advice for indexes in order to get the desired complexity.
Of course, if this general case is too relaxed, and does not satisfy the requirements for such a desired output, then I’ll have to adapt, and maybe distinguish more cases. For example, unique indexes, if acceptable by the user, can be used. But I have to deal with the fact that users may comes with a «weak» setup. Yet I do not want to punish users who have a «strong» setup with an SQL query which has a bad O(. ) complexity.
It’s a difficult exercice, but I hope I can find out a solution 🙂
Edit: if there is no general solution to the general problem as I describe it, then I may have to provide an advice such as «in such situation, setup an extra foreign key to the desired child, and consider using triggers so that it get automatically updated whenever the child table is modified». That would be an acceptable solution also, even if much less easy for the users to setup. I consider the «all chats with their latest post» and all similar queries to be an important use case to address, and I want to help users who do not want to write their SQL themselves (because they don’t want to, or because they can’t).
(9) By Gwendal Roué (groue) on 2020-04-20 16:13:36 in reply to 7 [link] [source]
Keith, you have provided excellent hints, which drove me to a solution that sounds quite general and satisfying.
It uses window functions, so it requires SQLite 3.28+.
It looks like it has a good complexity, as I see by running the request on datasets of various sizes. For a constant number of posts per chats, it is linear with the number of chats. For a constant number of chats, it is linear with the number of posts per chats. This sounds quite acceptable to me, as it fits well a kind of database that is quite common, in the wild.
It is general enough, because it accepts the input I have:
- one parent table (here, «chat»)
- one child table (here, «post»)
- a foreign key from child to parent
- a set of ordering clauses on the child table, which identifies one child per parent. The ordering clauses may not identify a single child per parent. The set of ordering clauses may actually be empty.
Источник